Thanks for the information! Indeed, the workaround solved my problem.
Is there a downside to unconditionally activating the workaround, or should
I check for use of pysqlite?
from sqlite3 import Connection as _sqlite3_Connection
from sqlalchemy import event as _event
from sqlalchemy.engine import Engine as _Engine
@_event.listens_for(_Engine, 'connect')
def do_connect(dbapi_connection, connection_record):
if isinstance(dbapi_connection, _sqlite3_Connection):
# disable pysqlite's emitting of the BEGIN statement entirely.
# also stops it from emitting COMMIT before any DDL.
logger.debug('setting connection isolation level to `None` to work
around pysqlite bug')
dbapi_connection.isolation_level = None
@_event.listens_for(_Engine, 'begin')
def do_begin(connection):
if isinstance(connection._Connection__connection.connection,
_sqlite3_Connection):
# emit our own BEGIN
logger.debug('emitting our own BEGIN to work around pysqlite bug')
connection.execute('BEGIN')
My only other question is, for future debugging, where should I log to get
*all* the SQL that is ultimately passed to the database?
Thanks,
Scott
On Monday, May 28, 2018 at 6:29:14 PM UTC-7, Mike Bayer wrote:
>
>
>
> On Mon, May 28, 2018, 8:00 PM Mike Bayer <mik...@zzzcomputing.com
> <javascript:>> wrote:
>
>> On Mon, May 28, 2018 at 7:23 PM, Scott Colby <scol...@gmail.com
>> <javascript:>> 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 sqlalchemy+...@googlegroups.com <javascript:>.
>> > To post to this group, send email to sqlal...@googlegroups.com
>> <javascript:>.
>> > 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.
