Postgresql 分区和 sqlalchemy [英] Postgresql partition and sqlalchemy

问题描述

SQLAlchemy doc 解释如何创建分区桌子.但它没有解释如何创建分区.

SQLAlchemy doc explain how to create a partitioned table. But it does not explains how to create partitions.

所以如果我有这个:

#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 measure WHERE logdate between XX and YY.但这似乎很有趣.

I know that most of the I'll be doing SELECT * FROM measures WHERE logdate between XX and YY. But that seems interesting.

推荐答案

您可以使用 MeasureMixin 两个类都可以继承.然后使用 event 附加表分区.

You can use a MeasureMixin that both classes can inherit from. And then use an event to attach the table partition.

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');""")
)

这篇关于Postgresql 分区和 sqlalchemy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！