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. 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: >>> print db.session.query(mapper(User, user_1, non_primary=True)).filter_by(username='pedro') SELECT user_1.user_id AS user_1_user_id, user_1.username AS user_1_username FROM user_1, user_active WHERE user_active.username = :username_1 And I was expecting no mention of the table user_active at all. So, two questions: 1. Is there a better approach to do what I'm trying to do? 2. If the above is a good approach, what am I doing wrong? Thanks [1] https://groups.google.com/forum/#!msg/sqlalchemy/FTUo-bMJuYc/NClEROL8n_4J -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.