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

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

# get an instance of the 'Entry' modelentry = Entry.query.get(1)# change the attribute of the instance; here the 'name' attribute is changedentry.name = 'New name'# now, commit your changes to the database; this will flush all changes # in the current session to the databasedb.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, ForeignKeyfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()class User(Base):    __tablename__ = 'user'    id = Column(Integer, primary_key=True)    name = Column(String)from sqlalchemy import create_engineengine = create_engine('sqlite:///')from sqlalchemy.orm import sessionmakersession = sessionmaker()session.configure(bind=engine)Base.metadata.create_all(engine)# Construct the first session objects1 = session()# Construct the second session objects2 = 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 s2True>>> 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.

Our project is separated in several files to ease mantenaince. One is routes.py with the controllers, and another one is models.py, which contains the SQLAlchemy instance and models.

So, while I was removing boilerplate to get a minimal working Flask project to upload it to a git repository to link it here, I found the cause.

Apparently, the reason is that my workmate, while attempting to insert data using queries instead of the model objects (no, I have no idea why on earth he wanted to do that, but he spent a whole day coding it), had defined another SQLAlchemy instance in the routes.py.

So, when I was trying to insert data from the Flask shell using:

from .models import *entry = Entry.query.get(1)entry.name = 'modified'db.session.commit()

I was using the correct db object, as defined in models.py, and it was working completely fine.

However, as in routes.py there was another db defined after the model import, this one was overwriting the reference to the correct SQLAlchemy instance, so I was commiting with a different session.