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 <http://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
<http://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>>
> > <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>>
> >
<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>
> <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>
>
<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>
> <http://connection.info>['session_info']=session.info
<http://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>
<http://session.info>["shard"]=shardid
> > yield
> > delsession.info <http://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>
> <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>
> <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>
> <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
<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
<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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
