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