How does Flask start a new SQLAlchemy transaction at the start of each request?

Question

I tried to totally seperate Flask and SQLAlchemy using this method but Flask still seems to be able to detect my database and start a new transaction at the beginning of each request.

The db.py file creates a new session and defines a simple model of a table:

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String


engine = create_engine("mysql://web:kingtezdu@localhost/web_unique")

print("creating new session")
db_session = scoped_session(sessionmaker(bind=engine))

Base = declarative_base()
Base.query = db_session.query_property()


# define model of 'persons' table
class Person(Base):
    __tablename__ = "persons"
    name = Column(String(30), primary_key=True)

    def __repr__(self):
        return "Person(\"{0.name}\")".format(self)

# create table
Base.metadata.create_all(bind=engine)

And app.py, a simple Flask application using the SQLAlchemy session and model:

from flask import Flask, escape
app = Flask(__name__)


# importing new session
from db import db_session, Person

# registering for app teardown to remove session
@app.teardown_appcontext
def shutdown_session(exception=None):
    db_session.remove()


@app.route("/query")
def query():
    # query all persons in the database
    all_persons = Person.query.all()
    print all_persons
    return "" # we use the console output


if __name__ == "__main__":
    app.run(debug=True)

Let's run this:

$ python app.py 
creating new session
 * Running on http://127.0.0.1:5000/
 * Restarting with reloader
creating new session

Weired enough it runs db.py two times but we just ignore this, let's access the webpage /query:

[]
127.0.0.1 - - [23/Dec/2015 18:20:14] "GET /query HTTP/1.1" 200 -

We can see that our request was answered, though we only use the console output. There is no Person in the database yet, let's add one:

mysql> INSERT INTO persons (name) VALUES ("Marie");
Query OK, 1 row affected (0.11 sec)

Marie is part of the database now so we reload the webpage:

[Person("Marie")]
127.0.0.1 - - [23/Dec/2015 18:24:48] "GET /query HTTP/1.1" 200 -

As you can see the session already knows about Marie. Flask didn't create a new session. That means that there was a new transaction started. Contrast this to the plan python example below to see the difference.

My question is how Flask is able to start a new transaction on the begin of each request. Flask shouldn't know about the database but seems to be able to change something about it's behaviour.

In case you don't know what a SQLAlchemy transaction is read this paragraph extracted from Managing Transactions:

When the transactional state is completed after a rollback or commit, the Session releases all Transaction and Connection resources, and goes back to the “begin” state, which will again invoke new Connection and Transaction objects as new requests to emit SQL statements are received.

So a transaction is ended by a commit and will cause a new connection to be set up which will then make the session read the database again. In reality this means that you have to commit when you want to see changes made to the database:

First in interactive python mode:

>>> from db import db_session, Person
creating new session
>>> Person.query.all()
[]

Switch over to MySQL and insert a new Person:

mysql> INSERT INTO persons (name) VALUES ("Paul");
Query OK, 1 row affected (0.03 sec)

Finally try to load Paul into our session:

>>> Person.query.all()
[]
>>> db_session.commit()
>>> Person.query.all()
[Person("Paul")]

pvg pvg · Accepted Answer · 2015-12-23T18:27:06

I think the issue here is that scoped_session somewhat hides what happens to the actual sessions in use. When your teardown handler

# registering for app teardown to remove session
@app.teardown_appcontext
def shutdown_session(exception=None):
    db_session.remove()

runs at the end of each request, you call db_session.remove() which disposes of the session used in that particular request along with any transaction context. See http://docs.sqlalchemy.org/en/latest/orm/contextual.html for the details, particularly

The scoped_session.remove() method first calls Session.close() on the current Session, which has the effect of releasing any connection/transactional resources owned by the Session first, then discarding the Session itself. “Releasing” here means that connections are returned to their connection pool and any transactional state is rolled back, ultimately using the rollback() method of the underlying DBAPI connection.

How does Flask start a new SQLAlchemy transaction at the start of each request?

1 Answers