[tip] StaleDataError when using Python and SQLAlchemy to update record

[renyuneyun]

(This is mainly not a question, but a tip. But digging deeper this is still a question to sqlite-zstd developer.)

I'm using Python with SQLAlchemy (SQLModel as the helper library) with sqlite-zstd. I encountered this error when trying to update a record after enabling sqlite-zstd compression on my table:

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

It turns out to be indeed related to sqlite-zstd: if compression is enabled and trying to update a row, SQLAlchemy will fail to verify the number of modified rows (c.f. [1]). In fact, this is mentioned (though not appearing to be related at a first glance) in the README:

• sqlite3_changes() will return 0 for modifying queries (see here).

I found a solution: turn off this feature of SQLAlchemy ([ref]):

engine = create_engine(".... your engine string normally  .. ")
engine.dialect.supports_sane_rowcount = False

Hope this helps others like me, who are not experienced sqlite or sqlalchemy developers/users but wanting to do something little but helps reducing size of database.

In the meantime, I'd be curious to know why must sqlite3_changes() return 0? Is it a temporary trick, or is there some deep reason?

(Feel free to close this issue if that's more appropriate.)