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
with session_shardid(DBSession(),table_hash):
Base.metadata.create_all(DBSession().connection())
Base.metadata.create_all() --> works fine
2016-11-26 12:38:13,681 INFO [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-26 12:38:13,682 INFO [sqlalchemy.engine.base.Engine:1143][b
'uWSGIWorker4Core0'] {}
Inside _apply_shard_id
{'shard': 'ba6bd213'}
2016-11-26 12:38:13,695 INFO [sqlalchemy.engine.base.Engine:1140][b
'uWSGIWorker4Core0'] CREATE INDEX group_order_ba6bd213_index1 ON
group_order_ba6bd213
(group_id, user_id)
2016-11-26 12:38:13,695 INFO [sqlalchemy.engine.base.Engine:1143][b
'uWSGIWorker4Core0'] {}
2016-11-26 12:38:13,705 INFO [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
>
> 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
>
> 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-26 08:52:08,628 INFO [sqlalchemy.engine.base.Engine:1140][b
> 'uWSGIWorker3Core0'] select relname from pg_class c join pg_namespace n
> on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and
> relname=%(name)s
> 2016-11-26 08:52:08,628 INFO [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-26 08:52:08,628 INFO [sqlalchemy.engine.base.Engine:1140][b
> 'uWSGIWorker3Core0'] select relname from pg_class c join pg_namespace n
> on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and
> relname=%(name)s
> 2016-11-26 08:52:08,628 INFO [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" in session_info:
> statement = statement.replace("_shardid_", session_info["shard"])
> for param,value in parameters.items():
> if isinstance(value, str):
> parameters[param] = value.replace("_shardid_",session_info["shard"
> ])
>
>
> return statement, 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 in 2 seconds on interpreter 0x15dc190
>> pid: 10256 (default app)
>> Inside session_shardid
>> *<sqlalchemy.orm.scoping.scoped_session object at 0x7fe70702bdd8>*
>> 2016-11-25 21:34:50,808 INFO [sqlalchemy.engine.base.Engine:1140][b
>> 'uWSGIWorker2Core0'] select version()
>> 2016-11-25 21:34:50,808 INFO [sqlalchemy.engine.base.Engine:1143][b
>> 'uWSGIWorker2Core0'] {}
>> 2016-11-25 21:34:50,809 INFO [sqlalchemy.engine.base.Engine:1140][b
>> 'uWSGIWorker2Core0'] select current_schema()
>> 2016-11-25 21:34:50,810 INFO [sqlalchemy.engine.base.Engine:1143][b
>> 'uWSGIWorker2Core0'] {}
>> 2016-11-25 21:34:50,811 INFO [sqlalchemy.engine.base.Engine:1235][b
>> 'uWSGIWorker2Core0'] SELECT CAST('test plain returns' AS VARCHAR(60)) AS
>> anon_1
>> 2016-11-25 21:34:50,811 INFO [sqlalchemy.engine.base.Engine:1236][b
>> 'uWSGIWorker2Core0'] {}
>> 2016-11-25 21:34:50,811 INFO [sqlalchemy.engine.base.Engine:1235][b
>> 'uWSGIWorker2Core0'] SELECT CAST('test unicode returns' AS VARCHAR(60))
>> AS anon_1
>> 2016-11-25 21:34:50,812 INFO [sqlalchemy.engine.base.Engine:1236][b
>> 'uWSGIWorker2Core0'] {}
>> 2016-11-25 21:34:50,812 INFO [sqlalchemy.engine.base.Engine:1140][b
>> 'uWSGIWorker2Core0'] show standard_conforming_strings
>> 2016-11-25 21:34:50,812 INFO [sqlalchemy.engine.base.Engine:1143][b
>> 'uWSGIWorker2Core0'] {}
>> 2016-11-25 21:34:50,813 INFO [sqlalchemy.engine.base.Engine:679][b
>> 'uWSGIWorker2Core0'] BEGIN (implicit)
>> Inside __connection_for_session
>> <sqlalchemy.engine.base.Connection object at 0x7fe70093b940>
>> *<sqlalchemy.orm.session.Session object at 0x7fe701b9dc88>*
>> 2016-11-25 21:34:50,815 ERROR [pyramid_debugtoolbar:227][b
>> 'uWSGIWorker2Core0'] Exception at 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, in group_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, in do
>> return getattr(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, in connection
>> 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, in after_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, in join_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' object has no attribute
>> '_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
>>> def session_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
>>> del session.info["shard"]
>>>
>>>
>>>
>>>
>>> View.py
>>>
>>> with session_shardid(DBSession,table_hash):
>>> Base.metadata.create_all(DBSession.connection())
>>>
>>>
>>> WSGI app 0 (mountpoint='') ready in 2 seconds on interpreter 0x15dc190
>>> pid: 10256 (default app)
>>> Inside session_shardid
>>> <sqlalchemy.orm.scoping.scoped_session object at 0x7fe70702bdd8>
>>> 2016-11-25 21:34:50,808 INFO [sqlalchemy.engine.base.Engine:1140][b
>>> 'uWSGIWorker2Core0'] select version()
>>> 2016-11-25 21:34:50,808 INFO [sqlalchemy.engine.base.Engine:1143][b
>>> 'uWSGIWorker2Core0'] {}
>>> 2016-11-25 21:34:50,809 INFO [sqlalchemy.engine.base.Engine:1140][b
>>> 'uWSGIWorker2Core0'] select current_schema()
>>> 2016-11-25 21:34:50,810 INFO [sqlalchemy.engine.base.Engine:1143][b
>>> 'uWSGIWorker2Core0'] {}
>>> 2016-11-25 21:34:50,811 INFO [sqlalchemy.engine.base.Engine:1235][b
>>> 'uWSGIWorker2Core0'] SELECT CAST('test plain returns' AS VARCHAR(60))
>>> AS anon_1
>>> 2016-11-25 21:34:50,811 INFO [sqlalchemy.engine.base.Engine:1236][b
>>> 'uWSGIWorker2Core0'] {}
>>> 2016-11-25 21:34:50,811 INFO [sqlalchemy.engine.base.Engine:1235][b
>>> 'uWSGIWorker2Core0'] SELECT CAST('test unicode returns' AS VARCHAR(60))
>>> AS anon_1
>>> 2016-11-25 21:34:50,812 INFO [sqlalchemy.engine.base.Engine:1236][b
>>> 'uWSGIWorker2Core0'] {}
>>> 2016-11-25 21:34:50,812 INFO [sqlalchemy.engine.base.Engine:1140][b
>>> 'uWSGIWorker2Core0'] show standard_conforming_strings
>>> 2016-11-25 21:34:50,812 INFO [sqlalchemy.engine.base.Engine:1143][b
>>> 'uWSGIWorker2Core0'] {}
>>> 2016-11-25 21:34:50,813 INFO [sqlalchemy.engine.base.Engine:679][b
>>> 'uWSGIWorker2Core0'] BEGIN (implicit)
>>> Inside __connection_for_session
>>> <sqlalchemy.engine.base.Connection object at 0x7fe70093b940>
>>> <sqlalchemy.orm.session.Session object at 0x7fe701b9dc88>
>>> 2016-11-25 21:34:50,815 ERROR [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.
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.
