Postgresql partition and sqlalchemy

Maybe a bit late, but I would like to share what I built upon @moshevi 's and @Seb 's answers:

In my IoT use-case, I required actual sub-partitioning (first level year, second level nodeid). Also I wanted to generalize it slightly.

This is what I came up with:

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

class PartitionByMeta(DeclarativeMeta):
    def __new__(cls, clsname, bases, attrs, *, partition_by, partition_type):

        @classmethod
        def get_partition_name(cls_, suffix):
            return f'{cls_.__tablename__}_{suffix}'

        @classmethod
        def create_partition(cls_, suffix, partition_stmt, subpartition_by=None, subpartition_type=None):
            if suffix not in cls_.partitions:

                partition = PartitionByMeta(
                    f'{clsname}{suffix}',
                    bases,
                    {'__tablename__': cls_.get_partition_name(suffix)},
                    partition_type = subpartition_type,
                    partition_by=subpartition_by,
                )

                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
                        # LIST: IN ('first', 'second');
                        # RANGE: FROM ('{key}-01-01') TO ('{key+1}-01-01')
                        f"""
                        ALTER TABLE {cls_.__tablename__}
                        ATTACH PARTITION {partition.__tablename__}
                        {partition_stmt};
                        """
                    )
                )
                
                cls_.partitions[suffix] = partition
            
            return cls_.partitions[suffix]
        
        if partition_by is not None:
            attrs.update(
                {
                    '__table_args__': attrs.get('__table_args__', ())
                    + (dict(postgresql_partition_by=f'{partition_type.upper()}({partition_by})'),),
                    'partitions': {},
                    'partitioned_by': partition_by,
                    'get_partition_name': get_partition_name,
                    'create_partition': create_partition
                }
            )
        
        return super().__new__(cls, clsname, bases, attrs)

Which is to be used as follows, assuming the respective VehicleDataMixin class to be created as introduced by @moshevi

class VehicleData(VehicleDataMixin, Project, metaclass=PartitionByMeta, partition_by='timestamp',partition_type='RANGE'):
    __tablename__ = 'vehicle_data'
    __table_args__ = (
        Index('ts_ch_nod_idx', "timestamp", "nodeid", "channelid", postgresql_using='brin'),
        UniqueConstraint('timestamp','nodeid','channelid', name='ts_ch_nod_constr')
    )

Which can then be subpartitoned iteratively like so (to be adapted)

    for y in range(2017, 2021): 
         # Creating tables for all known nodeids
        tbl_vehid_y = VehicleData.create_partition(
            f"{y}", partition_stmt=f"""FOR VALUES FROM ('{y}-01-01') TO ('{y+1}-01-01')""",
            subpartition_by='nodeid', subpartition_type='LIST'
        )
        
        for i in {3, 4, 7, 9}:
            # Creating all the years below these nodeids including a default partition
            tbl_vehid_y.create_partition(
                f"nid{i}", partition_stmt=f"""FOR VALUES IN ('{i}')"""
            )
        
        # Defaults (nodeid) per year partition
        tbl_vehid_y.create_partition("def", partition_stmt="DEFAULT")

   # Default to any other year than anticipated
   VehicleData.create_partition("def", partition_stmt="DEFAULT")

partition_by='timestamp' <= This is the column to partition by

partition_type='RANGE' <= This is the (PSQL specific) partition type

partition_stmt=f"""FOR VALUES IN ('{i}')""" <= This is the (PSQL specific) partitioning statement.