Issue: `execute_sql` seems not to respect `db.begin()`
Author: kunalbhagawati
State: closed
Created: 2017-01-28 10:40:18

For the lack of time, I have put this in StackOverflow also. I hope that is okay. If its redundant, i'll remove it from there.
http://stackoverflow.com/questions/41908762/using-transactions-with-peewee-without-using-atomic

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?

Begin is a no-op on Postgres and MySQL because transaction handling is baked into the Python driver. Don't ask me why this is the case, it just is part of the DB-API 2.0. As you have found out, the way to manually perform a transaction is to turn off autocommit, then execute your queries and call commit manually when done (or rollback as the case may be). The atomic decorator/context manager does this as well.

You should be able to just:

```
with db.atomic():
    db.execute_sql('UPDATE whatever')
```