Flask-SQLAlchemy db.session.query(Model) vs Model.query

Question

This is a weird bug I've stumbled upon, and I am not sure why is it happening, whether it's a bug in SQLAlchemy, in Flask-SQLAlchemy, or any feature of Python I'm not yet aware of.

We are using Flask 0.11.1, with Flask-SQLAlchemy 2.1 using a PostgreSQL as DBMS.

Examples use the following code to update data from the database:

entry = Entry.query.get(1)
entry.name = 'New name'
db.session.commit()

This works totally fine when executing from the Flask shell, so the database is correctly configured. Now, our controller for updating entries, slightly simplified (without validation and other boilerplate), looks like this:

def details(id):
    entry = Entry.query.get(id)

    if entry:
        if request.method == 'POST':
            form = request.form
            entry.name = form['name']
            db.session.commit()
            flash('Updated successfully.')
        return render_template('/entry/details.html', entry=entry)
    else:
        flash('Entry not found.')
        return redirect(url_for('entry_list'))

# In the application the URLs are built dynamically, hence why this instead of @app.route
app.add_url_rule('/entry/details/<int:id>', 'entry_details', details, methods=['GET', 'POST'])

When I submit the form in details.html, I can see perfectly fine the changes, meaning the form has been submitted properly, is valid and that the model object has been updated. However, when I reload the page, the changes are gone, as if it had been rolled back by the DBMS.

I have enabled app.config['SQLALCHEMY_ECHO'] = True and I can see a "ROLLBACK" before my own manual commit.

If I change the line:

entry = Entry.query.get(id)

To:

entry = db.session.query(Entry).get(id)

As explained in https://stackoverflow.com/a/21806294/4454028, it does work as expected, so my guess what there was some kind of error in Flask-SQLAlchemy's Model.query implementation.

However, as I prefer the first construction, I did a quick modification to Flask-SQLAlchemy, and redefined the query @property from the original:

class _QueryProperty(object):

    def __init__(self, sa):
        self.sa = sa

    def __get__(self, obj, type):
        try:
            mapper = orm.class_mapper(type)
            if mapper:
                return type.query_class(mapper, session=self.sa.session())
        except UnmappedClassError:
            return None

To:

class _QueryProperty(object):

    def __init__(self, sa):
        self.sa = sa

    def __get__(self, obj, type):
        return self.sa.session.query(type)

Where sa is the Flask-SQLAlchemy object (ie db in the controller).

Now, this is where things got weird: it still doesn't save the changes. Code is exactly the same, yet the DBMS is still rolling back my changes.

I read that Flask-SQLAlchemy can execute a commit on teardown, and tried adding this:

app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

Suddenly, everything works. Question is: why?

Isn't teardown supposed to happen only when the view has finished rendering? Why is the modified Entry.query behaving different to db.session.query(Entry), even if the code is the same?

Answer 1

Below is the correct way to make changes to a model instance and commit them to the database:

# get an instance of the 'Entry' model
entry = Entry.query.get(1)

# change the attribute of the instance; here the 'name' attribute is changed
entry.name = 'New name'

# now, commit your changes to the database; this will flush all changes 
# in the current session to the database
db.session.commit()

Note: Don't use SQLALCHEMY_COMMIT_ON_TEARDOWN, as it's considered harmful and also removed from docs. See the changelog for version 2.0.

Edit: If you have two objects of normal session (created using sessionmaker()) instead of scoped session , then on calling db.session.add(entry) above code will raise error sqlalchemy.exc.InvalidRequestError: Object '' is already attached to session '2' (this is '3'). For more understanding about sqlalchemy session, read below section

Major Difference between Scoped Session vs. Normal Session

The session object we mostly constructed from the sessionmaker() call and used to communicate with our database is a normal session. If you call sessionmaker() a second time, you will get a new session object whose states are independent of the previous session. For example, suppose we have two session objects constructed in the following way:

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)


from sqlalchemy import create_engine
engine = create_engine('sqlite:///')

from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

# Construct the first session object
s1 = session()
# Construct the second session object
s2 = session()

Then, we won't be able to add the same User object to both s1 and s2 at the same time. In other words, an object can only be attached at most one unique session object.

>>> jessica = User(name='Jessica')
>>> s1.add(jessica)
>>> s2.add(jessica)
Traceback (most recent call last):
......
sqlalchemy.exc.InvalidRequestError: Object '' is already attached to session '2' (this is '3')

If the session objects are retrieved from a scoped_session object, however, then we don't have such an issue since the scoped_session object maintains a registry for the same session object.

>>> session_factory = sessionmaker(bind=engine)
>>> session = scoped_session(session_factory)
>>> s1 = session()
>>> s2 = session()
>>> jessica = User(name='Jessica')
>>> s1.add(jessica)
>>> s2.add(jessica)
>>> s1 is s2
True
>>> s1.commit()
>>> s2.query(User).filter(User.name == 'Jessica').one()

Notice thats1 and s2 are the same session object since they are both retrieved from a scoped_session object who maintains a reference to the same session object.

Tips

So, try to avoid creating more than one normal session object. Create one object of the session and use it everywhere from declaring models to querying.