CODEWITHSUNDEEP
sql-serversqlalchemyrowcount
希德尼娅的骑士
希德尼娅的骑士

Reputation: 51

SqlAlchemy StaleDataError returns -1 when update record on mssql

I'm using flask-sqlalchemy to update mssql record, but it returns -1.

Library versions:

  1. SQLAlchemy 1.3.11
  2. Flask-SQLAlchemy 2.4.1
  3. pyodbc 4.0.27
  4. flask 1.1.1

Code part 1:

ret = db.session.query(XXX).filter_by(id=1).update({"xxx": "xxxx"})
print("ret", ret)
db.session.commit()

The ret is -1, but the record has been modified .

Code part 2:

obj = XXX.query.filter_by(id=q).first()
obj .xx = "xxx"
db.session.commit()

Raise error: 

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

And the modify did not successed.

Upvotes: 5

Views: 641

Answers (1)

tamersalama
tamersalama

Reputation: 4143

According to SQLAlchemy documentation, there's currently a limitation with some versions of SQL Server drivers not returning the number of records for UPDATE and DELETE statements. I'm currently faced with the issue on Linux, but it's working fine on Windows.

Here's also a related SQL Alchemy issue

I used the column as a version indicator (the documentation recommended creating a SQLServer ROWVERSION however, SQLAlchemy/PyODBC, again on Linux, was not able to assign proper FetchedValues() from the database as bytes into the fields. I also tried using a DateTime2 field - however again for SQLAlchemy accuracy when mapping the field to Python (7 precision)

I ended up implementing the following change:

Since I had the ID column already assigned by the database (IDENTITY), I used that field as the version indicator.

    __mapper_args__ = {
        'version_id_col': id_column,
        'version_id_generator': False,
    }

The SQLAlchemy update statements now looks like:

UPDATE <TABLE> SET <column>=? OUTPUT inserted.<ID-COLUMN> WHERE <TABLE>.<ID-COLUMNS> <ID-COLUMN> = ? AND <TABLE>.<ID-COLUMN> = ?

[('updated data', 123456, 123456)]

Upvotes: 3

Related Questions