Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
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 -- 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.
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
On Tue, Aug 28, 2018 at 5:22 PM, 'Brian DeRocher' via sqlalchemy 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 >> 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 > 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
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
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 > > 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 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
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
On Tue, Aug 28, 2018 at 11:32 AM, 'Brian DeRocher' via sqlalchemy 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.
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
On Tue, Aug 28, 2018 at 5:53 PM Simon King wrote: > > On Tue, Aug 28, 2018 at 4:32 PM 'Brian DeRocher' via sqlalchemy > 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? 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. > > > > 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 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 -- 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.
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
On Tue, Aug 28, 2018 at 4:32 PM 'Brian DeRocher' via sqlalchemy 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? 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. > 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 -- 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] sqlalchemy session in same transaction as existing psycopg2 connection
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
--
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.
import os
import logging
from dotenv import load_dotenv
import psycopg2
from psycopg2.extras import LoggingConnection
from sqlalchemy import Column, create_engine, Integer, MetaData, Text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
dotenv_path = os.path.join(os.path.dirname(__file__), '.env')
load_dotenv(dotenv_path)
logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.DEBUG)
db_conn = None
def get_conn():
global db_conn
logging.debug('get_conn()')
if db_conn is None:
logging.debug(' theres no db_conn, must create one')
conn_string = "dbname='{0}' host='localhost' port=5432 user='{1}' password='{2}'".format(
os.getenv("DB_NAME"),
os.getenv("DB_USER"),
os.getenv("DB_PASSWORD"))
db_conn = psycopg2.connect(conn_string, connection_factory=LoggingConnection)
db_conn.initialize(logger)
else:
logging.debug(' using exising db_conn')
return db_conn
BaseCi = declarative_base(metadata=MetaData(schema='config'))
class User(BaseCi):
__tablename__ = 'user'
user_id = Column(Integer, primary_key=True)
user_status = Column(Text)
def legacy_psycopg_code(uid, status):
conn = get_conn()
cur = conn.cursor()
query = 'UPDATE config.user SET user_status = %s WHERE user_id = %s '
cur.execute(query, (status, uid,))
# Confirm it's there.
query = 'select user_status from config.user where user_id = %s'
cur.execute(query, (uid,))
uc = cur.fetchone()[0]
logging.debug("user_status is %s" % uc)
def test_transaction():
uid = 78
status = 'ex-parrot'
legacy_psycopg_code(uid, status)
# Use existing connection so we can see inside the transaction.
engine = create_engine('postgresql+psycopg2://', creator=get_conn)
Session = sessionmaker(bind=engine)
session = Session()
user = session.query(User).get(uid)
logging.debug(user.user_status)
# assertEquals(user.user_status, status)
if __name__ == "__main__":
test_transaction()
DEBUG:root:get_conn()
DEBUG:root: theres no db_conn, must create one
DEBUG:__main__:UPDATE config.user SET user_status = 'ex-parrot' WHERE user_id =
78
DEBUG:__main__:select user_status from config.user where user_id = 78
DEBUG:root:user_status is ex-parrot
DEBUG:root:get_conn()
DEBUG:root: using exising db_conn
DEBUG:__main__:select version()
DEBUG:__main__:select current_schema()
DEBUG:__main__:show transaction isolation level
DEBUG:__main__:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
DEBUG:__main__:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
DEBUG:__main__:SELECT 'x' AS some_label
DEBUG:root:DefaultDialect::initialize() calls do_rollback() here.
<-
DEBUG:__main__:show standard_conforming_strings
DEBUG:__main__:SELECT config."user".user_id AS config_user_user_id,
config."user".user_status AS config_user_user_status
FROM config."user"
WHERE config."user".user_id = 78
DEBUG:root:Active
