On Tue, Aug 28, 2018 at 5:22 PM, 'Brian DeRocher' via sqlalchemy
<sqlalchemy@googlegroups.com> wrote:
>
>
> 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):
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.
>
> 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()))
>
>
>>
>> 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.
>
> --
> 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.
