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