I don't know if this is part of your problem, but: 1. metadata.create_all(Session.connection()) will not mark the session as dirty 2. Session.flush() is a no-op if the session is clean - it won't even fire the "after_flush" event 3. ZopeTransactionExtension uses the after_flush event (or after_bulk_update/after_bulk_delete) to decide that the session needs to be committed. If none of these events have occurred, "transaction.commit()" won't actually ever call Session.commit()
So, if you're going to continue to use ZopeTransactionExtension, you probably need to add a call to mark_changed after creating your tables.Search for "mark_changed" on this page: https://pypi.python.org/pypi/zope.sqlalchemy Hope that helps, Simon On Sun, Nov 27, 2016 at 2:24 PM, Srikanth Bemineni <bemineni.srika...@gmail.com> 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 > > > > > DBSession = > scoped_session(sessionmaker(extension=ZopeTransactionExtension())) > > > > with session_shardid(DBSession,table_hash): > Base.metadata.create_all(DBSession.connection()) > DBSession.flush() > transaction.commit() > > > Was analyzing why it is getting rolled back. Started with a 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, in rollback > 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): > assert not self.__branch_from > import traceback > tb = traceback.format_exc() > > > if self._has_events or self.engine._has_events: > self.dispatch.rollback(self) > > > if self._still_open_and_connection_is_valid: > if self._echo: > self.engine.logger.info("ROLLBACK") > try: > self.engine.dialect.do_rollback(self.connection) > except BaseException as e: > self._handle_dbapi_exception(e, None, None, None, None) > finally: > if not self.__invalid and \ > self.connection._reset_agent is self.__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> >> > >> > 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> >> > >> > 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 >> > traceback >> > >> > url: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['session_info']=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["shard"]=shardid >> > yield >> > 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 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 >> > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> > To post to this group, send email to sqlal...@googlegroups.com >> > <mailto:sqlal...@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. -- 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.
