How to create an SQL View with SQLAlchemy?
Question: Everything is in the title. Is there a "Pythonic" way (I mean, no "pure SQL"
query) to define an SQL view with SQLAlchemy ?

Thanks for your help,


Answer: **Update:** See also the SQLAlchemy usage recipe
[here](https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views)

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](http://stackoverflow.com/a/9597404/92092)). You just have to write a
[compiler extension](http://docs.sqlalchemy.org/en/latest/core/compiler.html)
`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)
             )
    

