sqlalchemy.schema DDLElement Example Code

DDLElement is a class within the sqlalchemy.schema module of the SQLAlchemy project.

CheckConstraint, Column, CreateIndex, CreateTable, ForeignKey, ForeignKeyConstraint, Index, PrimaryKeyConstraint, and Table are several other callables with code examples from the same sqlalchemy.schema package.

Example 1 from alembic

Alembic (project documentation and PyPI page) is a data migrations tool used with SQLAlchemy to make database schema changes. The Alembic project is open sourced under the MIT license.

alembic / alembic / ddl / base.py

# base.py
import functools

from sqlalchemy import exc
from sqlalchemy import Integer
from sqlalchemy import types as sqltypes
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import Column
from sqlalchemy.schema import DDLElement
from sqlalchemy.sql.elements import quoted_name

from ..util import sqla_compat
from ..util.sqla_compat import _columns_for_constraint  # noqa
from ..util.sqla_compat import _find_columns  # noqa
from ..util.sqla_compat import _fk_spec  # noqa
from ..util.sqla_compat import _is_type_bound  # noqa
from ..util.sqla_compat import _table_for_constraint  # noqa


class AlterTable(DDLElement):


    def __init__(self, table_name, schema=None):
        self.table_name = table_name
        self.schema = schema


class RenameTable(AlterTable):
    def __init__(self, old_table_name, new_table_name, schema=None):
        super(RenameTable, self).__init__(old_table_name, schema=schema)
        self.new_table_name = new_table_name


class AlterColumn(AlterTable):
    def __init__(
        self,
        name,
        column_name,
        schema=None,
        existing_type=None,
        existing_nullable=None,
        existing_server_default=None,
        existing_comment=None,
    ):


## ... source file continues with no further DDLElement examples...

Example 2 from Amazon Redshift SQLAlchemy Dialect

Amazon Redshift SQLAlchemy Dialect is a SQLAlchemy Dialect that can communicate with the AWS Redshift data store. The SQL is essentially PostgreSQL and requires psycopg2 to properly operate. This project and its code are open sourced under the MIT license.

Amazon Redshift SQLAlchemy Dialect / sqlalchemy_redshift / ddl.py

# ddl.py
import sqlalchemy as sa
from sqlalchemy.ext import compiler as sa_compiler
from sqlalchemy.schema import DDLElement

from .compat import string_types


def _check_if_key_exists(key):
    return isinstance(key, sa.Column) or key


