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>Our project is separated in several files to ease mantenaince. One is <code>routes.py</code> with the controllers, and another one is <code>models.py</code>, which contains the SQLAlchemy instance and models.</p>

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

<p>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), <strong>had defined another SQLAlchemy instance in the <code>routes.py</code></strong>.</p>

<p>So, when I was trying to insert data from the Flask shell using:</p>

<pre><code>from .models import *
entry = Entry.query.get(1)
entry.name = 'modified'
db.session.commit()
</code></pre>

<p>I was using the correct <code>db</code> object, as defined in <code>models.py</code>, and it was working completely fine.</p>

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

python postgresql flask sqlalchemy flask-sqlalchemy