pythonsqlsqlalchemy

VALUES clause in SQLAlchemy


Is there a way to build a Query object in SQLAlchemy which will be the equivalent of:

SELECT * FROM (VALUES (1, 2, 3)) AS sq;

From what I see in the documentation, the VALUES clause appears only in use with INSERT.


Solution

  • well "VALUES" in an insert is the standard SQL, the standalone "VALUES" keyword is a Postgresql thing. There's a quick compiler recipe for this one at PGValues (copied here in case I change the wiki someday):

    from sqlalchemy import *
    from sqlalchemy.ext.compiler import compiles
    from sqlalchemy.sql.expression import FromClause
    from sqlalchemy.sql import table, column
    
    class values(FromClause):
        def __init__(self, *args):
            self.list = args
    
        def _populate_column_collection(self):
            self._columns.update(
                [("column%d" % i, column("column%d" % i))
                        for i in xrange(1, len(self.list[0]) + 1)]
            )
    
    @compiles(values)
    def compile_values(element, compiler, asfrom=False, **kw):
        v = "VALUES %s" % ", ".join(
            "(%s)" % ", ".join(compiler.render_literal_value(elem, None) for elem in tup)
            for tup in element.list
        )
        if asfrom:
            v = "(%s)" % v
        return v
    
    if __name__ == '__main__':
        t1 = table('t1', column('a'), column('b'))
        t2 = values((1, 0.5), (2, -0.5)).alias('weights')
        print select([t1, t2]).select_from(t1.join(t2, t1.c.a==t2.c.column2))