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.
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 ''
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 ''
You could also send a raw SQL statement using the mechanism in (2.), by replacing
log_table.insert
withsa.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.