CODEWITHSUNDEEP
pythonsqlalchemy
Pedro Werneck
Pedro Werneck

Reputation: 41888

SQLAlchemy, using the same model with multiple tables

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?

Upvotes: 9

Views: 11735

Answers (3)

ffrosch
ffrosch

Reputation: 1383

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"

Upvotes: 1

van
van

Reputation: 76962

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.

Upvotes: 5

Victor Shelepen
Victor Shelepen

Reputation: 2256

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'

Upvotes: 10

Related Questions