11

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!

Thibault Martin
  • 509
  • 2
  • 5
  • 16

1 Answers1

8

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.

MarredCheese
  • 17,541
  • 8
  • 92
  • 91
Rachel Sanders
  • 5,734
  • 1
  • 27
  • 36
  • You made my day. Still, I'd like to really understand what happens here : setting the `db` object creates a session, and any request created from the Model.query function creates other concurrent sessions? – Thibault Martin Jul 29 '14 at 21:33
  • 3
    Awesome! I'm glad. And yes, that's right. If you look at the setup docs (https://pythonhosted.org/Flask-SQLAlchemy/quickstart.html), notice that you create db = SQLAlchemy() and then your models inherit from db.Model. When you call MyModel.query.all() it uses that session. You created a new session (entirely fine) but they don't automatically talk. I believe you can tell SQLAlchemy to manually expire caches, though, but I don't know that command offhand. The docs probably have it. – Rachel Sanders Jul 29 '14 at 21:45
  • 1
    Thanks so much for the help, I'll get through the docs. Impressive CV btw, inspiring for us students! – Thibault Martin Jul 29 '14 at 21:48
  • I am facing exact same issue on flask server running on EC2. whereas its working fine on my local machine. Dodn't worked even after expiring the session like this https://stackoverflow.com/a/21917724/5167801. Did you made it work ? – Anum Sheraz Sep 08 '19 at 13:59
  • UPDATE: My issue was, I was running flask app with guniwork webserver using 3 workers, and cache was not same for all three workers. Reducing them to 1 resolved. – Anum Sheraz Sep 08 '19 at 20:51