How can my Flask app check whether a SQLite3 transaction is in progress?

Question

I am trying to build some smart error messages using the @app.errorhandler (500) feature. For example, my route includes an INSERT command to the database:

if request.method == "POST":
   userID = int(request.form.get("userID"))
   topicID = int(request.form.get("topicID"))
   db.execute("BEGIN TRANSACTION")
   db.execute("INSERT INTO UserToTopic (userID,topicID) VALUES (?,?)", userID, topicID)
   db.execute("COMMIT")

If that transaction violates a constraint, such as UNIQUE or FOREIGN_KEY, I want to catch the error and display a user-friendly message. To do this, I'm using the Flask @app.errorhandler as follows:

@app.errorhandler(500)
def internal_error(error):
    db.execute("ROLLBACK")
    return render_template('500.html'), 500

The "ROLLBACK" command works fine if I'm in the middle of a database transaction. But sometimes the 500 error is not related to the db, and in those cases the ROLLBACK statement itself causes an error, because you can't rollback a transaction that never started. So I'm looking for a method that returns a Boolean value that would be true if a db transaction is under way, and false if not, so I can use it to make the ROLLBACK conditional. The only one I can find in the SQLite3 documentation is for a C interface, and I can't get it to work with my Python code. Any suggestions?

I know that if I'm careful enough with my forms and routes, I can prevent 99% of potential violations of db rules. But I would still like a smart error catcher to protect me for the other 1%.

Answer 1

I don't know how transaction works in sqlite but what you are trying to do, you can achieve it by try/except statements

1. use try/except within the function

try:
    db.execute("ROLLBACK")
except:
    pass
return render_template('500.html'), 500

2. Use try/except when inserting data.

from flask import abort
try:
    userID = int(request.form.get("userID"))
    [...]
except:
    db.rollback()
    abort(500)

I am not familiar with sqlite errors, if you know what specific error occurs except for that specific error.