Python flask-sqlalchemy: Do I have to commit session after a query?

Tags: , , , ,



I am writing an app in python flask-sqlalchemy with MySQL DB (https://flask-sqlalchemy.palletsprojects.com/en/2.x/) and I am wondering if I have to make “db.session.commit() or db.session.rollback()” after GET call, which only query DB .

For example:

@app.route('/getOrders')
def getOrders():
    orders = Order.query.all()
    # Do I have to put here "db.session.commit()" or "db.session.rollback" ?
    return { 'orders': [order.serialize() for order in orders] }

Answer

orders = Order.query.all() is a SELECT query that could be extended to include additional filters (WHERE etc.). It doesn’t alter the database, it simply reads values from it. You don’t need to commit on a read for precisely that reason – what would you store other than “I just read this data”? Databases are concerned about this in other ways i.e. access permissions and logs.

Given the above, rollback doesn’t make any sense because there are no changes to actually roll back.

Flask-SQLAlchemy does a bit of magic around sessions amongst other things. It’s roughly equivalent to:

from sqlalchemy.orm import scoped_session, sessionmaker

Session = sessionmaker(bind=engine, autocommit=False, autoflush=False)
db_session = scoped_session(Session)

Followed with a method to close sessions:

def init_db(app):
    app.teardown_appcontext(teardown_session)

def teardown_session(exception=None):
    db_session.remove()

Bottom line being: no, you don’t have to worry about commit or rollback here, even in SQL, and the session management (completely separate) is handled by Flask-SQLALchemy



Source: stackoverflow