Postgresql分区和sqlalchemy

Question

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

如果我有这个：

#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。但这似乎很有趣

Answer 1

对于数据库分区（PostgreSQL或MySQL），可以尝试使用architect包。它与一系列ORM库一起工作，包括SQLAlchemy。下面是PostgreSQL-https://architect.readthedocs.io/features/partition/postgresql.html的一个示例。支持几种分区类型，因此希望它能够满足您案例的要求

Answer 2

我也有类似的问题。我发现@moshevi的答案非常有用，并最终对其进行了一些推广（因为我有许多表要分区）

首先，创建一个元类，如下所示：

from sqlalchemy.ext.declarative import DeclarativeMeta
from sqlalchemy.sql.ddl import DDL
from sqlalchemy import event


class PartitionByYearMeta(DeclarativeMeta):
    def __new__(cls, clsname, bases, attrs, *, partition_by):
        @classmethod
        def get_partition_name(cls_, key):
            # 'measures' -> 'measures_2020' (customise as needed)
            return f'{cls_.__tablename__}_{key}'
        
        @classmethod
        def create_partition(cls_, key):
            if key not in cls_.partitions:
                
                Partition = type(
                    f'{clsname}{key}', # Class name, only used internally
                    bases,
                    {'__tablename__': cls_.get_partition_name(key)}
                )
                
                Partition.__table__.add_is_dependent_on(cls_.__table__)
                
                event.listen(
                    Partition.__table__,
                    'after_create',
                    DDL(
                        # For non-year ranges, modify the FROM and TO below
                        f"""
                        ALTER TABLE {cls_.__tablename__}
                        ATTACH PARTITION {Partition.__tablename__}
                        FOR VALUES FROM ('{key}-01-01') TO ('{key+1}-01-01');
                        """
                    )
                )
                
                cls_.partitions[key] = Partition
            
            return cls_.partitions[key]
        
        attrs.update(
            {
                # For non-RANGE partitions, modify the `postgresql_partition_by` key below
                '__table_args__': attrs.get('__table_args__', ())
                + (dict(postgresql_partition_by=f'RANGE({partition_by})'),),
                'partitions': {},
                'partitioned_by': partition_by,
                'get_partition_name': get_partition_name,
                'create_partition': create_partition
            }
        )
        
        return super().__new__(cls, clsname, bases, attrs)

接下来，对于模型中要分区的任何表：

class MeasureMixin:
    # The columns need to be pulled out into this mixin
    # Note: any foreign key columns will need to be wrapped like this:

    @declared_attr
    def city_id(self):
        return Column(ForeignKey('cities.id'), not_null=True)
    
    log_date = Column(Date, not_null=True)
    peaktemp = Column(Integer)
    unitsales = Column(Integer)

class Measure(MeasureMixin, Base, metaclass=PartitionByYearMeta, partition_by='logdate'):
    __tablename__ = 'measures'

这使得添加更多表和按任意数量的值分区变得容易

动态创建新分区的工作原理如下：

# Make sure you commit any session that is currently open, even for select queries:
session.commit()

Partition = Measure.create_partition(2020)
if not engine.dialect.has_table(Partition.__table__.name):
    Partition.__table__.create(bind=engine)

现在创建了键2020的分区，可以插入当年的值

Answer 3

您可以使用两个类都可以从中继承的^{}。 然后使用^{}来附加表分区

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