jeffwidman/sqlalchemy-postgresql-materialized-views

A SQLAlchemy recipe for managing PostgreSQL Materialized Views:

sqlalchemy-postgresql-materialized-views's Issues

When is the view created?

HI Jeff,

This is a very nice article, thank you for taking your time to write it. Looking at the code, I was wondering if the materialized view needs to be created manually in advance in the database or it will be automatically created by the model.
I cannot figure out where (and how) is the execution of 'CREATE MATERIALIZED VIEW ....'.

Thank you

Availability as a module / integration in SQLAlchemy

Hello,

I read your blog post, thanks for your work it was an excellent read. I was thinking of reusing your code for managing my materialized views in Python. Are you aware of any existing effort to publish this as a dedicated module, or to integrate it in SQLAlchemy directly (for example as a dialect extension)?

Follow-up question, what do you think would be the roadblocks to such an integration, technically and otherwise?

Best regards,
Olivier

column type: InstrumentedAttribute

Following the method of Jeff Widman to generate materialized views with SQLAlchemy; the Column types from the selectables are parsed roughly like:

selectable = select([MyObject])
table = Table('new_table', MetaData())
for c in selectable.c:
    table.append_column(Column(c.name, c.type, primary_key=c.primary_key))

If I print the variable table, I get the correct column types:

Table('new_table', MetaData(bind=None), Column('column1', String(length=20), table=<new_table>), Column('id', UUIDType(length=16), table=<new_table>, primary_key=True, nullable=False), Column('jsonb_column', JSONB(astext_type=Text()), table=<new_table>), Column('record_created', DateTime(), table=<new_table>), schema=None)

However, when I query the MV class. The column types are changed to type InstrumentedAttribute:

sqlalchemy.inspect(MyMaterializedView.column1)

sqlalchemy.orm.attributes.InstrumentedAttribute at 0x7fe420fd4ba0

Why are the types not correct? And how can I fix this?

(BTW, I'm on Pyramid which might differ slightly from Flask.)