python - Issue with Stale data Flask/SqlAlchemy

Question

I have the following set up for which on session.query() SqlAlchemy returns stale data:

Web application running on Flask with Gunicorn + supervisor. one of the services is composed in this way:

• app.py:

  @app.route('/api/generatepoinvoice', methods=["POST"])
  @auth.login_required
  def generate_po_invoice():
  try:
     po_id = request.json['po_id']
     email = request.json['email']
     return jsonify(response=POInvoiceGenerator.get_invoice(po_id, email))
  except Exception as ex:
      app.logger.error("generate_po_invoice(): " + ex.message)
  

in another folder i have the database related stuff:

DatabaseModels (folder)

|-->Model.py

|-->Connection.py

that's what is contained in the connection.py file:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(DB_BASE_URI, isolation_level="READ COMMITTED")
Session = scoped_session(sessionmaker(bind=engine))
session = Session()
Base = declarative_base()

and thats an extract of the model.py file:

from DatabaseModels.Connection import Base
from sqlalchemy import Column, String, etc...

class Po(Base):
    __tablename__ = 'PLC_PO'

    id = Column("POId", Integer, primary_key=True)
    code = Column("POCode", String(50))
    etc...

Then i have another file POInvoiceGenerator.py that contains the call to the database for fetching some data:

import DatabaseModels.Connection as connection
import DatabaseModels.model as model
def get_invoice(po_code, email):
    try:
        po_code = po_code.strip()
        PLCConnection.session.expire_all()
        po = connection.session.query(model.Po).filter(model.Po.code == po_code).first()

    except Exception as ex:
        logger.error("get_invoice(): " + ex.message)

in subsequent users calls to this service sometimes i start to get errors like: could not find data in the db for that specific code and so on. Like if the data are stale and so on.

My first approach was to add isolation_level="READ COMMITTED" to the engine declaration and then to create a scoped session, but the stale data reading keeps appening.

Is there anyone that had any idea if my setup is wrong (the session and the model are reused among multiple methods and files)

Thanks in advance.

标签： pythonflasksqlalchemygunicornsupervisord

Answer 1

even if the solution pointed by @TonyMountax seems valid and made me discover something that i didn't know about SqlAlchemy, In the end i opted for something different.

I figured out that the connection established by SqlAlchemy was durable since it was created from a pool of connection everytime, this somehow was causing the data to be stale.

i added a NullPool to my code:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.pool import NullPool

engine = create_engine(DB_URI, isolation_level="READ COMMITTED", poolclass=NullPool)
Session = scoped_session(sessionmaker(bind=engine))
session = Session()

and then i'm calling a session close for every query that i make:

session.query("some query..")
session.close()

this will cause SqlAlchemy to create a new connection every time and get fresh data from the db.

Hope that this is the correct way to use it and that might be useful to someone else.