sqlalchemy session in same transaction as existing psycopg2 connection

[Brian DeRocher]

Hey all,

I'm writing some automated tests for some legacy python code using a psycopg2 connection.  I'd like to check data in the database by using SQLAlchemy.  I rollback the database transaction after each test in tearDown().

The problem is my SQLAlchemy connection doesn't see the database updates.  At first I thought they weren't using the same connection, but they are.  I'm using create_engine(..., creator=get_conn).

The problem appears to be that DefaultDialect is rolling back the transaction.  See sqlalchemy/engine/default.py line 167.  I had to review PG logs to spot this as it's not logged.

self.do_rollback(connection.connection)

Is this line really needed?  What would it be rolling back?  Can it be avoided?  When I disable this line of code, the transaction continues and sqlalchemy can see the updates from psyopg2.

I've attached a demo file.

Thanks,

Brian

[Simon King]

I haven't tried to run your code, but the usual way to connect a
session to an existing connection is to bind the session directly to
the connection:

http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites

Would that work for your situation?

Hope that helps,

Simon

[Simon King]

Sorry, I realised just after I pressed Send that you have a
DBAPI-level connection rather than an SQLAlchemy connection, so my
suggestion doesn't really help...

For what it's worth, the "do_rollback" was apparently added here:

https://bitbucket.org/zzzeek/sqlalchemy/commits/39fd3442e306f9c2981c347ab2487921f3948a61#chg-lib/sqlalchemy/engine/default.py

Simon

[Mike Bayer]

On Tue, Aug 28, 2018 at 11:32 AM, 'Brian DeRocher' via sqlalchemy
<sqlal...@googlegroups.com> wrote:
> Hey all,
>
> I'm writing some automated tests for some legacy python code using a
> psycopg2 connection. I'd like to check data in the database by using
> SQLAlchemy. I rollback the database transaction after each test in
> tearDown().
>
> The problem is my SQLAlchemy connection doesn't see the database updates.
> At first I thought they weren't using the same connection, but they are.
> I'm using create_engine(..., creator=get_conn).
>
> The problem appears to be that DefaultDialect is rolling back the
> transaction. See sqlalchemy/engine/default.py line 167. I had to review PG
> logs to spot this as it's not logged.
>
> self.do_rollback(connection.connection)
>
> Is this line really needed?

yup (in general)

> What would it be rolling back?

all of the snapshots and/or locks that are accumulated by the
Postgresql database as commands on the connection proceed. This
means literally versions of the database that don't exist outside of
the transaction, as well as simple things like table/ row locks. See
https://www.postgresql.org/docs/9.1/static/mvcc.html . As far as why
there is a transaction in the first place, the pep-249 DBAPI drivers
like psycopg2 are required for a connection to be in a transaction by
default, and "autocommit" systems are not standard per spec.

> Can it be
> avoided?

the rollback you are seeing is likely the connection-pool level
"reset", which is not strictly necessary if the code that makes use of
the pool is absolutely disciplined about ensuring transactions are
cleaned up before returning a connection to the pool, or as in your
case you are linking it to some larger state and don't actually want
the connection pool to be managing connection lifecycle. You can set
this with pool_reset_on_return=None, see
http://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=reset_on_return#sqlalchemy.create_engine.params.pool_reset_on_return,
however that docstring seems a little typo-ish and the link is broken
so the actual list of values you can see at
http://docs.sqlalchemy.org/en/latest/core/pooling.html?highlight=reset_on_return#sqlalchemy.pool.Pool.params.reset_on_return

let me fix the formatting on that docstring

When I disable this line of code, the transaction continues and
> sqlalchemy can see the updates from psyopg2.
>
> I've attached a demo file.
>
> Thanks,
> Brian
>

> --
> 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.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Brian DeRocher]

On Tuesday, August 28, 2018 at 2:51:47 PM UTC-4, Mike Bayer wrote:

On Tue, Aug 28, 2018 at 11:32 AM, 'Brian DeRocher' via sqlalchemy
<sqlal...@googlegroups.com> wrote:
> Hey all,
>
> I'm writing some automated tests for some legacy python code using a
> psycopg2 connection.  I'd like to check data in the database by using
> SQLAlchemy.  I rollback the database transaction after each test in
> tearDown().
>
> The problem is my SQLAlchemy connection doesn't see the database updates.
> At first I thought they weren't using the same connection, but they are.
> I'm using create_engine(..., creator=get_conn).
>
> The problem appears to be that DefaultDialect is rolling back the
> transaction.  See sqlalchemy/engine/default.py line 167.  I had to review PG
> logs to spot this as it's not logged.
>
> self.do_rollback(connection.connection)
>
> Is this line really needed?

yup (in general)

I ask because it doesn't seem natural to me that the job so the DefaultDialect is to manage a transaction. 

