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 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/sqlal
[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
feedback request on support for sequences with --autogenerate
[23:13] I just started using Alembic/SA in a project that had a well established database. [23:13] I'm so glad there's support for relationships. Thanks! [23:13] But there was no support for sequences, when it came to --autogen. [23:14] So I took a stab at adding that and I got the happy path working. [23:14] I'd like someone to take a look at my patches and give me some feedback. [23:15] So what I have now are forks of SA and Alembic. [23:16] I'm not too familiar with Bitbucket. What's the next step? # Concerns So some of the issues I have are the following: * There's a lot of support in the codebase for schemas. I just want to make sure I got it right with regard to metadata. * I want to make sure that these sequences can be used independent of any column. * I want to make sure that column server_default works correctly. * I need to make sure downgrade() / drop sequence is working. Not to mention ALTER SEQUENCE. * I need to make sure dropping a schema will remove it as a default from any column that uses it. Surely I'm forgetting many things, so please fill me in. # Transactions in PG One thing I've noticed is that calls to sequences in PostgreSQL don't rollback in a transaction. I'm not sure if that causes an issue for alembic. # Testing I'm new to Python. I've run automated tests using python setup.py test. Is there any way to run a subset of tests or label tests with @WIP to just run those? And is it me, or are there a lot of failures? Is there anyway someone can create the tests, so I know I got this working correctly? Also, since sqlite doesn't support sequences, how do I get automated testing to use PG. A pull request is coming soon. Thanks, Brian -- Brian DeRocher Noblis | noblis.org | ☎ 703.610.1589 | ✉ brian.deroc...@noblis.org -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
