Using transactions with peewee without using `atomic()`

Question

We have a file db.py where a peewee database is defined:

db = PostgresqlExtDatabase('mom',
                           user=DB_CONFIG['username'],
                           password=DB_CONFIG['password'],
                           host=DB_CONFIG['host'],
                           port=DB_CONFIG['port'],
                           threadlocals=True,
                           register_hstore=False,
                           autocommit=True,
                           autorollback=True,
                           cursor_factory=DictCursor)

Calling db.execute("SOME RAW SQL UPDATE QUERY") works as expected. But calling a begin before that does not stop the DB from being modified.

db.begin()
db.execute("SOME RAW SQL UPDATE QUERY")  # <- Does not wait, db is updated immediately here
db.commit()

Am i doing this right?

I basically need to nest the raw sql in a transaction if one is already ongoing, else just execute it right away if there is no transaction begin called.

This works as expected if i do db.set_autocommit(False) then execute_sql then commit().
It also works inside the atomic() context manager.

---

To give some context, I am working on a web application, on logistics, and our codebase uses Flask and an SQLAlchemy scoped_session with autocommit set to True. It does not use the SQLAlchemy ORM (due to.. historical reasons) and instead just uses the Session object and its execute(), begin(), begin_nested(), rollback() and remove() methods.

The way it does it is by defining a Session Session = scoped_session(sessionmaker(autocommit=True)) in a file, and then calling session = Session() everywhere in the codebase, and executing the queries using session.execute("SQL") Sometimes, a session.begin() is called, so the query does not execute until the commit (or rollback).

We'd now really like to use peewee. But.. the codebase is built on this session. So this has to be spoofed. Going and changing every file is impossible, and not enough test cases to boot (for.. historical reasons).

---

Also I had some questions but I don't know where to ask them, so I hope you don't mind if I put them here:

• Is this db object (and its connection) bound to the thread it is executing in? Basically will there be some bug if db is imported from two different files, and db.begin() is called from each? I can see in the ipython shell that the id for the db object above is the same per thread, so am I correct in assuming that unless the psycopg2 connection is recreated, this should be isolated?

• To spoof the sqlalchemy Session, I have created a wrapper class that returns the kind of session required, the SQLA Session object, or the wrapper I've written for peewee to spoof that.

  class SessionMocker(object):
      # DO NOT make this a singleton. Sessions will break
  
      def __init__(self, orm_type=ORM_TYPES.SQLA):
          assert orm_type in ORM_TYPES, "Invalid session constructor type"
          super(SessionMocker, self).__init__()
          self.orm_type = orm_type
  
      def __call__(self, *args, **kwargs):
          if self.orm_type == ORM_TYPES.SQLA:
              return SQLASession(*args, **kwargs)
  
          if self.orm_type == ORM_TYPES.PEEWEE:
              # For now lets assume no slave
              return SessionWrapper(*args, **kwargs)
  
          raise NotImplementedError
  
      def __getattr__(self, item):
          """
          Assuming this will never be called without calling Session() first.
          Else there is no way to tell what type of Session class (ORM) is required, since that can't be passed.
          """
  
          if self.orm_type == ORM_TYPES.SQLA:
              kls = SQLASession
          elif self.orm_type == ORM_TYPES.PEEWEE:
              kls = SessionWrapper
          else:
              raise NotImplementedError
  
          return getattr(kls, item)
  
  Session = SessionMocker(ORM_TYPES.SQLA)
  

I figured this will allow the codebase to make a transparent and seamless switch over to using peewee without having to change it everywhere. How can i do this in a better way?

Answer 1

The docs explain how to do this: http://docs.peewee-orm.com/en/latest/peewee/transactions.html#autocommit-mode

But, tl;dr, you need to disable autocommit before begin/commit/rollback will work like you expect:

db.set_autocommit(False)
db.begin()
try:
    user.delete_instance(recursive=True)
except:
    db.rollback()
    raise
else:
    try:
        db.commit()
    except:
        db.rollback()
        raise
finally:
    db.set_autocommit(True)

Answer 2

The default value for autocommit is True but the default value of autorollback is False. Setting autorollback as True automatically rollback when an exception occurs while executing a query. Can't be sure but maybe this mess the situation. So, if you want try it with the autorollback as False