Skip to content

PartitionTable

Jump to bottom
Mike Bayer edited this page Feb 20, 2014 · 2 revisions

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:

#!python

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
        
@compiles(Partition)
def visit_partition(element, compiler, **kw):
    if kw.get('asfrom'):
        return element.name
    else:
        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')

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

output:

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
Clone this wiki locally