On Tue, Aug 28, 2018 at 11:32 AM, 'Brian DeRocher' via sqlalchemy <sqlalchemy@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+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.
