SQLAlchemy View support

view.py

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement
from sqlalchemy.schema import DDLElement, DropTable
from sqlalchemy.sql import table
from sqlalchemy.orm import Query

from . import db


class CreateView(DDLElement):
    def __init__(self, name, selectable):
        self.name = name
        self.selectable = selectable


class DropView(DDLElement):
    def __init__(self, name):
        self.name = name


@compiles(CreateView)
def compile_create_view(element, compiler, **kw):
    return "CREATE VIEW %s AS %s" % (element.name, compiler.sql_compiler.process(element.selectable))


@compiles(DropView)
def compile_drop_view(element, compiler, **kw):
    return "DROP VIEW IF EXISTS %s" % (element.name)


def View(name, selectable):
    """
        `View` support for SQLAlchemy
        See: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views
    """

    t = table(name)

    if isinstance(selectable, Query):
        selectable = selectable.subquery()

    for c in selectable.c:
        c._make_proxy(t)

    CreateView(name, selectable).execute_at('after-create', db.metadata)
    DropView(name).execute_at('before-drop', db.metadata)

    return t