SQLAlchemy data not consistent across connections

Question

I have a couple python apps (separate processes) that both use Flask-SQLAlchemy to access a sqlite database. One of them is set up using the asyncio module (but is still a Flask app) and the other is a Flask-RESTful API.

I'm seeing an issue in which data written to the database by the Flask-RESTful API is not being seen by the asyncio app. I've read that this is likely due to isolation levels (How to disable SQLAlchemy caching?). If I use session.expire_all() the issue goes away.

So is this really due to isolation levels? My understanding is that sqlite uses the serializable isolation level almost all the time (I believe it is in my case). The REST API, which is doing the writing, is committing its transactions but the asyncio code just doesn't see them unless I call session.expire_all(). This seems lees like a transaction isolation issue and more like a SQLAlchemy issue but I'm not 100% sure.

Do I just have to always remember to call session.expire_all() before querying the database? Is there a better way to keep things consistent between apps?

Here is some code if it helps:

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

app = Flask(__name__)    
db = SQLAlchemy(app)

@asyncio.coroutine
def grab_data_off_queue(self):
    while True:
        try:
            hit = self.hit_queue.get(False)

            # required for this session to see the changes made by REST api
            db.session.expire_all()

            rows = MyTable.query.filter_by.all()

            for row in rows:
                # the REST API is setting field_2 to 123,
                # but this session cannot see those changes
                if row.field_2 == 123:
                     do_something() #execution never gets here

Here is the REST API:

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

app = Flask(__name__)    
db = SQLAlchemy(app)

class MyApi(Resource):
    def put(self, id):
         row = MyTable.query.filter_by(table_id=id).one()
         row.field_2 = 123
         db.session.commit()
         return '', 204

Answer 1

SQLAlchemy doesn't commit changes by default until handler exits, you need call session.commit() explicitly.

But using synchronous SQLAlchemy code from async coroutine effectively kills performance even under relative moderate load you know.