> What would it be rolling back?

all of the snapshots and/or locks that are accumulated by the
Postgresql database as commands on the connection proceed.   This
means literally versions of the database that don't exist outside of
the transaction, as well as simple things like table/ row locks.   See
https://www.postgresql.org/docs/9.1/static/mvcc.html .   As far as why
there is a transaction in the first place, the pep-249 DBAPI drivers
like psycopg2 are required for a connection to be in a transaction by
default, and "autocommit" systems are not standard per spec.

> Can it be
> avoided?

the rollback you are seeing is likely the connection-pool level
"reset", which is not strictly necessary if the code that makes use of
the pool is absolutely disciplined about ensuring transactions are
cleaned up before returning a connection to the pool, or as in your
case you are linking it to some larger state and don't actually want
the connection pool to be managing connection lifecycle.  You can set
this with pool_reset_on_return=None, see
http://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=reset_on_return#sqlalchemy.create_engine.params.pool_reset_on_return,
however that docstring seems a little typo-ish and the link is broken
so the actual list of values you can see at
http://docs.sqlalchemy.org/en/latest/core/pooling.html?highlight=reset_on_return#sqlalchemy.pool.Pool.params.reset_on_return

let me fix the formatting on that docstring

Unfortunately setting pool_reset_on_return=None did not help.  There's still a path of code down to this do_rollback().   I insert a traceback.format_stack() near the rollback, and this is the stack at that point in time (with pool_reset_on_return=None):

DEBUG:root:  File "iso.py", line 72, in <module>

    test_transaction()

  File "iso.py", line 66, in test_transaction

    user = session.query(User).get(uid)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 864, in get

    return self._get_impl(ident, loading.load_on_ident)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 897, in _get_impl

    return fallback_fn(self, key)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 223, in load_on_ident

    return q.one()

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2814, in one

    ret = self.one_or_none()

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2784, in one_or_none

    ret = list(self)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2855, in __iter__

    return self._execute_and_instances(context)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2876, in _execute_and_instances

    close_with_result=True)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2885, in _get_bind_args

    **kw

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2867, in _connection_from_session

    conn = self.session.connection(**kw)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1013, in connection

    execution_options=execution_options)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1018, in _connection_for_bind

    engine, execution_options)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 403, in _connection_for_bind

    conn = bind.contextual_connect()

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2112, in contextual_connect

    self._wrap_pool_connect(self.pool.connect, None),

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2147, in _wrap_pool_connect

    return fn()

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 387, in connect

    return _ConnectionFairy._checkout(self)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 766, in _checkout

    fairy = _ConnectionRecord.checkout(pool)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 516, in checkout

    rec = pool._do_get()

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1135, in _do_get

    return self._create_connection()

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 333, in _create_connection

    return _ConnectionRecord(self)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 461, in __init__

    self.__connect(first_connect_check=True)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 661, in __connect

    exec_once(self.connection, self)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 246, in exec_once

    self(*args, **kw)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 256, in __call__

    fn(*args, **kw)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 1334, in go

    return once_fn(*arg, **kw)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 181, in first_connect

    dialect.initialize(c)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 537, in initialize

    super(PGDialect_psycopg2, self).initialize(connection)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2127, in initialize

    super(PGDialect, self).initialize(connection)

  File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 272, in initialize

    logging.debug(''.join(traceback.format_stack()))

[Mike Bayer]

On Tue, Aug 28, 2018 at 5:22 PM, 'Brian DeRocher' via sqlalchemy

looks like you missed the last line of the traceback, there is an
"initialize" step that must be performed so that the dialect knows
what kind of database it's dealing with. you can skip this step like
this:

engine = create_engine(
'postgresql+psycopg2://', creator=get_conn,
pool_reset_on_return=None, _initialize=False)


use that until you can fix your program to get its connections from
the engine in the first place. All you need to do for that is where
you have this:

if db_conn is None:
db_conn = psycopg2.connect(user="scott", password="tiger",
dbname="test")

change it to this:

def get_conn():
global db_conn
global engine
if db_conn is None:
engine = create_engine(
"postgresql+psycopg2://scott:tiger@localhost/test",
poolclass=StaticPool)
db_conn = engine.raw_connection()

return db_conn

StaticPool is a "pool" that holds onto exactly one connection like a
singleton. db_conn will be your psycopg2 connection inside of a
transparent wrapper. the initialize will be done up front before you
need to do anything. then use that same "engine" down below.

[Brian DeRocher]

Beautiful.  Skipping the psycopg2 initialization prevents that rollback and allows SQLAlchemy to use the same transaction.

FWIW, I don't think pool_reset_on_return=None is needed, at least for my purposes.

Thanks for the help and thanks for the advice about raw_connection().  I'll get that into place, at least for the testing suite.

Brian