View utilities

create_view

sqlalchemy_utils.create_view(name, selectable, metadata, cascade_on_drop=True, replace=False)[source]

Create a view on a given metadata

Parameters:
  • name – The name of the view to create.
  • selectable – An SQLAlchemy selectable e.g. a select() statement.
  • metadata – An SQLAlchemy Metadata instance that stores the features of the database being described.
  • cascade_on_drop – If True the view will be dropped with CASCADE, deleting all dependent objects as well.
  • replace – If True the view will be created with OR REPLACE, replacing an existing view with the same name.

The process for creating a view is similar to the standard way that a table is constructed, except that a selectable is provided instead of a set of columns. The view is created once a CREATE statement is executed against the supplied metadata (e.g. metadata.create_all(..)), and dropped when a DROP is executed against the metadata.

To create a view that performs basic filtering on a table.

metadata = MetaData()
users = Table('users', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String),
        Column('fullname', String),
        Column('premium_user', Boolean, default=False),
    )

premium_members = select(users).where(users.c.premium_user == True)
# sqlalchemy 1.3:
# premium_members = select([users]).where(users.c.premium_user == True)
create_view('premium_users', premium_members, metadata)

metadata.create_all(engine) # View is created at this point

create_materialized_view

sqlalchemy_utils.create_materialized_view(name, selectable, metadata, indexes=None, aliases=None)[source]

Create a view on a given metadata

Parameters:
  • name – The name of the view to create.
  • selectable – An SQLAlchemy selectable e.g. a select() statement.
  • metadata – An SQLAlchemy Metadata instance that stores the features of the database being described.
  • indexes – An optional list of SQLAlchemy Index instances.
  • aliases – An optional dictionary containing with keys as column names and values as column aliases.

Same as for create_view except that a CREATE MATERIALIZED VIEW statement is emitted instead of a CREATE VIEW.

refresh_materialized_view

sqlalchemy_utils.refresh_materialized_view(session, name, concurrently=False)[source]

Refreshes an already existing materialized view

Parameters:
  • session – An SQLAlchemy Session instance.
  • name – The name of the materialized view to refresh.
  • concurrently – Optional flag that causes the CONCURRENTLY parameter to be specified when the materialized view is refreshed.