Support Table Reflection For Materialized Views

[cieplak]

Table reflection works in Postgres for regular views, but when reflecting materialized views, a sqlalchemy.exc.NoSuchTableError exception is raised.

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 MATERIALIZED VIEW %s AS %s" % (
         element.name,
         compiler.process(element.select, literal_binds=True)
         )


from sqlalchemy import MetaData, Column, Integer
from sqlalchemy.engine import create_engine


uri = 'postgresql://postgres@localhost/postgres'
engine = create_engine(uri)
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))

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

[Mike Bayer]

your CreateView needs autocommit=True. Beyond that we just committed materialized view reflection today in #2891 as of 3a6cd72beaeed49b49619e4907.

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

CreateView._execution_options = {"autocommit": True}

@compiles(CreateView)
def visit_create_view(element, compiler, **kw):
    return "CREATE MATERIALIZED VIEW %s AS %s" % (
         element.name,
         compiler.process(element.select, literal_binds=True)
         )


from sqlalchemy import MetaData, Column, Integer
from sqlalchemy.engine import create_engine


uri = 'postgresql://scott:tiger@localhost/test'
engine = create_engine(uri, echo=True)
metadata = MetaData(engine)
t = Table('t',
          metadata,
          Column('id', Integer, primary_key=True),
          Column('number', Integer))
metadata.drop_all()
metadata.create_all()
engine.execute(t.insert().values(id=1, number=3))
engine.execute(t.insert().values(id=9, number=-3))

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

[Mike Bayer]

Duplicate of #2891.

[cieplak]

Sweet, thanks @zzzeek!