How to change autocommit in SQLAlchemy in the middle of the application

Question

I have a webapp that communicates with a postgres database via SQLAlchemy. The default value of autocommit=False is good for the most part of the application. With autocommit=False, the following code runs ok.

db = sqlalchemy(session_options={'autocommit': False})
...
def func1():
    db.session.add(image1)
    db.session.commit()

def func2():
    db.session.add(image2)
    db.session.commit()

def func3():
    db.session.add(image3)
    db.session.commit()

    db.session.add(image4)
    db.session.commit()

I now, want to use single commit for multiple inserts/update operations.
I can change my db session to autocommit=True, and call db.session.begin() before every operation, e.g.

db = SQLAlchemy(session_options={'autocommit': True})
...
def func1():
    db.session.begin()
    db.session.add(image1)
    db.session.commit()

def func2():
    db.session.begin()
    db.session.add(image2)
    db.session.commit()

def func3():
    db.session.begin()
    db.session.add(image3)

    db.session.add(image4)
    db.session.commit()

But if I change back to autocommit = True, I get an error, that complains about the db.session.begin()

...
  File "/usr/src/app/web/app/api/sites.py", line 559, in view_sites
    db.session.begin()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 949, in begin
    raise sa_exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: A transaction is already begun.  Use subtransactions=True to allow subtransactions.
...

According to here subtransactions will become obsolete in future versions of sqlalchemy ?

I'm looking for a way to programatically set autocommit=True in the beginning of func3, and turn it back to autocommit=True in the end of func3
Is this possible?

Thanks

question from:https://stackoverflow.com/questions/65910920/how-to-change-autocommit-in-sqlalchemy-in-the-middle-of-the-application

Answer 1

Waitting for answers