Dynamically change table name to select from for query

[Stanislav Lobanov]

Hello!

I have a database with 10 tables with same structure. This structure of a tables are always the same.

I want to be able to query this tables using one declarative mapper instead of 10 similar mappers, but to do so i need a way to change table name somehow.

Desired code:

class T(Base):
   some_column = Column(Integer)  # just an example

table_a_results = session.query(T).select_from_table('table_a').filter(T.some_column > 10).count()

table_b_results = session.query(T).select_from_table('table_b').filter(T.some_column > 10).count()

Are there any possibility to implement such query feature? Are there any alternative variants to this feature?

Thanks!

[Mike Bayer]

the canonical solution is the"entity name" pattern at
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName,
however this does establish subclasses-per-table even though it is
automated.

To truly use just a single class, the caveat is that the identity map
will not have any cue to store the object from a different table if it
already has an identity of that row (there is an "identity_token"
feature that can also work around this, it's new and experimental but
i can illustrate if you want to get into that).

For single class see the example below.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
x = Column(Integer)
y = Column(Integer)

def __repr__(self):
return "A(id=%r, x=%r, y=%r)" % (self.id, self.x, self.y)

e = create_engine("sqlite://", echo=True)
with e.connect() as conn:
conn.execute("""
CREATE TABLE a (
id INTEGER NOT NULL, x INTEGER, y INTEGER, PRIMARY KEY (id)
)
""")
conn.execute("""
CREATE TABLE b (
id INTEGER NOT NULL, x INTEGER, y INTEGER, PRIMARY KEY (id)
)
""")
conn.execute("""
CREATE TABLE c (
id INTEGER NOT NULL, x INTEGER, y INTEGER, PRIMARY KEY (id)
)
""")
conn.execute("INSERT INTO a (id, x, y) VALUES (1, 2, 3), (4, 5, 6)")
conn.execute("INSERT INTO b (id, x, y) VALUES (7, 8, 9), (10, 11, 12)")
conn.execute("INSERT INTO c (id, x, y) VALUES (13, 14, 15), (16, 17, 18)")

s = Session(e)

b_table = Table('b', Base.metadata, autoload_with=e)
c_table = Table('c', Base.metadata, autoload_with=e)

print(s.query(A).all())

# if overlapping primary keys between "a" and "b", need to remove all "A"
# instances to load them from "b", these are in the identity map against table
# "a". Use the full "entity name" pattern, or the identity_token, to avoid this
s.close()

print(s.query(A).select_entity_from(
aliased(A, b_table, adapt_on_names=True)).all())


# if overlapping primary keys between "b" and "c", need to remove all "A"
# instances to load them from "c", these are in the identity map against table
# "b". Use the full "entity name" pattern, or the identity_token, to avoid this
s.close()
print(s.query(A).select_entity_from(
aliased(A, c_table, adapt_on_names=True)).all())

> --
> 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.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Stanislav Lobanov]

Thank you very much, your answer is very detailed as always. I think that this approach may fit to me, because this tables are read-only, because it is kind of datawarehouse.

Thanks!

суббота, 3 марта 2018 г., 10:45:45 UTC+3 пользователь Stanislav Lobanov написал: