On Mon, May 28, 2018, 8:00 PM Mike Bayer <[email protected]> wrote:
> On Mon, May 28, 2018 at 7:23 PM, Scott Colby <[email protected]> wrote: > > Hello all, > > > > I am working on testing a project that uses SQLalchemy with the pytest > > testing framework. I have adapted the code from the docs to pytest as > shown. > > > > logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) > > > > cursor_handler = logging.FileHandler('cursor.log') > > cursor_log = logging.getLogger('cursor_log') > > cursor_log.addHandler(cursor_handler) > > cursor_log.setLevel(logging.DEBUG) > > cursor_log.propagate = false > > > > > > @pytest.fixture(scope='session') > > def created_database_path(tmpdir_factory): > > db_path = tmpdir_factory.mktemp('ocspdash').join('ocspdash.db') > > > > engine = create_engine(f'sqlite:///{db_path}') > > Base.metadata.create_all(engine) > > > > yield db_path > > > > @pytest.fixture(scope='session') > > def manager_session(created_database_path): > > engine = create_engine(f'sqlite:///{created_database_path}') > > > > @event.listens_for(engine, 'before_cursor_execute') > > def receive_before_cursor_execute(connection, cursor, statement, > > parameters, context, executemany): > > cursor_log.debug(statement) > > cursor_log.debug(parameters) > > > > connection = engine.connect() > > > > session_maker = sessionmaker(bind=connection) > > session = scoped_session(session_maker) > > > > @event.listens_for(session, 'after_transaction_end') > > def restart_savepoint(session, transaction): > > if transaction.nested and not transaction._parent.nested: > > # ensure that state is expired the way > > # session.commit() normally does > > session.expire_all() > > > > session.begin_nested() > > > > transaction = connection.begin() > > session.begin_nested() > > > > manager = Manager( > > engine=engine, > > session=session, > > ) > > > > yield manager, connection > > > > session.close() > > transaction.rollback() > > > > connection.close() > > > > > > Pytest fixtures are sort of like context managers: everything before the > > `yield` is the set up and after the `yield` is the tear down. > > > > I have separated the `create_all` part because SQLalchemy seems to issue > > unnecessary COMMITs in the process of creating the tables: > > > the create_all() needs to be called with the connection that you've > started the transaction within: > > trans = connection.begin() > metadata.create_all(connection) > > then no COMMITs will be emitted. > > > > > > > > These commits would prematurely end the transaction created by > > `connection.begin()`. > > is that when using a file-based SQLIte database or :memory: ? if a > file based SQLite database, it would be using a separate connection. > > > > > > But here is the real problem. Consider the output in `cursor.log` of one > > test function: > > The ROLLBACK is missing in the 'before_cursor_execute' events. Why is > this? > > When using the Python DBAPI, you don't emit the string "ROLLBACK", you > call connection.rollback() where the DBAPI does the actual > implementation, in the case of SQLite it would be the ROLLBACK string. > See https://www.python.org/dev/peps/pep-0249/#rollback > > > > > Adding an extra `engine.execute('ROLLBACK')` at the end raises an > > appropriate error about there being no transaction to end. > > that's correct because it was already rolled back. > > > > > However, if I open up the test database: > > > > $ sqlite3 test.db > > SQLite version 3.19.3 2017-06-27 16:48:08 > > Enter ".help" for usage hints. > > sqlite> select * from authority; > > 1|Test Authority|2345|2018-05-28 22:59:28 > > sqlite> > > > > It's still in there! > > you're using SAVEPOINT with the pysqlite driver which requires this > workaround: > > > http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl > > that section overall should provide some perspective about how the > driver gets involved, and pysqlite's is particualrly intervening in an > effort to reduce file lock contention. > That workaround also allows transactional ddl to work, the pysqlite driver is otherwise also emitting COMMIT for every ddl statement so perhaps that's what you were seeing since it looks like you were logging from the driver side. > > > > > So here are my questions: > > > > why does `engine.create_all` issue unnecessary COMMITs? > > you mean "metadata.create_all()". if you hand it the engine, then you > are using connectionless execution which only runs in "autocommit" > mode, e.g. commit for every DDL / DML statement. See > > http://docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit > as well as the section that follows. give it the connection that > you've begun the transaction upon. > > > why is the final ROLLBACK not showing up in the 'before_cursor_execute' > > event? > > SQLAlchemy calls the connection.rollback() and connection.commit() > pep-249 methods for transaction rollback/commit and no SQL is rendered > on the SQLAlchemy side. > > > what am I doing wrong that the transaction doesn't actually get rolled > back? > > probably the SAVEPOINT workaround for pysqlite. > > > > > (I know that `scoped_session` might not be the most appropriate thing to > use > > here, but that is how the real code works and I want to test in a similar > > environment; additionally, when I tried this whole exercise with a normal > > `sessionmaker`, all these problems happened in an identical manner.) > > if you're manipulating transactions, work with engines/connections and > transaction, and just have the Session hang on within the inside of > the transaction. The connection/transaction > begin()/commit()/rollback() API has a nesting behavior such that only > the outermost begin()/commit() actually demarcate the transaction; a > rollback() at any level rolls back the transaction immediately. > This is illustrated at > > http://docs.sqlalchemy.org/en/latest/core/connections.html#nesting-of-transaction-blocks > . > > > > > > Thanks, > > Scott Colby > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [email protected]. > > To post to this group, send email to [email protected]. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
