I have data for a particular entity partitioned across multiple identical tables, often separated chronologically or by numeric range. For instance, I may have a table called mytable for current data, a mytable_2013 for last year's data, mytable_2012, and so on.
Only the current table is ever written to. The others are only consulted. With SQLAlchemy, is there any way I can specify the table to query from when using the declarative model?
3 Answers 3
Use mixins and change table names by an object property.
class Node(Base):
__tablename__ = 'node'
nid = Column(Integer, primary_key=True)
uuid = Column(String(128))
vid = Column(Integer)
class Node1(Node):
__tablename__ = 'node_1'
class Node2(Node):
__tablename__ = 'node_2'
NoForeignKeysError("Can't find any foreign key relationships between 'Node and 'Node1'.",)Node-class as class NodeMixin(object): followed by the column definitions of the Node-class but without the __table_name__-property and b) for Node1 and Node2 inherit from the mixin and Base, e. g. class Node1(NodeMixin, Base): __tablename__ = "node_1". See: docs.sqlalchemy.org/en/14/orm/… As requested, re-posting as answer:
Please take a look at this answer to Mapping lots of similar tables in SQLAlchemy in the Concrete Table Inheritance section.
In your case you can query MyTable only when working with the current data, and do a polymorphic search on all tables when you need the whole history.
Comments
Update for SQLAlchemy 2.0
The syntax changed slightly. Most importantly, the mixin-class does not inherit from Base anymore (SQLAlchemy 2.0).
class MultiTableMixin:
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[Optional[str]]
class MyModel(MultiTableMixin, Base):
__tablename__ = "table1"
class MyModel(MultiTableMixin, Base):
__tablename__ = "table2"
Concrete Table Inheritancesection. I would not like to re-post it as an answer, as it is quite long. In your case you can queryMyTableonly when working with the current data, and do apolymorphicsearch when looking for all the history.declared_attrpolymorphically. This is to say: if you make SA think that all those tables are "children" ofMyBaseTable, and you execute a query polymorphically, SA will fetch data from all these tables (mytable,mytable_2012, etc) automatically for you. Indeed, SA does aUNIONautomatically.