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+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.