MSSQL with PyODBC: StaleDataError on UPDATE statement

[jue...@gmail.com‏]

I'm currently working with sqlalchemy (Version 1.3.18) to access a Microsoft Server SQL Database (Version: 14.00.3223). I use pyodbc (Version 4.0.30)

When I try to update a single object and try to update and commit the object, I run into a StaleDataError:

StaleDataError: UPDATE statement on table 'Measurement' expected to update 1 row(s); 5051 were matched. 

The update statement looks perfectly fine:

2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?

2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)

Any Idea what's wrong with this.

[jue...@gmail.com‏]

Here is the code I used (simplified):

class Measurement(Base):

    __tablename__ = 'Measurement'

    __table_args__ = (

        {'implicit_returning': False},  # Required because of some triggers on this table

    )

    ID = Column(Integer, primary_key=True)

    Created = Column(DATETIME2, nullable=False, default=datetime.datetime.now)

    IsCompleted = Column(BIT)

    Completed = Column(DATETIME2)

        

measurement = Measurement(Part=motor_unit, TestProcedureVersion=test_procedure)

session.add(measurement)

session.commit()

# And Later:

measurement.IsCompleted = True

session.commit()  # --> This raises the StaleData Exception

[jue...@gmail.com‏]

Strangely, if I add the following mapper_args, it is working with a warning (SAWarning: Dialect mssql+pyodbc does not support updated rowcount - versioning cannot be verified.) but not with an exception.

    __mapper_args__ = {

        'version_id_col': Created,

        'version_id_generator': False,

    }

[Mike Bayer‏]

hi -

does your Measurement table have a primary key constraint present, and does this primary key constraint consist of exactly the "ID" column only and no other columns?  it would appear you have not mapped the primary key correctly.

--

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/d607c30c-2bc5-4680-9e7f-842da8f28792n%40googlegroups.com.

[Mike Bayer‏]

yeah that is just disabling the check, the "5000 rows matched" is a critical malfunction referring to non-working primary key.

--

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/d71580a6-07a4-4495-af60-cd8164fcc899n%40googlegroups.com.

[jue...@gmail.com‏]

I think ID is the only primary key of the Measurements table, but I can double check this on Monday. I used sqlacodegen to create the initial models, so if this tool correctly detects primary keys, the Measurement model should match the table definition.

I also tried to execute the follwing statement using engine.execute:

2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?

2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)

The result also indicated that 5051 rows where affected, but I don't get it why... There is clearly only one Measurement with ID 6248... I know that certain triggers are executed if IsCompleted is changed, so maybe this has an effect on the reported rows?

[Mike Bayer‏]

triggers, ah. Maybe?   that seems inconvenient.   it would be good if you could adjust the triggers to not affect the rowcount of the original statement, otherwise you'd have to set supports_sane_rowcount=False on your engine:

engine.dialect.supports_sane_rowcount =False

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/811cc2da-18fc-4679-914f-e7d672c4bdcfn%40googlegroups.com.

[jue...@gmail.com‏]

So, I'm back to work and double checked if the "ID" is the primary key. And in fact, it is defined as "IDENTITY(1, 1)" with an additional "CONSTRAINT PK_Measurement" PRIMARY KEY (ID)" constraint. So on this side this should be correct. 

However, there is a trigger defined for this table and I think the trigger performs some actions if the IsCompleted column is set to TRUE. I don't have access to the triggers and need to contact the database administrator first, so for the moment I will use the "supports_sane_rowcount" workaround.