I'm trying to recover after a primary key violation in an insert, by logging the duplicate user to another table. However, my code produces the error
InvalidRequestError: This transaction is inactive.
Unfortunately the traceback doesn't show the specific line within this function where the error occurs--it only goes as deep as the function calling this function (which is strange).
Does my try/except begin/rollback/commit pattern look correct?
def convert_email(db, user_id, response): """Map a submitted email address to a user, if the user does not yet have an email address. """ email = response['text_response'] trans = db.begin() try: update_stmt = users_tbl.update( and_(users_tbl.c.user_id==user_id, users_tbl.c.email==None)) db.execute(update_stmt.values(dict(email=email))) trans.commit() except sqlalchemy.exc.IntegrityError as e: trans.rollback() if e.orig.pgcode == UNIQUE_VIOLATION: trans = db.begin() try: user = db.execute(users_tbl.select(users_tbl.c.email==email)) parent_user_id = user.first()['user_id'] insert_stmt = duplicate_public_users_tbl.insert().values( user_id=user_id, parent_id=parent_user_id) db.execute(insert_stmt) trans.commit() except sqlalchemy.exc.IntegrityError as e: trans.rollback() if e.orig.pgcode != UNIQUE_VIOLATION: raise