Flask-SQLAlchemy db.session.query(Model) vs Model.query
<p>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.</p>

<p>We are using Flask 0.11.1, with Flask-SQLAlchemy 2.1 using a PostgreSQL as DBMS.</p>

<p>Examples use the following code to update data from the database:</p>

<pre><code>entry = Entry.query.get(1)
entry.name = 'New name'
db.session.commit()
</code></pre>

<p>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:</p>

<pre><code>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/&lt;int:id&gt;', 'entry_details', details, methods=['GET', 'POST'])
</code></pre>

<p>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.</p>

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

<p>If I change the line:</p>

<pre><code>entry = Entry.query.get(id)
</code></pre>

<p>To:</p>

<pre><code>entry = db.session.query(Entry).get(id)
</code></pre>

<p>As explained in <a href="http://stackoverflow.com/a/21806294/4454028">http://stackoverflow.com/a/21806294/4454028</a>, it does work as expected, so my guess what there was some kind of error in Flask-SQLAlchemy's <code>Model.query</code> implementation.</p>

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

<pre><code>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
</code></pre>

<p>To:</p>

<pre><code>class _QueryProperty(object):

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

    def __get__(self, obj, type):
        return self.sa.session.query(type)
</code></pre>

<p>Where <code>sa</code> is the Flask-SQLAlchemy object (ie <code>db</code> in the controller).</p>

<p>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.</p>

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

<pre><code>app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
</code></pre>

<p>Suddenly, everything works. Question is: why?</p>

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

<p>So, the correct way to made changes in model instance and also committed them to DB is looks like this:-</p>

<pre><code># get the instance of `Entry` 
entry = Entry.query.get(1)

# change the attribute of an instance which you want, here `name` attribute is changed
entry.name = 'New name'

# now, commit your changes into the DB, this will flush all changes 
# in current session to DB
db.session.commit()
</code></pre>

<p><strong>Note:</strong> Don't use <code>SQLALCHEMY_COMMIT_ON_TEARDOWN</code> , as it's considered harmful and also removed from docs. <a href="http://flask-sqlalchemy.pocoo.org/2.1/changelog/" rel="nofollow">See here</a> in changelog of version 2.0.</p>

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

<h2>Major Difference between Scoped Session vs. Normal Session</h2>

<p>The session object we mostly constructed from the <code>sessionmaker()</code> call and used to communicate with our database is a <strong>normal session</strong>. If you call <code>sessionmaker()</code> 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:</p>

<pre><code>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()
</code></pre>

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

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

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

<pre><code>&gt;&gt;&gt; session_factory = sessionmaker(bind=engine)
&gt;&gt;&gt; session = scoped_session(session_factory)
&gt;&gt;&gt; s1 = session()
&gt;&gt;&gt; s2 = session()
&gt;&gt;&gt; jessica = User(name='Jessica')
&gt;&gt;&gt; s1.add(jessica)
&gt;&gt;&gt; s2.add(jessica)
&gt;&gt;&gt; s1 is s2
True
&gt;&gt;&gt; s1.commit()
&gt;&gt;&gt; s2.query(User).filter(User.name == 'Jessica').one()
</code></pre>

<p>Notice that<code>s1</code> and <code>s2</code> are the same session object since they are both retrieved from a <code>scoped_session</code> object who maintains a reference to the same session object.</p>

<h2>Tips</h2>

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

python postgresql flask sqlalchemy flask-sqlalchemy