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](http://flask.pocoo.org/docs/0.10/patterns/sqlalchemy/#declarative) 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.

Thanks for answers in advance,

\- timgame

* * *

In case you don't know what a SQLAlchemy transaction is read this paragraph
extracted from [Managing
Transactions](http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#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")]
    


Answer: 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.

