Hi Mike, I am using pyramid_tm as transaction manager which is part of the pyramid framework. Is this the third party transaction manager that you are mentioning about ?. Can you please tell us how pyramid_tm, ZopeTransacationExtension and sqlachemy come together. Why are they so many layers for a simple transactions.?. When they are so many libraries involved, may be I need to post this question in multiple forums to get all the answers?. If there are any pyramid experts please let me know what is happening here ?
http://docs.pylonsproject.org/projects/pyramid_tm/en/latest/ with session_shardid(DBSession,table_hash): Base.metadata.create_all(DBSession.connection()) self.request.tm.commit() -> This also doesn't help all the statements just roll back. Srikanth Bemineni On Monday, November 28, 2016 at 8:28:22 AM UTC-6, Mike Bayer wrote: > > > > On 11/27/2016 09:24 AM, Srikanth Bemineni wrote: > > Hi Mike, > > > > I even tried flushing and committing the transaction. But still the same > > issue. I was trying to figure out why is getting rolled back. Please see > > the below stack trace > > > that rollback is normal when the connection is returned to the > connection pool. you are still not getting your Session committed. > Because you are using ZopeTransactionExtension it probably is managing > the transaction on the Connection externally to the Session. You need > to use your chosen third party transaction commit facilities here to > ensure all transactions are committed (or don't use that tool if it is > making things more difficult). > > Alternatively, for the CREATE you can metadata.create_all() on the > connection: > > > with engine.connect() as conn: > conn.info['whatever token you need'] = 'the token' > metadata.create_all(conn) > > > > > > > > > > > | > > > > > > > DBSession=scoped_session(sessionmaker(extension=ZopeTransactionExtension())) > > > > > > > > > withsession_shardid(DBSession,table_hash): > > Base.metadata.create_all(DBSession.connection()) > > DBSession.flush() > > transaction.commit() > > > > > > Wasanalyzing why it isgetting rolled back.Startedwitha trace. > > > > > > Traceback(most recent call last): > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/pool.py",line > > > > 687,in_finalize_fairy > > fairy._reset(pool) > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/pool.py",line > > > > 827,in_reset > > self._reset_agent.rollback() > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/engine/base.py",line > > > > 1614,inrollback > > self._do_rollback() > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/engine/base.py",line > > > > 1652,in_do_rollback > > self.connection._rollback_impl() > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/engine/base.py",line > > > > 694,in_rollback_impl > > tb =traceback.format_exc() > > > > > > > > > > def_rollback_impl(self): > > assertnotself.__branch_from > > importtraceback > > tb =traceback.format_exc() > > > > > > ifself._has_events orself.engine._has_events: > > self.dispatch.rollback(self) > > > > > > *ifself._still_open_and_connection_is_valid: > > ifself._echo: > > self.engine.logger.info("ROLLBACK") > > try:* > > self.engine.dialect.do_rollback(self.connection) > > exceptBaseExceptionase: > > self._handle_dbapi_exception(e,None,None,None,None) > > finally: > > ifnotself.__invalid and\ > > self.connection._reset_agent > isself.__transaction: > > self.connection._reset_agent =None > > self.__transaction =None > > else: > > self.__transaction =None > > > > | > > > > > > > > > > On Sunday, November 27, 2016 at 7:53:23 AM UTC-6, Mike Bayer wrote: > > > > you need to call commit() on a Session in order for the transaction > to > > be committed. > > > > Also you should be able to attach session events to the > scoped_session > > object directly, the session event structure will extract the > > underlying > > sessionmaker class as the target. > > > > > > > > On 11/26/2016 01:56 PM, Srikanth Bemineni wrote: > > > Hi, > > > > > > May be I celebrated little bit too early. It looks like for no > > obvious > > > reason the scoped_session rolls back the transaction right at the > > last > > > moment. Any idea on how to debug this ? > > > > > > | > > > View.py > > > > > > withsession_shardid(DBSession(),table_hash): > > > Base.metadata.create_all(DBSession().connection()) > > > > > > Base.metadata.create_all()-->works fine > > > > > > 2016-11-2612:38:13,681INFO > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker4Core0'] > > > CREATE TABLE group_order_ba6bd213 ( > > > gtid SERIAL NOT NULL, > > > group_id INTEGER NOT NULL, > > > title TEXT NOT NULL, > > > description TEXT, > > > created_at TIMESTAMP WITHOUT TIME ZONE, > > > updated_at TIMESTAMP WITHOUT TIME ZONE, > > > PRIMARY KEY (gtid), > > > FOREIGN KEY(group_id)REFERENCES "group"(gid)ON DELETE CASCADE, > > > FOREIGN KEY(user_id)REFERENCES youzer (uid)ON DELETE CASCADE > > > ) > > > > > > > > > > > > > > > 2016-11-2612:38:13,682INFO > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker4Core0']{} > > > Inside_apply_shard_id > > > {'shard':'ba6bd213'} > > > 2016-11-2612:38:13,695INFO > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker4Core0']CREATE > > INDEX > > > group_order_ba6bd213_index1 ON group_order_ba6bd213 > > (group_id,user_id) > > > 2016-11-2612:38:13,695INFO > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker4Core0']{} > > > 2016-11-2612:38:13,705INFO > > > [sqlalchemy.engine.base.Engine:699][b'uWSGIWorker4Core0']ROLLBACK > > > | > > > > > > > > > > > > > > > On Saturday, November 26, 2016 at 9:10:23 AM UTC-6, Srikanth > > Bemineni wrote: > > > > > > Hi, > > > > > > Hope this helps others > > > > > > 1. DBSession is scoped_session. Use DBSession() to get the > actual > > > session > > > https://pypi.python.org/pypi/zope.sqlalchemy > > <https://pypi.python.org/pypi/zope.sqlalchemy> > > > <https://pypi.python.org/pypi/zope.sqlalchemy > > <https://pypi.python.org/pypi/zope.sqlalchemy>> > > > > > > with session_shardid(DBSession(),table_hash): > > > Base.metadata.create_all(DBSession().connection()) > > > > > > 2. AttributeError: 'SessionTransaction' object has no > > attribute '_iterate_parents' > > > This is a bug in zope.sqlalchemy. I was using 7.6. This is > > fixed > > > in 7.7 > > > > > https://github.com/zopefoundation/zope.sqlalchemy/issues/15 > > <https://github.com/zopefoundation/zope.sqlalchemy/issues/15> > > > <https://github.com/zopefoundation/zope.sqlalchemy/issues/15 > > <https://github.com/zopefoundation/zope.sqlalchemy/issues/15>> > > > > > > Hi Mike, > > > > > > There is small issue in the recipe. May be we need to replace > the > > > parameter also, if we it contains table names > > > > > > | > > > 2016-11-2608:52:08,628INFO > > > > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker3Core0']selectrelname > > > > > frompg_class c join pg_namespace n on n.oid=c.relnamespace > > > wherepg_catalog.pg_table_is_visible(c.oid)andrelname=%(name)s > > > 2016-11-2608:52:08,628INFO > > > > > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker3Core0']{'name':'group_order_shardid_'} > > > > > > > > > > > > > I need to replace the parameters so that it checks the proper > > table > > > name before creating it > > > > > > > > > 2016-11-2608:52:08,628INFO > > > > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker3Core0']selectrelname > > > > > frompg_class c join pg_namespace n on n.oid=c.relnamespace > > > wherepg_catalog.pg_table_is_visible(c.oid)andrelname=%(name)s > > > 2016-11-2608:52:08,628INFO > > > > > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker3Core0']{'name':'group_order_ba6bd213'} > > > > > > > > > > > > > > > > > > > def_apply_shard_id(connection,cursor,statement,parameters, > > > context,executemany): > > > """Apply a "shard id" to statements. > > > > > > > > > Similar to the comment listener, we alter the statement on > > the > > > fly replacing occurrences of "_shard_" with the current > > "shard id". > > > > > > > > > """ > > > session_info =connection.info.get('session_info',{}) > > > print(session_info) > > > if"shard"insession_info: > > > statement > > =statement.replace("_shardid_",session_info["shard"]) > > > forparam,value inparameters.items(): > > > ifisinstance(value,str): > > > > > > > > parameters[param]=value.replace("_shardid_",session_info["shard"]) > > > > > > > > > returnstatement,parameters > > > > > > | > > > > > > > > > > > > On Friday, November 25, 2016 at 10:14:31 PM UTC-6, Srikanth > > Bemineni > > > wrote: > > > > > > Hi, > > > > > > Sorry some of the debug trace was missing from the > > previous post > > > > > > | > > > > > > > > > WSGI app 0(mountpoint='')ready in2seconds on interpreter > > > 0x15dc190pid:10256(defaultapp) > > > Insidesession_shardid > > > *<sqlalchemy.orm.scoping.scoped_session objectat > > 0x7fe70702bdd8>* > > > 2016-11-2521:34:50,808INFO > > > > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker2Core0']selectversion() > > > > > 2016-11-2521:34:50,808INFO > > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,809INFO > > > > > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker2Core0']selectcurrent_schema() > > > > > > > 2016-11-2521:34:50,810INFO > > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,811INFO > > > > > [sqlalchemy.engine.base.Engine:1235][b'uWSGIWorker2Core0']SELECT > > CAST('test > > > plain returns'AS VARCHAR(60))AS anon_1 > > > 2016-11-2521:34:50,811INFO > > > > [sqlalchemy.engine.base.Engine:1236][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,811INFO > > > > > [sqlalchemy.engine.base.Engine:1235][b'uWSGIWorker2Core0']SELECT > > CAST('test > > > unicode returns'AS VARCHAR(60))AS anon_1 > > > 2016-11-2521:34:50,812INFO > > > > [sqlalchemy.engine.base.Engine:1236][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,812INFO > > > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker2Core0']show > > > standard_conforming_strings > > > 2016-11-2521:34:50,812INFO > > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,813INFO > > > > > > [sqlalchemy.engine.base.Engine:679][b'uWSGIWorker2Core0']BEGIN(implicit) > > > > > Inside__connection_for_session > > > <sqlalchemy.engine.base.Connectionobjectat 0x7fe70093b940> > > > *<sqlalchemy.orm.session.Sessionobjectat 0x7fe701b9dc88>* > > > 2016-11-2521:34:50,815ERROR > > > > [pyramid_debugtoolbar:227][b'uWSGIWorker2Core0']Exceptionat > > > http://localhost:9090/group_debug > > <http://localhost:9090/group_debug> > > > traceback > > > > > url: > http://localhost:9090/_debug_toolbar/exception?token=62272d5c7863305c7830665c78393224765c7838335c786662265c78666427&tb=140630123854312 > > > < > http://localhost:9090/_debug_toolbar/exception?token=62272d5c7863305c7830665c78393224765c7838335c786662265c78666427&tb=140630123854312> > > > > > > > Traceback(most recent call last): > > > .... > > > File"./medicscity/views/group.py",line 144,ingroup_debug > > > Base.metadata.create_all(DBSession.connection()) > > > > > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/orm/scoping.py",line > > > > > > > 157,indo > > > returngetattr(self.registry(),name)(*args,**kwargs) > > > > > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/orm/session.py",line > > > > > > > 966,inconnection > > > execution_options=execution_options) > > > > > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/orm/session.py",line > > > > > > > 971,in_connection_for_bind > > > engine,execution_options) > > > > > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/orm/session.py",line > > > > > > > 417,in_connection_for_bind > > > > self.session.dispatch.after_begin(self.session,self,conn) > > > > > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/sqlalchemy/event/attr.py",line > > > > > > > 256,in__call__ > > > fn(*args,**kw) > > > > > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/zope.sqlalchemy-0.7.6-py3.4.egg/zope/sqlalchemy/datamanager.py",line > > > > > > > 231,inafter_begin > > > > > > > > > join_transaction(session,self.initial_state,self.transaction_manager,self.keep_session) > > > > > > > > > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/zope.sqlalchemy-0.7.6-py3.4.egg/zope/sqlalchemy/datamanager.py",line > > > > > > > 205,injoin_transaction > > > > > > > > > DataManager(session,initial_state,transaction_manager,keep_session=keep_session) > > > > > > > > > > > > > File"/home/izero/devel/medics_city/medics_city_env/lib/python3.4/site-packages/zope.sqlalchemy-0.7.6-py3.4.egg/zope/sqlalchemy/datamanager.py",line > > > > > > > 65,in__init__ > > > self.tx =session.transaction._iterate_parents()[-1] > > > AttributeError:'SessionTransaction'objecthas noattribute > > > '_iterate_parents' > > > | > > > > > > > > > On Friday, November 25, 2016 at 10:12:18 PM UTC-6, > Srikanth > > > Bemineni wrote: > > > > > > Hi, > > > > > > I was not using the DBSession.connection() while > creating > > > the tables when invoking the create_all function on > the > > > metadata. After fixing that issue I end with an > another > > > issue. It looks like the session(scoped_session), > where I > > > set the shard id, and the session that I receive, when > > the > > > shard id is moved to the connection(after_begin event > > > received by _connection_for_session) are different, > > even if > > > I am using the same scoped_session. How can I get the > > > session that is inside the scoped_session to set the > > info ? > > > > > > | > > > app __init__.py > > > > > > engine =engine_from_config(settings,'sqlalchemy.') > > > > > > DBSession=scoped_session(sessionmaker(extension=ZopeTransactionExtension())) > > > > > > DBSession.configure(bind=engine) > > > > > > > > > > > > > > > connectionsetup.py > > > > > > @listens_for(DBSession,"after_begin") > > > def_connection_for_session(session,trans,connection): > > > """Share the 'info' dictionary of Session with > > Connection > > > objects. > > > This occurs as new Connection objects are > associated > > > with the > > > Session. The .info dictionary on Connection is > > local > > > to the > > > DBAPI connection. > > > """ > > > print("Inside __connection_for_session") > > > print(connection) > > > print(session) > > > connection.info > > <http://connection.info>['session_info']=session.info > > <http://session.info> > > > > > > > > > @contextmanager > > > defsession_shardid(session,shardid): > > > """Apply the "shard" id to all SQL emitted by the > > given > > > Session. > > > """ > > > print("Inside session_shardid") > > > print(session) > > > session.info <http://session.info>["shard"]=shardid > > > > yield > > > delsession.info <http://delsession.info>["shard"] > > > > > > > > > > > > > > > View.py > > > > > > withsession_shardid(DBSession,table_hash): > > > Base.metadata.create_all(DBSession.connection()) > > > > > > > > > WSGI app 0(mountpoint='')ready in2seconds on > interpreter > > > 0x15dc190pid:10256(defaultapp) > > > Insidesession_shardid > > > <sqlalchemy.orm.scoping.scoped_session objectat > > 0x7fe70702bdd8> > > > 2016-11-2521:34:50,808INFO > > > > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker2Core0']selectversion() > > > > > 2016-11-2521:34:50,808INFO > > > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,809INFO > > > > > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker2Core0']selectcurrent_schema() > > > > > > > 2016-11-2521:34:50,810INFO > > > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,811INFO > > > > > [sqlalchemy.engine.base.Engine:1235][b'uWSGIWorker2Core0']SELECT > > > CAST('test plain returns'AS VARCHAR(60))AS anon_1 > > > 2016-11-2521:34:50,811INFO > > > > > [sqlalchemy.engine.base.Engine:1236][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,811INFO > > > > > [sqlalchemy.engine.base.Engine:1235][b'uWSGIWorker2Core0']SELECT > > > CAST('test unicode returns'AS VARCHAR(60))AS anon_1 > > > 2016-11-2521:34:50,812INFO > > > > > [sqlalchemy.engine.base.Engine:1236][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,812INFO > > > > > [sqlalchemy.engine.base.Engine:1140][b'uWSGIWorker2Core0']show > > > standard_conforming_strings > > > 2016-11-2521:34:50,812INFO > > > > > [sqlalchemy.engine.base.Engine:1143][b'uWSGIWorker2Core0']{} > > > 2016-11-2521:34:50,813INFO > > > > > > [sqlalchemy.engine.base.Engine:679][b'uWSGIWorker2Core0']BEGIN(implicit) > > > > > Inside__connection_for_session > > > <sqlalchemy.engine.base.Connectionobjectat > > 0x7fe70093b940> > > > <sqlalchemy.orm.session.Sessionobjectat > 0x7fe701b9dc88> > > > 2016-11-2521:34:50,815ERROR > > > [pyramid_debugtoolbar:227][b<span style="color: #080;" > > > class="styled-by > > > | > > > > > > -- > > > 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 > > <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:> > > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:> > <javascript:>>. > > > To post to this group, send email to sqlal...@googlegroups.com > > <javascript:> > > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > > Visit this group at https://groups.google.com/group/sqlalchemy > > <https://groups.google.com/group/sqlalchemy>. > > > For more options, visit https://groups.google.com/d/optout > > <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+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto: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.
