How do deal with transactions in sqlalchemy or disable them in non-deprecated way

user_1466713 0
Viewed 285

Im struggling with transactions in SQLAlchemy. So apparently there is always a transaction (unless we use autocommit=True, which is deprecated), and if someone wants to get stuff persisted, we need to call commit, which will, after flushing, create a new transaction in the end. Now my problem is that a lot of users are working on the codebase, so it becomes very complicated to keep control of transactions in the original sense. Imagine we have something like this:

db_session.begin_nested()
try:
    method_that_triggers_a_lot()
except:
    session.rollback()
session.commit()

This only works as-expected as long as we have one or no commit called under method_that_triggers_a_lot execution. Im aware of that, but other developers might not. So if someone adds another commit somewhere under method_that_triggers_a_lot, i would loose my expected behavior. Im not quite sure if im missing something, other ORMs like doctrine dont ship with a transaction around everything, so you have to explicitly declare begin to open one. As this always goes in pairs, you never loose the expected-behavior. I read that i might be able to use autocommit=True to get rid of the automatic transactions, but as that is marked as deprecated, im wondering if there is a better way of dealing with this. Is there something like "named" transactions, where i could do something like 

session.begin_nested("my_transaction")
try:
    session.add(Whatever())
    session.commit()
    session.commit()
except:
    session.rollback("my_transaction")
session.commit("my_transaction")

From what i got, neither subtransactions nor nested can help here. Or is there a non-deprecated way to work without default-transactions around everything?

sqlalchemy transactions orm
1 Answers

This problem seems to be fixed in v1.4, where you can use a context-manager. This works as-expected in 1.4:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine(<DSN>)

Base = declarative_base()
session = Session(engine)

class MyRecord(Base):
    __tablename__ = 'my_record'
    id = Column(Integer, primary_key=True)
    something = Column(String)
    institution_name = Column(String)


def change_name(source_record: MyRecord, name):
    source_record.institution_name = name
    session.commit()


def print_records():
    rows = session.query(MyRecord).filter(MyRecord.something == 'testing').all()
    for r in rows:
        print(f'id {r.id} - {r.institution_name}')


record = MyRecord(something='testing')
record.institution_name = 'test'
session.add(record)
session.commit()

print_records()

with Session(engine) as session:
    change_name(record, 'change one')
    change_name(record, 'change two')
    session.rollback()

print_records()

this gives something like this.

id 2 - test
id 2 - test

while the similar snippet in 1.3:

session.begin_nested()
change_name(record, 'change one')
change_name(record, 'change two')
session.rollback()

gives:

id 3 - test
id 3 - change two

So maybe someone finds this helpful :)

user_14667130
Related