On Mon, May 28, 2018 at 7:23 PM, Scott Colby <scolb...@gmail.com> 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.
>
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
