Why does a query cause autoload 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)] 

The displayed instance is still being added to the session. An instance wants to know (what a query in the database means) if other instances have their own type, which have the same value. There is a second attribute / column ( _nn ). It is specified in NOT NULL . But the default is NULL .

When the instance (as in the sample) is still added to the session, calling query.one() causes autostart. This flash will create an INSERT that tries to save the instance. This fails because _nn is still null and violates the NOT NULL .

This is what I understand now. But the question is why does it cause auto-flash? Can I block this?

 #!/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, the solution would be to not add the instance to the session before calling query.one() . This work. But in my real (but difficult for this question) precedent, this is not a good solution.

Answer 1

How to disable autoflush function:

• Temporarily: you can use the no_autoflush context manager in the fragment where you query the database, i.e. in X.test :

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

• System-wide: just pass autoflush=False your session player:

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