Why does a query invoke a auto-flush in SQLAlchemy?

Question

The code you see above is just a sample but it works to reproduce this error:

sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush;  consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) NOT NULL constraint failed: X.nn  [SQL: 'INSERT INTO "X" (nn, val) VALUES (?, ?)'] [parameters: (None, 1)] 

A mapped instance is still added to a session. The instance wants to know (which means query on the database) if other instances its own type exists having the same values. There is a second attribute/column (_nn). It is specified to NOT NULL. But by default it is NULL.

When the instance (like in the sample) is still added to the session a call to query.one() invoke a auto-flush. This flush create an INSERT which tries to store the instance. This fails because _nn is still null and violates the NOT NULL constraint.

That is what I understand currently. But the question is why does it invoke an auto-flush? Can I block that?

#!/usr/bin/env python3  import os.path import os import sqlalchemy as sa  import sqlalchemy.orm as sao import sqlalchemy.ext.declarative as sad from sqlalchemy_utils import create_database  _Base = sad.declarative_base() session = None   class X(_Base):     __tablename__ = 'X'      _oid = sa.Column('oid', sa.Integer, primary_key=True)     _nn = sa.Column('nn', sa.Integer, nullable=False) # NOT NULL!     _val = sa.Column('val', sa.Integer)      def __init__(self, val):         self._val = val      def test(self, session):         q = session.query(X).filter(X._val == self._val)         x = q.one()         print('x={}'.format(x))  dbfile = 'x.db'  def _create_database():     if os.path.exists(dbfile):         os.remove(dbfile)      engine = sa.create_engine('sqlite:///{}'.format(dbfile), echo=True)     create_database(engine.url)     _Base.metadata.create_all(engine)     return sao.sessionmaker(bind=engine)()   if __name__ == '__main__':     session = _create_database()      for val in range(3):         x = X(val)         x._nn = 0         session.add(x)     session.commit()      x = X(1)     session.add(x)     x.test(session) 

Of course a solution would be to not add the instance to the session before query.one() was called. This work. But in my real (but to complex for this question) use-case it isn't a nice solution.

Answer 1

How to turn off autoflush feature:

• Temporary: you can use no_autoflush context manager on snippet where you query the database, i.e. in X.test method:

  def test(self, session):     with session.no_autoflush:         q = session.query(X).filter(X._val == self._val)         x = q.one()         print('x={}'.format(x)) 

• Session-wide: just pass autoflush=False to your sessionmaker:

  return sao.sessionmaker(bind=engine, autoflush=False)() 

Answer 2

I know this is old but it might be helpful for some others who are getting this error while using flask-sqlalchemy. The below code has fixed my issue with autoflush.

db = SQLAlchemy(session_options={"autoflush": False})