Home > Software engineering >  flask sqlalchemy insert without session
flask sqlalchemy insert without session

Time:05-15

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

CodePudding user response:

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.
  • Related