If you return a modified version of the sql statement in a before_cursor_execute event, UPDATEs to models will fail against SQLite, but not against other RDBMs like Postgres.
It's common to add watermarking comments to all queries that explain the origin for use during debugging/load optimization. e.g.
@event.listens_for(engine, "before_cursor_execute", retval=True)
def watermark_sql_calls(conn, cursor, statement, parameters, context, executemany):
statement = "/* %s */\n%s" % ("this is a test", statement)
return statement, parameters
Complete failure example:
import datetime
import sqlalchemy
from sqlalchemy import Column, Integer, DateTime, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
engine = sqlalchemy.create_engine('sqlite:///test.sqlite').execution_options(autocommit=True)
Session = scoped_session(sessionmaker(bind=engine))
# Removing this event listener will fix the problem
@event.listens_for(engine, "before_cursor_execute", retval=True)
def watermark_sql_calls(conn, cursor, statement, parameters, context, executemany):
statement = "/* %s */\n%s" % ("watermarking bug", statement)
return statement, parameters
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
updated_at = Column(DateTime)
Base.metadata.create_all(engine)
foo = Foo()
session = Session()
session.add(foo)
session.commit()
foo.updated_at = datetime.datetime.now()
session.commit()
results in a failed row count update assertion:
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 954, in commit
self.transaction.commit()
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 467, in commit
self._prepare_impl()
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
self.session.flush()
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2313, in flush
self._flush(objects)
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2440, in _flush
transaction.rollback(_capture_exception=True)
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
raise value
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2404, in _flush
flush_context.execute()
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 395, in execute
rec.execute(self)
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 560, in execute
uow
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 177, in save_obj
mapper, table, update)
File "/Users/montanalow/.pyenv/versions/3.6.6/envs/lore/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 797, in _emit_update_statements
(table.description, len(records), rows))
sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'foo' expected to update 1 row(s); -1 were matched.
If you return a modified version of the sql statement in a
before_cursor_executeevent,UPDATEs to models will fail against SQLite, but not against other RDBMs like Postgres.It's common to add watermarking comments to all queries that explain the origin for use during debugging/load optimization. e.g.
Complete failure example:
results in a failed row count update assertion: