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

270 views
Skip to first unread message

Leandro Lázaro

unread,
Aug 22, 2023, 2:42:56 PM8/22/23
to sqlalchemy
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

unread,
Aug 22, 2023, 2:51:47 PM8/22/23
to noreply-spamdigest via sqlalchemy
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
 
 
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.

Leandro Lázaro

unread,
Sep 5, 2023, 6:50:27 PM9/5/23
to sqlalchemy

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

unread,
Sep 5, 2023, 8:16:27 PM9/5/23
to noreply-spamdigest via sqlalchemy
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. 

Leandro Lázaro

unread,
Sep 5, 2023, 9:03:21 PM9/5/23
to sqlal...@googlegroups.com
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?

Leandro Lázaro

unread,
Sep 6, 2023, 7:40:59 AM9/6/23
to sqlal...@googlegroups.com
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

unread,
Sep 6, 2023, 8:41:20 AM9/6/23
to noreply-spamdigest via sqlalchemy
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.

Mike Bayer

unread,
Sep 6, 2023, 8:42:07 AM9/6/23
to noreply-spamdigest via sqlalchemy
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
Reply all
Reply to author
Forward
0 new messages