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.

Palasaty · Accepted Answer · 2016-02-18 09:33:51Z

76

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)()
```

answered Feb 18, 2016 at 9:33

Palasaty

5,6092 gold badges28 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jonathan Over a year ago

Can someone explain why this is needed?

Palasaty Over a year ago

@JonathanLeaders In the answer there is a link to the docs where autoflush feature is explained. If something remains unclear you'd better ask specific question.

Jonathan Over a year ago

It turns out I wasn't calling db.commit() when I was supposed to, so it made me think the autoflush wasn't working as expected, so my question was based on an error.

Goodword Over a year ago

@JonathanLeaders: quote from the no_autoflush section of the docs linked above: "This is useful when initializing a series of objects which involve existing database queries, where the uncompleted object should not yet be flushed."

shalama Over a year ago

This is very helpful for writing unit tests that involves multiple sqla mock objects. With autoflush sqla persists data on uncompleted objects. What leads to this Exception.

|

Süleyman Sümertaş · Accepted Answer · 2021-03-01 14:53:31Z

10

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})

answered Mar 1, 2021 at 14:53

Süleyman Sümertaş

5416 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related