时间戳

首页 > 解决方案 > Postgresql 分区和 sqlalchemy

python - Postgresql 分区和 sqlalchemy

问题描述

SQLAlchemy 文档解释了如何创建分区表。但它没有解释如何创建分区。

所以如果我有这个：

#Skipping create_engine and metadata
Base = declarative_base()

class Measure(Base):
    __tablename__ = 'measures'
    __table_args__ = {
        postgresql_partition_by: 'RANGE (log_date)'
    }
    city_id = Column(Integer, not_null=True)
    log_date = Columne(Date, not_null=True)
    peaktemp = Column(Integer)
    unitsales = Column(Integer)

class Measure2020(Base):
    """How am I suppposed to declare this ? """

我知道我会做的大部分事情SELECT * FROM measures WHERE logdate between XX and YY。但这似乎很有趣。

标签： pythonpostgresqlsqlalchemypostgresql-12

解决方案

您可以使用MeasureMixin两个类都可以继承的 a 。然后使用 anevent来附加表分区。

from sqlalchemy import event

class MeasureMixin:
    city_id = Column(Integer, not_null=True)
    log_date = Column(Date, not_null=True)
    peaktemp = Column(Integer)
    unitsales = Column(Integer)

class Measure(MeasureMixin, Base):
    __tablename__ = 'measures'
    __table_args__ = {
        postgresql_partition_by: 'RANGE (log_date)'
    }

class Measure2020(MeasureMixin, Base):
    __tablename__ = 'measures2020'

Measure2020.__table__.add_is_dependent_on(Measure.__table__)

event.listen(
    Measure2020.__table__,
    "after_create",
    DDL("""ALTER TABLE measures ATTACH PARTITION measures2020
VALUES FROM ('2020-01-01') TO ('2021-01-01');""")
)

推荐阅读