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
'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 7:50:25 PM UTC-6, Srikanth Bemineni wrote:
>
> Hi,
>
> Just trying to integrate the recipe into my pyramid application.
> Sqlalchemy 1.1.4
>
> How do we put an event on a sessionmaker() managed by scope_session in a
> pyramid application. This doesn't seem to work. Is the connection already
> established ?
>
>
> engine = engine_from_config(settings, 'sqlalchemy.')
> DBSession = scoped_session(sessionmaker(extension=
> ZopeTransactionExtension()))
>
> @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")
> connection.info['session_info'] = session.info
>
>
>
>
> DBSession.configure(bind=engine)
>
>
> Srikanth Bemineni
>
>
>
> On Friday, November 25, 2016 at 3:15:27 PM UTC-6, Mike Bayer wrote:
>>
>>
>>
>> On 11/25/2016 03:55 PM, Srikanth Bemineni wrote:
>> >
>> > Hi Mike,
>> >
>> > The more "high scale" way here if you're really doing
>> > hundreds/thousands
>> > of different sets of tables is to modify the SQL statements on
>> > the way
>> > out. That example is here:
>> >
>> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SessionModifiedSQL
>> > . This does exactly the use case you describe, which is that
>> > per-request, a subset of tables need to have specific
>> > names.Enter code here...
>> >
>> >
>> >
>> >
>> >
>> > This is exactly what I was looking for. Are there any pitfalls using
>> > this method, like during joins where sqlalchemy making wrong
>> > interpretation of the data returned by database and its not able to map
>> > to the specified class.
>>
>> that is a great point, and in version 0.9 or prior it does have that
>> problem, and in fact I hadn't even noticed that until you brought it up.
>> however in version 1.0 and 1.1 the column targeting is done
>> positionally, not by name, so the renaming of the columns doesn't get in
>> the way (I just confirmed it causes problems w/ 0.9 but not 1.0/1.1).
>> By all means try it out and if it does have a column targeting problem,
>> let me know.
>>
>> >
>> > Are there future plans to get this recipe into the the library.?
>>
>> just the schema name version of the feature is in for now. if the
>> recipe works well for you, feel free to report back and a more inline
>> feature can be considered.
>>
>>
>>
>> >
>> > Srikanth Bemineni
>> >
>> >
>> > On Friday, November 25, 2016 at 2:04:29 PM UTC-6, Mike Bayer wrote:
>> >
>> >
>> >
>> > On 11/24/2016 11:04 PM, Srikanth Bemineni wrote:
>> > > Hi,
>> > >
>> > > I am using sqlalchemy in my pyramid application. I have situation
>> > here,
>> > > where I need to map a table on the fly in the application during
>> run
>> > > time. I also dont want to re-map all tables, there are some table
>> > which
>> > > are defined using a declarative base. I need those mapping to
>> > remain as
>> > > is. How can we do this?. Does clear_mappers() remove even
>> declarative
>> > > base mapping ?
>> >
>> >
>> > clear_mappers() is not for application use, only for test suites,
>> since
>> > it wipes all mappers.
>> >
>> > You can make ad-hoc mappings that will be garbage collected, *if*
>> you
>> > don't point other non-GC'ed mappings to them using relationships or
>> > backrefs, by making a subclass, mapping that, then dropping the
>> > subclass. An example of this pattern is at:
>> >
>> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName
>> > <
>> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName>
>> > .
>> > However, I don't recommend this pattern, if you are actually making
>> > thousands of different mappings and dropping them - it won't scale.
>> > mappings are expensive to create and garbage collect, and it is
>> easy to
>> > accidentally build a backref that will cause it to not be garbage
>> > collected.
>> >
>> > The more "high scale" way here if you're really doing
>> > hundreds/thousands
>> > of different sets of tables is to modify the SQL statements on the
>> way
>> > out. That example is here:
>> >
>> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SessionModifiedSQL
>> > <
>> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SessionModifiedSQL>
>>
>>
>> >
>> > . This does exactly the use case you describe, which is that
>> > per-request, a subset of tables need to have specific names.
>> >
>> > SQLAlchemy now includes a feature which does what this recipe does,
>> but
>> > only for the "schema" name of the Table:
>> >
>> https://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=schema%20execution%20option#schema-translating
>>
>> > <
>> https://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=schema%20execution%20option#schema-translating>
>>
>>
>> >
>> > . For more generalized modification of table names on the fly, the
>> > wiki
>> > recipe is more useful.
>> >
>> >
>> >
>> > >
>> > >
>> > > DBSession =
>> > >
>> scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
>> > > Base = declarative_base()
>> > >
>> > > class User(Base):
>> > > __tablename__ ="youzer"
>> > > uid = Column(Integer,autoincrement=True, primary_key=True)
>> > > uname = Column(Text,nullable=False,index=True)
>> > >
>> > > Just one of the tables as example. There are many tables that I
>> > need to
>> > > map for different request
>> > >
>> > > class GroupOrder()
>> > > gtid = Column(Integer, autoincrement=True,primary_key=True)
>> > > group_id = Column(Integer,
>> > > ForeignKey("group.gid",ondelete="CASCADE"),nullable=False)
>> > > ...
>> > > ...
>> > >
>> > > These groups can grow huge , so decision was made to shard these
>> > group
>> > > based on a hash. These are similar structured tables , with
>> > different names.
>> > >
>> > > Tables
>> > > group_order_<shard1>
>> > > group_order_<shard1>
>> > > group_order_<shard1>
>> > >
>> > > table_object = Table(<shard table name with similar structure>,
>> > > Base.metadata,
>> > > Column('gtid', Integer, primary_key=True),
>> > > Column('group_id',
>> > ForeignKey('group.gid',ondelete="CASCADE"),
>> > > nullable=False),
>> > > ....
>> > > ...
>> > >
>> > >
>> > > Based on the request, I need to map the GroupOrder to the
>> concerned
>> > > table and process the request.
>> > >
>> > > This is a uwsgi application will the clear_mapper affect other
>> worker
>> > > threads. ?
>> > >
>> > > Srikanth Bemineni
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > --
>> > > 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+...@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.
