0

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] }
Marek Pastierik
  • 3
  • 1
  • 2
  • Why would you need to do either? – roganjosh Aug 13 '20 at 22:44
  • Reading a database doesn't change it, so there's nothing to commit. And since there's no transaction to commit, there's nothing to roll back – roganjosh Aug 13 '20 at 22:45
  • But if I am right, by default the query command will create a new transaction. So will be this transaction closed after GET request or do I have to close it on my own? – Marek Pastierik Aug 13 '20 at 22:58
  • Ok, now I understand your confusion – roganjosh Aug 13 '20 at 23:03
  • I think you have to close it on your own, because you cannot rely on closing session after http request. If It if that were the case, It would mean that you can wrap all your code, which modifies (add,delete, update) DB, into API endpoints and after that it will automatically end transactions without doing anything. – Marek Pastierik Aug 13 '20 at 23:22

1 Answers1

2

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

roganjosh
  • 12,594
  • 4
  • 29
  • 46