["python","postgresql","sqlalchemy"]
How to create an SQL View with SQLAlchemy?

Is there a "Pythonic" way (I mean, no "pure SQL" query) to define an SQL view with SQLAlchemy?
Update: SQLAlchemy now has a great usage recipe here on this topic, which I recommend. It covers different SQL Alchemy versions up to the latest and has ORM integration (see comments below this answer and other answers). And if you look through the version history, you can also learn why using literal_binds is iffy (in a nutshell: binding parameters should be left to the database), but still arguably any other solution would make most users of the recipe not happy. I leave the below answer mostly for historical reasons. Original answer: Creating a (read-only non-materialized) view is not supported out of the box as far as I know. But adding this functionality in SQLAlchemy 0.7 is straightforward (similar to the example I gave here). You just have to write a compiler extension CreateView. With this extension, you can then write (assuming that t is a table object with a column id) createview = CreateView('viewname', t.select().where(t.c.id>5)) engine.execute(createview) v = Table('viewname', metadata, autoload=True) for r in engine.execute(v.select()): print r Here is a working example: from sqlalchemy import Table from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import Executable, ClauseElement class CreateView(Executable, ClauseElement): def __init__(self, name, select): self.name = name self.select = select @compiles(CreateView) def visit_create_view(element, compiler, **kw): return "CREATE VIEW %s AS %s" % ( element.name, compiler.process(element.select, literal_binds=True) ) # test data from sqlalchemy import MetaData, Column, Integer from sqlalchemy.engine import create_engine engine = create_engine('sqlite://') metadata = MetaData(engine) t = Table('t', metadata, Column('id', Integer, primary_key=True), Column('number', Integer)) t.create() engine.execute(t.insert().values(id=1, number=3)) engine.execute(t.insert().values(id=9, number=-3)) # create view createview = CreateView('viewname', t.select().where(t.c.id>5)) engine.execute(createview) # reflect view and print result v = Table('viewname', metadata, autoload=True) for r in engine.execute(v.select()): print r If you want, you can also specialize for a dialect, e.g. @compiles(CreateView, 'sqlite') def visit_create_view(element, compiler, **kw): return "CREATE VIEW IF NOT EXISTS %s AS %s" % ( element.name, compiler.process(element.select, literal_binds=True) )