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 <javascript:>> 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())) > 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 <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.
