> On Oct 22, 2014, at 11:35 AM, Pedro Werneck <[email protected]> wrote: > > > I have one time with currently active data, and several other tables with > archive data, that are eventually moved to another database. Now I have to a > demand to make those archive tables available read-only through our API. I'm > using Flask and Flask-SQLAlchemy. > > I tried to do it by using polymorphic identity with subclasses generated > reflexively, and querying with the base class. It works in principle, but it > generates UNION queries with a filtering condition that performs poorly in > MySQL, and selecting exactly which tables to query from is a pain.
this doesn’t sound like a use case for inheritance or multiple mappings for a single class. Typically the approach is just to create copies of your mappings to a new series of classes. An example of automating this is the “versioned objects” example at http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html#module-examples.versioned_history <http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html#module-examples.versioned_history>. Another way you might look into, if you’re attempting to avoid explicit mappings, is the automap system: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html <http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html>. > > Searching the web and the group, I found a message[1] from 2008 with several > ideas on how to do that. I tried option 2, using a non-primary mapper, since > the caveats mentioned there aren't a problem for me. For testing, I did > something like this: > > > class User(Model): > __tablename__ = 'user_active' > user_id = db.Column(db.Integer, primary_key=True) > username = db.Column(db.String(32)) > > > user_1 = Table('user_1', db.metadata, > db.Column('user_id', db.Integer, primary_key=True), > db.Column('username', db.String(32))) > > user_2 = Table('user_2', db.metadata, > db.Column('user_id', db.Integer, primary_key=True), > db.Column('username', db.String(32))) > > user_3 = Table('user_3', db.metadata, > db.Column('user_id', db.Integer, primary_key=True), > db.Column('username', db.String(32))) > > > And apparently that works for a plain select query, with no filtering > criterion: > > >>> print db.session.query(mapper(User, user_1, non_primary=True)) > SELECT user_1.user_id AS user_1_user_id, user_1.username AS user_1_username > FROM user_1 > > However, as soon as I add filtering, this is what I get: right, because the column attributes linked to “User” are in terms of the base table, not the “non primary” table. The “non primary” mapper is mostly a historical artifact from before we had column attributes directly on classes, hence the message you found is from 2008. non primary mappers are still useful for one relationship() case that’s in the docs, so they hang around still, but they’re not useful for much else. If you need common functionality between User and whatever will be your UserHistory, you probably want to use mixins to duplicate those methods. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
