Isolation Level with Flask-SQLAlchemy

Question

I find it difficult to understand 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 am using Flask-SQLAlchemy for a Flask project with a MySQL database. This is how my project is set up

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' 

Creating a 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 the python terminal, and although I read about the isolation level Read committed SQLAlchemy, I am facing the following problem. Here is what I have on the 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 browse the database, my change was committed:

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

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

Although I understand that SQLAlchemy expects the changes to be committed and / or the session will be closed, I do not understand why I cannot read my changes after the db.session.commit() statement and how to close the session (I tried db.session.close() , reading the database after that does not give the best results)

Thank you for your help!

Answer 1

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

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

I am wondering if two sessions are taking place:

1. One that sets up your application with which Printer.query.all() talking
2. The one you use in this snippet that you created with this db = SQLAlchemy() calls

This explains why db.session.commit() does not db.session.commit() data that Printer.query.all() .