def get_table_attributes(preparer,
                         diststyle=None,
                         distkey=None,
                         sortkey=None,
                         interleaved_sortkey=None):
    text = ""

    has_distkey = _check_if_key_exists(distkey)
    if diststyle:
        diststyle = diststyle.upper()
        if diststyle not in ('EVEN', 'KEY', 'ALL'):
            raise sa.exc.ArgumentError(
                u"diststyle {0} is invalid".format(diststyle)
            )
        if diststyle != 'KEY' and has_distkey:
            raise sa.exc.ArgumentError(


## ... source file abbreviated to get to DDLElement examples ...


    text = """\
        CREATE MATERIALIZED VIEW {name}
        {backup}
        {table_attributes}
        AS {selectable}\
    Drop the materialized view from the database.
    SEE:
    docs.aws.amazon.com/redshift/latest/dg/materialized-view-drop-sql-command

    This undoes the create command, as expected:

    >>> import sqlalchemy as sa
    >>> from sqlalchemy_redshift.dialect import DropMaterializedView
    >>> engine = sa.create_engine('redshift+psycopg2://example')
    >>> drop = DropMaterializedView(
    ...     'materialized_view_of_users',
    ...     if_exists=True
    ... )
    >>> print(drop.compile(engine))
    <BLANKLINE>
    DROP MATERIALIZED VIEW IF EXISTS materialized_view_of_users
    <BLANKLINE>
    <BLANKLINE>

    This can be included in any execute() statement.
        Build the DropMaterializedView DDLElement.

        Parameters
        ----------
        name: str
            name of the materialized view to drop
        if_exists: bool, optional
            if True, the IF EXISTS clause is added. This will make the query
            successful even if the view does not exist, i.e. it lets you drop
            a non-existant view. Defaults to False.
        cascade: bool, optional
            if True, the CASCADE clause is added. This will drop all
            views/objects in the DB that depend on this materialized view.
            Defaults to False.
    Formats and returns the drop statement for materialized views.

            distkey = distkey.name
        text += " DISTKEY ({0})".format(preparer.quote(distkey))

    has_sortkey = _check_if_key_exists(sortkey)
    has_interleaved = _check_if_key_exists(interleaved_sortkey)
    if has_sortkey and has_interleaved:
        raise sa.exc.ArgumentError(
            "Parameters sortkey and interleaved_sortkey are "
            "mutually exclusive; you may not specify both."
        )

    if has_sortkey or has_interleaved:
        keys = sortkey if has_sortkey else interleaved_sortkey
        if isinstance(keys, (string_types, sa.Column)):
            keys = [keys]
        keys = [key.name if isinstance(key, sa.Column) else key
                for key in keys]
        if has_interleaved:
            text += " INTERLEAVED"
        sortkey_string = ", ".join(preparer.quote(key)
                                   for key in keys)
        text += " SORTKEY ({0})".format(sortkey_string)
    return text


class CreateMaterializedView(DDLElement):
    def __init__(self, name, selectable, backup=True, diststyle=None,
                 distkey=None, sortkey=None, interleaved_sortkey=None):
        self.name = name
        self.selectable = selectable
        self.backup = backup
        self.diststyle = diststyle
        self.distkey = distkey
        self.sortkey = sortkey
        self.interleaved_sortkey = interleaved_sortkey


@sa_compiler.compiles(CreateMaterializedView)
def compile_create_materialized_view(element, compiler, **kw):



## ... source file continues with no further DDLElement examples...

Example 3 from sqlalchemy-utils

sqlalchemy-utils (project documentation and PyPI package information) is a code library with various helper functions and new data types that make it easier to use SQLAlchemy when building projects that involve more specific storage requirements such as currency. The wide array of data types includes ranged values and aggregated attributes.

sqlalchemy-utils / sqlalchemy_utils / view.py

# view.py
import sqlalchemy as sa
from sqlalchemy.ext import compiler
from sqlalchemy.schema import DDLElement, PrimaryKeyConstraint


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


@compiler.compiles(CreateView)
def compile_create_materialized_view(element, compiler, **kw):
    return 'CREATE {}VIEW {} AS {}'.format(
        'MATERIALIZED ' if element.materialized else '',
        element.name,
        compiler.sql_compiler.process(element.selectable, literal_binds=True),
    )


class DropView(DDLElement):
    def __init__(self, name, materialized=False, cascade=True):
        self.name = name
        self.materialized = materialized
        self.cascade = cascade


@compiler.compiles(DropView)
def compile_drop_materialized_view(element, compiler, **kw):
    return 'DROP {}VIEW IF EXISTS {} {}'.format(
        'MATERIALIZED ' if element.materialized else '',
        element.name,
        'CASCADE' if element.cascade else ''
    )


def create_table_from_selectable(
    name,
    selectable,
    indexes=None,
    metadata=None,
    aliases=None
):
    if indexes is None:
        indexes = []


## ... source file continues with no further DDLElement examples...

Sponsored By

Sentry logo

Software errors are inevitable. Chaos is not. Try Sentry for free.


AssemblyAI logo

The most accurate speech-to-text API. Built for Python developers.

1. Introduction 2. Development Environments 3. Data 4. Web Development 5. Deployment 6. DevOps Changelog What Full Stack Means About the Author Future Directions Page Statuses SQLAlchemy ExtensionsSQLAlchemy Example CodeSQLAlchemy Modelssqlalchemy.databases mysqlsqlalchemy.dialects mssqlsqlalchemy.dialects mysqlsqlalchemy.dialects oraclesqlalchemy.schema DDLsqlalchemy.dialects postgresqlsqlalchemy.sql.expression Functionsqlalchemy.dialects sqlitesqlalchemy.dialects.mysql pymysqlsqlalchemy.dialects.postgresql ARRAYsqlalchemy.dialects.postgresql BIGINTsqlalchemy.dialects.postgresql BITsqlalchemy.dialects.postgresql DOUBLE_PRECISIONsqlalchemy.dialects.postgresql ExcludeConstraintsqlalchemy.dialects.postgresql INTEGERsqlalchemy.dialects.postgresql JSONsqlalchemy.dialects.postgresql TSVECTORsqlalchemy.dialects.postgresql pypostgresqlsqlalchemy.dialects.postgresql.base PGCompilersqlalchemy.dialects.postgresql.base PGIdentifierPreparersqlalchemy.dialects.postgresql.base PGTypeCompilersqlalchemy.dialects.postgresql.psycopg2 PGDialect_psycopg2sqlalchemy.dialects.sqlite pysqlitesqlalchemy.engine Connectionsqlalchemy.engine Enginesqlalchemy.engine create_enginesqlalchemy.engine defaultsqlalchemy.engine urlsqlalchemy.engine.default DefaultDialectsqlalchemy.engine.interfaces ExecutionContextsqlalchemy.engine.result ResultMetaDatasqlalchemy.engine.result RowProxysqlalchemy.engine.strategies EngineStrategysqlalchemy.engine.strategies MockEngineStrategysqlalchemy.engine.url make_urlsqlalchemy.events SchemaEventTargetsqlalchemy.exc ArgumentErrorsqlalchemy.exc DataErrorsqlalchemy.exc DatabaseErrorsqlalchemy.exc IntegrityErrorsqlalchemy.exc InvalidRequestErrorsqlalchemy.exc NoInspectionAvailablesqlalchemy.exc NoSuchTableErrorsqlalchemy.exc OperationalErrorsqlalchemy.exc ProgrammingErrorsqlalchemy.exc UnsupportedCompilationErrorsqlalchemy.ext compilersqlalchemy.ext.associationproxy AssociationProxysqlalchemy.ext.automap automap_basesqlalchemy.ext.compiler compilessqlalchemy.ext.declarative DeclarativeMetasqlalchemy.ext.declarative declarative_basesqlalchemy.ext.hybrid hybrid_methodsqlalchemy.ext.hybrid hybrid_propertysqlalchemy.ext.mutable Mutablesqlalchemy.inspection inspectsqlalchemy.orm ColumnPropertysqlalchemy.orm CompositePropertysqlalchemy.orm Loadsqlalchemy.orm Querysqlalchemy.orm RelationshipPropertysqlalchemy.orm SynonymPropertysqlalchemy.orm aliasedsqlalchemy.orm attributessqlalchemy.orm backrefsqlalchemy.orm class_mappersqlalchemy.orm column_propertysqlalchemy.orm compositesqlalchemy.orm interfacessqlalchemy.orm mappersqlalchemy.orm mapperlibsqlalchemy.orm object_mappersqlalchemy.orm object_sessionsqlalchemy.orm relationshipsqlalchemy.orm sessionsqlalchemy.orm sessionmakersqlalchemy.orm strategiessqlalchemy.orm.attributes InstrumentedAttributesqlalchemy.orm.attributes QueryableAttributesqlalchemy.orm.attributes flag_modifiedsqlalchemy.orm.collections InstrumentedListsqlalchemy.orm.exc NoResultFoundsqlalchemy.orm.exc UnmappedClassErrorsqlalchemy.orm.exc UnmappedInstanceErrorsqlalchemy.orm.interfaces MapperPropertysqlalchemy.orm.interfaces PropComparatorsqlalchemy.orm.mapper Mappersqlalchemy.orm.properties ColumnPropertysqlalchemy.orm.properties RelationshipPropertysqlalchemy.orm.query Querysqlalchemy.orm.query QueryContextsqlalchemy.orm.relationships RelationshipPropertysqlalchemy.orm.session Sessionsqlalchemy.orm.session object_sessionsqlalchemy.orm.util AliasedClasssqlalchemy.orm.util AliasedInspsqlalchemy.orm.util identity_keysqlalchemy.pool NullPoolsqlalchemy.pool StaticPoolsqlalchemy.schema CheckConstraintsqlalchemy.schema Columnsqlalchemy.schema CreateIndexsqlalchemy.schema CreateTablesqlalchemy.schema DDLElementsqlalchemy.schema ForeignKeysqlalchemy.schema ForeignKeyConstraintsqlalchemy.schema Indexsqlalchemy.schema PrimaryKeyConstraintsqlalchemy.schema Tablesqlalchemy.sql ClauseElementsqlalchemy.sql columnsqlalchemy.sql expressionsqlalchemy.sql extractsqlalchemy.sql functionssqlalchemy.sql operatorssqlalchemy.sql schemasqlalchemy.sql selectsqlalchemy.sql sqltypessqlalchemy.sql tablesqlalchemy.sql.compiler SQLCompilersqlalchemy.sql.elements ColumnElementsqlalchemy.sql.elements Labelsqlalchemy.sql.expression ClauseElementsqlalchemy.sql.expression ColumnClausesqlalchemy.sql.expression ColumnElementsqlalchemy.sql.expression Executablesqlalchemy.sql.expression FunctionElementsqlalchemy.sql.expression UnaryExpressionsqlalchemy.sql.functions FunctionElementsqlalchemy.sql.functions GenericFunctionsqlalchemy.sql.naming convsqlalchemy.sql.schema Columnsqlalchemy.sql.schema SchemaItemsqlalchemy.sql.sqltypes NullTypesqlalchemy.sql.util ClauseAdaptersqlalchemy.sql.visitors traversesqlalchemy.types Booleansqlalchemy.types Datesqlalchemy.types DateTimesqlalchemy.types Enumsqlalchemy.types Floatsqlalchemy.types Integersqlalchemy.types Intervalsqlalchemy.types NULLTYPEsqlalchemy.types Stringsqlalchemy.types Textsqlalchemy.types Timesqlalchemy.types TypeEnginesqlalchemy.types UserDefinedTypesqlalchemy.types to_instancesqlalchemy.util OrderedDictsqlalchemy.util OrderedSetsqlalchemy.util set_creation_ordersqlalchemy.util symbolsqlalchemy.util topologicalsqlalchemy.util.langhelpers public_factorysqlalchemy.util.langhelpers symbol ...or view the full table of contents.

Full Stack Python

Full Stack Python is an open book that explains concepts in plain language and provides helpful resources for those topics.
Updates via Twitter & Facebook.

Matt Makai 2012-2020