SQLAlchemy, using the same model with multiple tables

Question 1

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?

Question 2

Please take a look at stackoverflow.com/a/22825638/99594 in the Concrete Table Inheritance section. I would not like to re-post it as an answer, as it is quite long. In your case you can query MyTable only when working with the current data, and do a polymorphic search when looking for all the history.

Question 3

Thanks. My case is more simple it's the same entity distributed horizontally, not multiple entities defined by inheritance. Considering that, it seems to be the same solution as the one by @viktor.likin answer below.

Question 4

Inheritance is just a trick to get SA to query across multiple tables. But if you do not need that, viktor's solution is perfect. Note that if you have foreign keys from your table(s), you will need to use declared_attr

Question 5

Yeah, I just stumbled on that. Can you clarify the point about inheritance being just a trick to get SA to query across multiple tables. Does that work with the horizontal partitioning I'm doing? SQLAlchemy does an UNION automatically?

Question 6

Yes, it can work with the horizontal partitioning you are doing. The trick is to query all (or some of) the (fake) inheritance tree polymorphically. This is to say: if you make SA think that all those tables are "children" of MyBaseTable, and you execute a query polymorphically, SA will fetch data from all these tables (mytable, mytable_2012, etc) automatically for you. Indeed, SA does a UNION automatically.

Question 7

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'

Question 8

This method is less complicated than the accepted answer. Here is a link to a recipe that uses a similar approach: bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName

Question 9

not working anymore, produces NoForeignKeysError("Can't find any foreign key relationships between 'Node and 'Node1'.",)

Question 10

The answer is correct, the code is not (or not anymore ;-)). Define the mixin by a) replicating the 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/…

Question 11

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.

Question 12

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"

Victor Shelepen 2,3663 gold badges24 silver badges56 bronze badges · Accepted Answer · 2014-08-26 14:36:49Z

12

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'

Share

Improve this answer

answered Aug 26, 2014 at 14:36

Victor Shelepen's user avatar

Victor Shelepen

2,3663 gold badges24 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Will

Will Over a year ago

This method is less complicated than the accepted answer. Here is a link to a recipe that uses a similar approach: bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName

2015年07月05日T15:12:39.863Z+00:00

nicolaspanel

nicolaspanel Over a year ago

not working anymore, produces NoForeignKeysError("Can't find any foreign key relationships between 'Node and 'Node1'.",)

2017年09月01日T09:56:35.093Z+00:00

taffit

taffit Over a year ago

The answer is correct, the code is not (or not anymore ;-)). Define the mixin by a) replicating the 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/…

2021年09月24日T15:22:45.063Z+00:00

CollectivesTM on Stack Overflow

SQLAlchemy, using the same model with multiple tables

3 Answers 3

Comments

Update for SQLAlchemy 2.0

Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Linked

Hot Network Questions

CollectivesTM on Stack Overflow

3 Answers 3

3 Comments

Comments

Update for SQLAlchemy 2.0

Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Linked

Related