title: How does Flask start a new SQLAlchemy transaction at the start of each request?
body: <p>I tried to totally seperate Flask and SQLAlchemy using <a href="http://flask.pocoo.org/docs/0.10/patterns/sqlalchemy/#declarative" rel="nofollow noreferrer">this method</a> but Flask still seems to be able to detect my database and start a new transaction at the beginning of each request.</p>
  
  <p>The <code>db.py</code> file creates a new session and defines a simple model of a table:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>And <code>app.py</code>, a simple Flask application using the SQLAlchemy session and model:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Let's run this:</p>
  
  <pre><code>$ python app.py 
  creating new session
   * Running on http://127.0.0.1:5000/
   * Restarting with reloader
  creating new session
  </code></pre>
  
  <p>Weired enough it runs <code>db.py</code> two times but we just ignore this, let's access the webpage <code>/query</code>:</p>
  
  <pre><code>[]
  127.0.0.1 - - [23/Dec/2015 18:20:14] "GET /query HTTP/1.1" 200 -
  </code></pre>
  
  <p>We can see that our request was answered, though we only use the console output. There is no <code>Person</code> in the database yet, let's add one:</p>
  
  <pre><code>mysql&gt; INSERT INTO persons (name) VALUES ("Marie");
  Query OK, 1 row affected (0.11 sec)
  </code></pre>
  
  <p><code>Marie</code> is part of the database now so we reload the webpage:</p>
  
  <pre><code>[Person("Marie")]
  127.0.0.1 - - [23/Dec/2015 18:24:48] "GET /query HTTP/1.1" 200 -
  </code></pre>
  
  <p>As you can see the session already knows about <code>Marie</code>. 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.</p>
  
  <p>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.</p>
  
  <hr>
  
  <p>In case you don't know what a SQLAlchemy transaction is read this paragraph extracted from <a href="http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#managing-transactions" rel="nofollow noreferrer">Managing Transactions</a>:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>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:</p>
  
  <p>First in interactive python mode:</p>
  
  <pre><code>&gt;&gt;&gt; from db import db_session, Person
  creating new session
  &gt;&gt;&gt; Person.query.all()
  []
  </code></pre>
  
  <p>Switch over to MySQL and insert a new <code>Person</code>:</p>
  
  <pre><code>mysql&gt; INSERT INTO persons (name) VALUES ("Paul");
  Query OK, 1 row affected (0.03 sec)
  </code></pre>
  
  <p>Finally try to load <code>Paul</code> into our session:</p>
  
  <pre><code>&gt;&gt;&gt; Person.query.all()
  []
  &gt;&gt;&gt; db_session.commit()
  &gt;&gt;&gt; Person.query.all()
  [Person("Paul")]
  </code></pre>