flask sqlalchemy insert without session

Question

HI is there is any way that I can insert a row to db without using session. A simple Example:

try:
   db.session.add(user1)
   #in here I want to insert a row to my db but I can't do it with session because if i commit in here it will commit all inserts so my transaction not work.
   db.session.add(user2)
except:
   db.session.rollback()
else:
   db.session.commit()

thank you

Answer 1

If you want to commit changes independently of the default db.session there a couple of possibilities.

1. If you need an actual session, create one using SQLAlchemy and use it for your log entries:

   from sqlalchemy import orm
   ...
   @app.route('/')
   def index():
       model = MyModel(name='M1')
       db.session.add(model)
       with orm.Session(db.engine).begin() as log_session:
           # Session.begin will commit automatically.
           log = MyLog(message='hello')
           log_session.add(log)
       return ''
   

2. If you are just inserting entries in the log table you can just connect using the engine.

   import sqlalchemy as sa
   ...
   @app.route('/')
   def index():
       model = MyModel(name='M1')
       db.session.add(model)
       log_table = sa.Table('my_log', db.metadata, autoload_with=db.engine)
       with db.engine.begin() as conn:
           conn.execute(log_table.insert(), {'message': 'hello'})
           db.session.rollback()
       return ''
   

3. You could also send a raw SQL statement using the mechanism in (2.), by replacing log_table.insert with sa.text(sql_string)

How ever you choose to do this be aware that:

• Due to transaction isolation, you two transactions may have different views of the data in the database
• You are responsible for making sure these additional sessions/transactions/connections are rolled back, committed and closed as necessary
• You are responsible for handling problem scenarios, for example if an error causes the db.session to roll back, making log messages potentially invalid.