1. Michael Bayer
  2. sqlalchemy


Clone wiki

sqlalchemy / UsageRecipes / PartitionTable


This recipe allows you to create a construct from a Table that gives it a different name. This could be used to select from some horizontal or inheritance partitions in PG, for example. This is 95% like an alias(), just different rendering:

from sqlalchemy.sql.expression import Alias
from sqlalchemy.ext.compiler import compiles

class Partition(Alias):
    """Represents a 'table partition'."""

    def alias(self, name=None):
        """Allow alias() of the partition to take place."""

        a = Alias(self, name)
        a.original = self
        return a

def visit_partition(element, compiler, **kw):
    if kw.get('asfrom'):
        return element.name
        return compiler.visit_alias(element)

if __name__ == '__main__':
    from sqlalchemy import *

    metadata = MetaData()
    t1 = Table('sometable', metadata,
        Column('id', Integer, primary_key=True),
        Column('data', String(50))

    print select([t1]).where(t1.c.data == 'foo')


    t1_partition_a = Partition(t1, "partition_a")
    print select([t1_partition_a]).where(t1_partition_a.c.data=='foo')


    t1_p_alias = t1_partition_a.alias()
    print select([t1_p_alias]).where(t1_p_alias.c.data=='foo')


SELECT sometable.id, sometable.data 
FROM sometable 
WHERE sometable.data = :data_1

SELECT partition_a.id, partition_a.data 
FROM partition_a 
WHERE partition_a.data = :data_1

SELECT sometable_1.id, sometable_1.data 
FROM partition_a AS sometable_1 
WHERE sometable_1.data = :data_1