Wiki

Clone wiki

sqlalchemy / UsageRecipes / SelectInto

SelectInto

SQLAlchemy should be adding this to the select() construct at some point, here's a recipe for now to generate SELECT..INTO TEMPORARY TABLE.

from sqlalchemy.sql import Select
from sqlalchemy.ext.compiler import compiles

class SelectInto(Select):
    def __init__(self, columns, into, *arg, **kw):
        super(SelectInto, self).__init__(columns, *arg, **kw)
        self.into = into

@compiles(SelectInto)
def s_into(element, compiler, **kw):
    text = compiler.visit_select(element)
    text = text.replace('FROM', 
                'INTO TEMPORARY TABLE %s FROM' % 
                element.into)
    return text


if __name__ == '__main__':
    from sqlalchemy.sql import table, column

    marker = table('marker', 
        column('x1'),
        column('x2'),
        column('x3')
    )

    print SelectInto([marker.c.x1, marker.c.x2], "tmp_markers").\
            where(marker.c.x3==5).\
            where(marker.c.x1.in_([1, 5]))

Updated