Class CreateTableAs

lib/sqlalchemy/sql/ddl.py:652–755

Represent a CREATE TABLE ... AS statement. This creates a new table directly from the output of a SELECT, including its schema and its initial set of data. Unlike a view, the new table is fixed and does not synchronize further with the originating SELECT statement. The exampl

class CreateTableAs(DialectKWArgs, _TableViaSelect):
    """Represent a CREATE TABLE ... AS statement.

    This creates a new table directly from the output of a SELECT, including
    its schema and its initial set of data.   Unlike a view, the
    new table is fixed and does not synchronize further with the originating
    SELECT statement.

    The example below illustrates basic use of :class:`.CreateTableAs`; given a
    :class:`.Select` and optional :class:`.MetaData`, the
    :class:`.CreateTableAs` may be invoked directly via
    :meth:`.Connection.execute` or indirectly via :meth:`.MetaData.create_all`;
    the :attr:`.CreateTableAs.table` attribute provides a :class:`.Table`
    object with which to generate new queries::

        from sqlalchemy import CreateTableAs
        from sqlalchemy import select

        # instantiate CreateTableAs given a select() and optional MetaData
        cas = CreateTableAs(
            select(users.c.id, users.c.name).where(users.c.status == "active"),
            "active_users",
            metadata=some_metadata,
        )

        # a Table object is available immediately via the .table attribute
        new_statement = select(cas.table)

        # to emit CREATE TABLE AS, either invoke CreateTableAs directly...
        with engine.begin() as conn:
            conn.execute(cas)

        # or alternatively, invoke metadata.create_all()
        some_metdata.create_all(engine)

        # drop is performed in the usual way, via drop_all
        # or table.drop()
        some_metdata.drop_all(engine)

    For detailed background on :class:`.CreateTableAs` see
    :ref:`metadata_create_table_as`.

    .. versionadded:: 2.1

    :param selectable: :class:`_sql.Select`
        The SELECT statement providing the columns and rows.

    :param table_name: table name as a string. Combine with the optional
        :paramref:`.CreateTableAs.schema` parameter to indicate a
        schema-qualified table name.

    :param metadata: :class:`_schema.MetaData`, optional
        If provided, the :class:`_schema.Table` object available via the
        :attr:`.table` attribute will be associated with this
        :class:`.MetaData`.  Otherwise, a new, empty :class:`.MetaData`
        is created.

    :param schema: str, optional schema or owner name.