sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'PPESSOA' expected to update 1 row(s); 0 were matched.

[Leandro Lázaro]

Hello

First, thanks for building this library. God bless you

I've been trying to understand what's going on for days.

This code works fine:

https://pastebin.com/fLTnB8jy

But if I try to modify any other parameter like PERSONAL EMAIL I get the error:

sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'PPESSOA' expected to update 1 row(s); 0 were matched

Example: https://pastebin.com/EwdwLRcp

Tank you

[Mike Bayer]

if it's fully reproducible every time with both statements, then this suggests something is happening with the database server itself, such as some kind of issue with triggers getting involved or something.   In particular if this is MS SQL Server and there are triggers involved, the updated rowcount might not be working correctly and extra steps might need to be taken to turn off SQLAlchemy's rowcount facilities.

you want to turn on `echo=True` and see that the expected UPDATE statement takes place.  Then you might want to try running those UPDATE statements directly on the database and see that they work.

What database backend, what database driver, and is the issue only reproducible in a single environment or can it be reproduced elsewhere?

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c2939c34-4d95-4fe8-b697-afc705b47a11n%40googlegroups.com.

[Leandro Lázaro]

Hello, apologies for the delay.

I'm using SQL Server. Updates using the code below work normally. However, when I try to change the object directly and apply commit, I receive the mentioned error.

stmt = update(PPESSOA).where(PPESSOA.CODIGO == ppessoa.CODIGO).values(EMAILPESSOAL=data['EMAIL'])
session.execute(stmt)
session.commit()

The connection string is:

SQLALCHEMY_TCLOUD_DATABASE_URI = 'mssql://'+TCLOUD_DATABASE_USERNAME+':'+TCLOUD_DATABASE_PASSWORD_UPDATED+'@'+TCLOUD_DATABASE_HOST+':'+TCLOUD_DATABASE_PORT+'/'+TCLOUD_DATABASE_NAME+'?driver='+TCLOUD_DATABASE_DRIVER

Driver is:

libmsodbcsql-17.10.so.4.1

Thank you for all!

[Mike Bayer]

we will work around what is likely some kind of driver related error:

engine = create_engine(".... your engine string normally  .. ")

engine.dialect.supports_sane_rowcount = False

then run the program normally.  the UPDATE will succeed.  however, after the program runs, look in the database and make sure the UPDATE took effect correctly.    if so, the driver, either pyodbc, or the ODBC driver in use, has an unknown bug.   try updating drivers, both pyodbc and the ODBC driver in use. 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/65034fdf-0743-4f6a-8f4b-7b947364880dn%40googlegroups.com.

[Leandro Lázaro]

It worked!!! Thank you very much, it solved my problem. I was already going crazy haha. I will try to update the driver to solve the problem right at the root. Just a doubt: Changing to false does not generate any side effects?

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/77ef55c9-9c91-48c4-a1df-5e7fe121e3cd%40app.fastmail.com.

[Leandro Lázaro]

I updated the driver to the latest version and removed the line 'self.engine.dialect.supports_sane_rowcount = False,' and the same error occurred again. Could this driver also have an unknown bug?

TCLOUD_DATABASE_DRIVER_V18 = "/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.1.1"

SQLALCHEMY_TCLOUD_DATABASE_URI = 'mssql://'+TCLOUD_DATABASE_USERNAME+':'+TCLOUD_DATABASE_PASSWORD_UPDATED+'@'+TCLOUD_DATABASE_HOST+':'+TCLOUD_DATABASE_PORT+'/'+TCLOUD_DATABASE_NAME+'?driver='+TCLOUD_DATABASE_DRIVER_V18+'&Encrypt=yes&TrustServerCertificate=yes'

[Mike Bayer]

changing it to false means if an UPDATE statement that expects to update on primary key in the ORM does not actually match any rows, the program continues and this may be a silent failure, leading to more confusing problems later if the row was in fact deleted or is otherwise non-existent.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAEKDjrkbZPwyBySd10fBzUiBFyd0a6LqPB4kNXQTduO5Li4w1w%40mail.gmail.com.

[Mike Bayer]

it can be related to the driver or the "cloud" database you're using, we have lots of reports of SQL Server drivers not reporting on matched rows correctly

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAEKDjrk655oE0f5RqK5Q7_kagoTcHk11n%3DyaGstXMN3%3D%3DjQxHA%40mail.gmail.com.