Isolation level with Flask-SQLAlchemy

Question

I'm having trouble understanding how database isolation levels work with Flask-SQLAlchemy, and especially how to really commit changes or close a session. Here is the context of my problem :

I'm using Flask-SQLAlchemy for a Flask project with a MySQL database. Here is how is configured my project

SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://user:passwd@localhost/mydb'
SQLALCHEMY_MIGRATE_REPO = '/path/to/myapp/db_repository'

CSRF_ENABLED = True
SECRET_KEY = 'this is a secret'

The creation of the db object in my __init__.py file:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
import config

app = Flask(__name__)
app.config.from_object('config')
db = SQLAlchemy(app)

I have defined models such as Printer one:

from myapp import db
...
class Printer(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120))

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

I tried to play around with a python terminal, and even though I read about the Read committed isolation level of SQLAlchemy, I face the following problem. Here is what I have in my console:

>>> from myapp import app
>>> from myapp.models import Printer
>>> import config
>>> from flask.ext.sqlalchemy import SQLAlchemy
>>> app.config.from_object('config')
>>> db = SQLAlchemy(app)
>>> for printer in Printer.query.all():
...     print printer.name
...
Circle
Mww
>>> p = Printer('dummy')
>>> db.session.add(p)
>>> db.session.commit()
>>> for printer in Printer.query.all():
...     print printer.name
...
Circle
Mww
>>> 

When I look up the database, my change has been committed :

mysql> SELECT * FROM printer;
+----+--------+
| id | name   |
+----+--------+
|  1 | Circle |
|  2 | Mww    |
|  3 | dummy  |
+----+--------+
3 rows in set (0.00 sec)

If I quit my python terminal, open it again and just read the results with Printer.query.all(), my changes appear.

Though I understand SQLAlchemy waits for change to be committed and/or the session to be closed, I don't get why I can't read my changes after the db.session.commit() statement nor how to close the session (I tried db.session.close(), reading the database after that does not give better results)

Thanks for your help!

Answer 1

What happens if you use the SQLAlchemy version of the query?

db.session.query(Printer).all() 

I'm wondering if there are two sessions going on:

1. The one your app sets up that Printer.query.all() is talking to
2. The one you're using in that snippet that you created by that db = SQLAlchemy() call

It would explain why the db.session.commit() isn't flushing the data that Printer.query.all() has.