Python sqlalchemy.DDL Examples

The following are 30 code examples of sqlalchemy.DDL(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module sqlalchemy , or try the search function .
Example #1
Source File: ddl.py    From pyRevit with GNU General Public License v3.0 6 votes vote down vote up
def __init__(
            self, element, on=None, bind=None,
            include_foreign_key_constraints=None):
        """Create a :class:`.CreateTable` construct.

        :param element: a :class:`.Table` that's the subject
         of the CREATE
        :param on: See the description for 'on' in :class:`.DDL`.
        :param bind: See the description for 'bind' in :class:`.DDL`.
        :param include_foreign_key_constraints: optional sequence of
         :class:`.ForeignKeyConstraint` objects that will be included
         inline within the CREATE construct; if omitted, all foreign key
         constraints that do not specify use_alter=True are included.

         .. versionadded:: 1.0.0

        """
        super(CreateTable, self).__init__(element, on=on, bind=bind)
        self.columns = [CreateColumn(column)
                        for column in element.columns
                        ]
        self.include_foreign_key_constraints = include_foreign_key_constraints 
Example #2
Source File: ddl.py    From planespotter with MIT License 6 votes vote down vote up
def __init__(
            self, element, on=None, bind=None,
            include_foreign_key_constraints=None):
        """Create a :class:`.CreateTable` construct.

        :param element: a :class:`.Table` that's the subject
         of the CREATE
        :param on: See the description for 'on' in :class:`.DDL`.
        :param bind: See the description for 'bind' in :class:`.DDL`.
        :param include_foreign_key_constraints: optional sequence of
         :class:`.ForeignKeyConstraint` objects that will be included
         inline within the CREATE construct; if omitted, all foreign key
         constraints that do not specify use_alter=True are included.

         .. versionadded:: 1.0.0

        """
        super(CreateTable, self).__init__(element, on=on, bind=bind)
        self.columns = [CreateColumn(column)
                        for column in element.columns
                        ]
        self.include_foreign_key_constraints = include_foreign_key_constraints 
Example #3
Source File: ddl.py    From jarvis with GNU General Public License v2.0 6 votes vote down vote up
def __init__(
            self, element, on=None, bind=None,
            include_foreign_key_constraints=None):
        """Create a :class:`.CreateTable` construct.

        :param element: a :class:`.Table` that's the subject
         of the CREATE
        :param on: See the description for 'on' in :class:`.DDL`.
        :param bind: See the description for 'bind' in :class:`.DDL`.
        :param include_foreign_key_constraints: optional sequence of
         :class:`.ForeignKeyConstraint` objects that will be included
         inline within the CREATE construct; if omitted, all foreign key
         constraints that do not specify use_alter=True are included.

         .. versionadded:: 1.0.0

        """
        super(CreateTable, self).__init__(element, on=on, bind=bind)
        self.columns = [CreateColumn(column)
                        for column in element.columns
                        ]
        self.include_foreign_key_constraints = include_foreign_key_constraints 
Example #4
Source File: answer.py    From dokomoforms with GNU General Public License v3.0 6 votes vote down vote up
def add_new_photo_to_session(session, *, id, **kwargs):
    """Create a new Photo and update the referenced PhotoAnswer."""
    try:
        answer = (
            session
            .query(PhotoAnswer)
            .filter_by(main_answer=id)
            .one()
        )
    except NoResultFound:
        raise PhotoIdDoesNotExistError(id)
    with session.begin():
        answer.photo = Photo(id=id, **kwargs)
        answer.actual_photo_id = answer.main_answer
    return answer.photo


# sa.event.listen(
#     Photo.__table__,
#     'after_create',
#     sa.DDL(
#         'CREATE TRIGGER t_image BEFORE UPDATE OR DELETE ON photo'
#         ' FOR EACH ROW EXECUTE PROCEDURE lo_manage(image)'
#     ),
# ) 
Example #5
Source File: ddl.py    From Fluid-Designer with GNU General Public License v3.0 6 votes vote down vote up
def __init__(
            self, element, on=None, bind=None,
            include_foreign_key_constraints=None):
        """Create a :class:`.CreateTable` construct.

        :param element: a :class:`.Table` that's the subject
         of the CREATE
        :param on: See the description for 'on' in :class:`.DDL`.
        :param bind: See the description for 'bind' in :class:`.DDL`.
        :param include_foreign_key_constraints: optional sequence of
         :class:`.ForeignKeyConstraint` objects that will be included
         inline within the CREATE construct; if omitted, all foreign key
         constraints that do not specify use_alter=True are included.

         .. versionadded:: 1.0.0

        """
        super(CreateTable, self).__init__(element, on=on, bind=bind)
        self.columns = [CreateColumn(column)
                        for column in element.columns
                        ]
        self.include_foreign_key_constraints = include_foreign_key_constraints 
Example #6
Source File: test_reflection.py    From sqlalchemy with MIT License 6 votes vote down vote up
def define_tables(cls, metadata):
        # the actual function isn't reflected yet
        dv = Table(
            "data_values",
            metadata,
            Column("modulus", Integer, nullable=False),
            Column("data", String(30)),
            Column("q", Integer),
            postgresql_partition_by="range(modulus)",
        )

        # looks like this is reflected prior to #4237
        sa.event.listen(
            dv,
            "after_create",
            sa.DDL(
                "CREATE TABLE data_values_4_10 PARTITION OF data_values "
                "FOR VALUES FROM (4) TO (10)"
            ),
        )

        if testing.against("postgresql >= 11"):
            Index("my_index", dv.c.q) 
Example #7
Source File: ddl.py    From sqlalchemy with MIT License 6 votes vote down vote up
def __init__(
        self, element, bind=None, include_foreign_key_constraints=None
    ):
        """Create a :class:`.CreateTable` construct.

        :param element: a :class:`_schema.Table` that's the subject
         of the CREATE
        :param on: See the description for 'on' in :class:`.DDL`.
        :param bind: See the description for 'bind' in :class:`.DDL`.
        :param include_foreign_key_constraints: optional sequence of
         :class:`_schema.ForeignKeyConstraint` objects that will be included
         inline within the CREATE construct; if omitted, all foreign key
         constraints that do not specify use_alter=True are included.

         .. versionadded:: 1.0.0

        """
        super(CreateTable, self).__init__(element, bind=bind)
        self.columns = [CreateColumn(column) for column in element.columns]
        self.include_foreign_key_constraints = include_foreign_key_constraints 
Example #8
Source File: 20170123151655_add_trigger_for_meta_updated.py    From collectors with MIT License 6 votes vote down vote up
def upgrade():
    conn = op.get_bind()
    func = sa.DDL("""CREATE FUNCTION set_meta_updated()
                      RETURNS TRIGGER
                      LANGUAGE plpgsql
                    AS $$
                    BEGIN
                      NEW.meta_updated := now();
                      RETURN NEW;
                    END;
                    $$;""")
    conn.execute(func)

    for table in updatable_tables:
        trigger_params = {'trigger': ('%s_set_meta_updated' % table), 'table': table}
        trigger = ("""CREATE TRIGGER %(trigger)s
                    BEFORE UPDATE ON %(table)s
                    FOR EACH ROW EXECUTE PROCEDURE set_meta_updated();""" % trigger_params)
        conn.execute(trigger) 
Example #9
Source File: ddl.py    From jbox with MIT License 6 votes vote down vote up
def __init__(
            self, element, on=None, bind=None,
            include_foreign_key_constraints=None):
        """Create a :class:`.CreateTable` construct.

        :param element: a :class:`.Table` that's the subject
         of the CREATE
        :param on: See the description for 'on' in :class:`.DDL`.
        :param bind: See the description for 'bind' in :class:`.DDL`.
        :param include_foreign_key_constraints: optional sequence of
         :class:`.ForeignKeyConstraint` objects that will be included
         inline within the CREATE construct; if omitted, all foreign key
         constraints that do not specify use_alter=True are included.

         .. versionadded:: 1.0.0

        """
        super(CreateTable, self).__init__(element, on=on, bind=bind)
        self.columns = [CreateColumn(column)
                        for column in element.columns
                        ]
        self.include_foreign_key_constraints = include_foreign_key_constraints 
Example #10
Source File: ddl.py    From android_universal with MIT License 6 votes vote down vote up
def __init__(
            self, element, on=None, bind=None,
            include_foreign_key_constraints=None):
        """Create a :class:`.CreateTable` construct.

        :param element: a :class:`.Table` that's the subject
         of the CREATE
        :param on: See the description for 'on' in :class:`.DDL`.
        :param bind: See the description for 'bind' in :class:`.DDL`.
        :param include_foreign_key_constraints: optional sequence of
         :class:`.ForeignKeyConstraint` objects that will be included
         inline within the CREATE construct; if omitted, all foreign key
         constraints that do not specify use_alter=True are included.

         .. versionadded:: 1.0.0

        """
        super(CreateTable, self).__init__(element, on=on, bind=bind)
        self.columns = [CreateColumn(column)
                        for column in element.columns
                        ]
        self.include_foreign_key_constraints = include_foreign_key_constraints 
Example #11
Source File: ddl.py    From sqlalchemy with MIT License 5 votes vote down vote up
def __init__(self, statement, context=None, bind=None):
        """Create a DDL statement.

        :param statement:
          A string or unicode string to be executed.  Statements will be
          processed with Python's string formatting operator.  See the
          ``context`` argument and the ``execute_at`` method.

          A literal '%' in a statement must be escaped as '%%'.

          SQL bind parameters are not available in DDL statements.

        :param context:
          Optional dictionary, defaults to None.  These values will be
          available for use in string substitutions on the DDL statement.

        :param bind:
          Optional. A :class:`.Connectable`, used by
          default when ``execute()`` is invoked without a bind argument.


        .. seealso::

            :class:`.DDLEvents`

            :ref:`event_toplevel`

        """

        if not isinstance(statement, util.string_types):
            raise exc.ArgumentError(
                "Expected a string or unicode SQL statement, got '%r'"
                % statement
            )

        self.statement = statement
        self.context = context or {}

        self._bind = bind 
Example #12
Source File: ddl.py    From sqlalchemy with MIT License 5 votes vote down vote up
def against(self, target):
        """Return a copy of this DDL against a specific schema item."""

        self.target = target 
Example #13
Source File: test_reflection.py    From sqlalchemy with MIT License 5 votes vote down vote up
def define_tables(cls, metadata):
        testtable = Table(
            "testtable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String(30)),
        )

        # insert data before we create the view
        @sa.event.listens_for(testtable, "after_create")
        def insert_data(target, connection, **kw):
            connection.execute(target.insert(), {"id": 89, "data": "d1"})

        materialized_view = sa.DDL(
            "CREATE MATERIALIZED VIEW test_mview AS " "SELECT * FROM testtable"
        )

        plain_view = sa.DDL(
            "CREATE VIEW test_regview AS " "SELECT * FROM testtable"
        )

        sa.event.listen(testtable, "after_create", plain_view)
        sa.event.listen(testtable, "after_create", materialized_view)
        sa.event.listen(
            testtable,
            "before_drop",
            sa.DDL("DROP MATERIALIZED VIEW test_mview"),
        )
        sa.event.listen(
            testtable, "before_drop", sa.DDL("DROP VIEW test_regview")
        ) 
Example #14
Source File: test_reflection.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_time_types(self):
        specs = []

        if testing.requires.mysql_fsp.enabled:
            fsps = [None, 0, 5]
        else:
            fsps = [None]

        for type_ in (mysql.TIMESTAMP, mysql.DATETIME, mysql.TIME):
            # MySQL defaults fsp to 0, and if 0 does not report it.
            # we don't actually render 0 right now in DDL but even if we do,
            # it comes back blank
            for fsp in fsps:
                if fsp:
                    specs.append((type_(fsp=fsp), type_(fsp=fsp)))
                else:
                    specs.append((type_(), type_()))

        specs.extend(
            [(TIMESTAMP(), mysql.TIMESTAMP()), (DateTime(), mysql.DATETIME())]
        )

        # note 'timezone' should always be None on both
        self._run_test(specs, ["fsp", "timezone"]) 
Example #15
Source File: ddl.py    From jarvis with GNU General Public License v2.0 5 votes vote down vote up
def execute(self, bind=None, target=None):
        """Execute this DDL immediately.

        Executes the DDL statement in isolation using the supplied
        :class:`.Connectable` or
        :class:`.Connectable` assigned to the ``.bind``
        property, if not supplied. If the DDL has a conditional ``on``
        criteria, it will be invoked with None as the event.

        :param bind:
          Optional, an ``Engine`` or ``Connection``. If not supplied, a valid
          :class:`.Connectable` must be present in the
          ``.bind`` property.

        :param target:
          Optional, defaults to None.  The target SchemaItem for the
          execute call.  Will be passed to the ``on`` callable if any,
          and may also provide string expansion data for the
          statement. See ``execute_at`` for more information.

        """

        if bind is None:
            bind = _bind_or_error(self)

        if self._should_execute(target, bind):
            return bind.execute(self.against(target))
        else:
            bind.engine.logger.info(
                "DDL execution skipped, criteria not met.") 
Example #16
Source File: ddl.py    From sqlalchemy with MIT License 5 votes vote down vote up
def __call__(self, target, bind, **kw):
        """Execute the DDL as a ddl_listener."""

        if self._should_execute(target, bind, **kw):
            return bind.execute(self.against(target)) 
Example #17
Source File: ddl.py    From jarvis with GNU General Public License v2.0 5 votes vote down vote up
def execute_at(self, event_name, target):
        """Link execution of this DDL to the DDL lifecycle of a SchemaItem.

        Links this ``DDLElement`` to a ``Table`` or ``MetaData`` instance,
        executing it when that schema item is created or dropped. The DDL
        statement will be executed using the same Connection and transactional
        context as the Table create/drop itself. The ``.bind`` property of
        this statement is ignored.

        :param event:
          One of the events defined in the schema item's ``.ddl_events``;
          e.g. 'before-create', 'after-create', 'before-drop' or 'after-drop'

        :param target:
          The Table or MetaData instance for which this DDLElement will
          be associated with.

        A DDLElement instance can be linked to any number of schema items.

        ``execute_at`` builds on the ``append_ddl_listener`` interface of
        :class:`.MetaData` and :class:`.Table` objects.

        Caveat: Creating or dropping a Table in isolation will also trigger
        any DDL set to ``execute_at`` that Table's MetaData.  This may change
        in a future release.

        """

        def call_event(target, connection, **kw):
            if self._should_execute_deprecated(event_name,
                                               target, connection, **kw):
                return connection.execute(self.against(target))

        event.listen(target, "" + event_name.replace('-', '_'), call_event) 
Example #18
Source File: ddl.py    From stdm with GNU General Public License v2.0 5 votes vote down vote up
def __init__(self, element, on=None, bind=None):
        """Create a :class:`.CreateTable` construct.

        :param element: a :class:`.Table` that's the subject
         of the CREATE
        :param on: See the description for 'on' in :class:`.DDL`.
        :param bind: See the description for 'bind' in :class:`.DDL`.

        """
        super(CreateTable, self).__init__(element, on=on, bind=bind)
        self.columns = [CreateColumn(column)
                        for column in element.columns
                        ] 
Example #19
Source File: ddl.py    From jarvis with GNU General Public License v2.0 5 votes vote down vote up
def against(self, target):
        """Return a copy of this DDL against a specific schema item."""

        self.target = target 
Example #20
Source File: ddl.py    From jarvis with GNU General Public License v2.0 5 votes vote down vote up
def __call__(self, target, bind, **kw):
        """Execute the DDL as a ddl_listener."""

        if self._should_execute(target, bind, **kw):
            return bind.execute(self.against(target)) 
Example #21
Source File: db.py    From koschei with GNU General Public License v2.0 5 votes vote down vote up
def load_ddl():
    for script in ('triggers.sql', 'rpmvercmp.sql'):
        with open(os.path.join(get_config('directories.datadir'), script)) as ddl_script:
            ddl = DDL(ddl_script.read())
        listen(Base.metadata, 'after_create', ddl.execute_if(dialect='postgresql')) 
Example #22
Source File: ddl.py    From moviegrabber with GNU General Public License v3.0 5 votes vote down vote up
def execute(self, bind=None, target=None):
        """Execute this DDL immediately.

        Executes the DDL statement in isolation using the supplied
        :class:`.Connectable` or
        :class:`.Connectable` assigned to the ``.bind``
        property, if not supplied. If the DDL has a conditional ``on``
        criteria, it will be invoked with None as the event.

        :param bind:
          Optional, an ``Engine`` or ``Connection``. If not supplied, a valid
          :class:`.Connectable` must be present in the
          ``.bind`` property.

        :param target:
          Optional, defaults to None.  The target SchemaItem for the
          execute call.  Will be passed to the ``on`` callable if any,
          and may also provide string expansion data for the
          statement. See ``execute_at`` for more information.

        """

        if bind is None:
            bind = _bind_or_error(self)

        if self._should_execute(target, bind):
            return bind.execute(self.against(target))
        else:
            bind.engine.logger.info(
                        "DDL execution skipped, criteria not met.") 
Example #23
Source File: ddl.py    From moviegrabber with GNU General Public License v3.0 5 votes vote down vote up
def execute_at(self, event_name, target):
        """Link execution of this DDL to the DDL lifecycle of a SchemaItem.

        Links this ``DDLElement`` to a ``Table`` or ``MetaData`` instance,
        executing it when that schema item is created or dropped. The DDL
        statement will be executed using the same Connection and transactional
        context as the Table create/drop itself. The ``.bind`` property of
        this statement is ignored.

        :param event:
          One of the events defined in the schema item's ``.ddl_events``;
          e.g. 'before-create', 'after-create', 'before-drop' or 'after-drop'

        :param target:
          The Table or MetaData instance for which this DDLElement will
          be associated with.

        A DDLElement instance can be linked to any number of schema items.

        ``execute_at`` builds on the ``append_ddl_listener`` interface of
        :class:`.MetaData` and :class:`.Table` objects.

        Caveat: Creating or dropping a Table in isolation will also trigger
        any DDL set to ``execute_at`` that Table's MetaData.  This may change
        in a future release.

        """

        def call_event(target, connection, **kw):
            if self._should_execute_deprecated(event_name,
                                    target, connection, **kw):
                return connection.execute(self.against(target))

        event.listen(target, "" + event_name.replace('-', '_'), call_event) 
Example #24
Source File: ddl.py    From moviegrabber with GNU General Public License v3.0 5 votes vote down vote up
def against(self, target):
        """Return a copy of this DDL against a specific schema item."""

        self.target = target 
Example #25
Source File: ddl.py    From moviegrabber with GNU General Public License v3.0 5 votes vote down vote up
def __call__(self, target, bind, **kw):
        """Execute the DDL as a ddl_listener."""

        if self._should_execute(target, bind, **kw):
            return bind.execute(self.against(target)) 
Example #26
Source File: ddl.py    From moviegrabber with GNU General Public License v3.0 5 votes vote down vote up
def __init__(self, element, on=None, bind=None):
        """Create a :class:`.CreateTable` construct.

        :param element: a :class:`.Table` that's the subject
         of the CREATE
        :param on: See the description for 'on' in :class:`.DDL`.
        :param bind: See the description for 'bind' in :class:`.DDL`.

        """
        super(CreateTable, self).__init__(element, on=on, bind=bind)
        self.columns = [CreateColumn(column)
            for column in element.columns
        ] 
Example #27
Source File: ddl.py    From android_universal with MIT License 5 votes vote down vote up
def execute(self, bind=None, target=None):
        """Execute this DDL immediately.

        Executes the DDL statement in isolation using the supplied
        :class:`.Connectable` or
        :class:`.Connectable` assigned to the ``.bind``
        property, if not supplied. If the DDL has a conditional ``on``
        criteria, it will be invoked with None as the event.

        :param bind:
          Optional, an ``Engine`` or ``Connection``. If not supplied, a valid
          :class:`.Connectable` must be present in the
          ``.bind`` property.

        :param target:
          Optional, defaults to None.  The target SchemaItem for the
          execute call.  Will be passed to the ``on`` callable if any,
          and may also provide string expansion data for the
          statement. See ``execute_at`` for more information.

        """

        if bind is None:
            bind = _bind_or_error(self)

        if self._should_execute(target, bind):
            return bind.execute(self.against(target))
        else:
            bind.engine.logger.info(
                "DDL execution skipped, criteria not met.") 
Example #28
Source File: ddl.py    From android_universal with MIT License 5 votes vote down vote up
def execute_at(self, event_name, target):
        """Link execution of this DDL to the DDL lifecycle of a SchemaItem.

        Links this ``DDLElement`` to a ``Table`` or ``MetaData`` instance,
        executing it when that schema item is created or dropped. The DDL
        statement will be executed using the same Connection and transactional
        context as the Table create/drop itself. The ``.bind`` property of
        this statement is ignored.

        :param event:
          One of the events defined in the schema item's ``.ddl_events``;
          e.g. 'before-create', 'after-create', 'before-drop' or 'after-drop'

        :param target:
          The Table or MetaData instance for which this DDLElement will
          be associated with.

        A DDLElement instance can be linked to any number of schema items.

        ``execute_at`` builds on the ``append_ddl_listener`` interface of
        :class:`.MetaData` and :class:`.Table` objects.

        Caveat: Creating or dropping a Table in isolation will also trigger
        any DDL set to ``execute_at`` that Table's MetaData.  This may change
        in a future release.

        """

        def call_event(target, connection, **kw):
            if self._should_execute_deprecated(event_name,
                                               target, connection, **kw):
                return connection.execute(self.against(target))

        event.listen(target, "" + event_name.replace('-', '_'), call_event) 
Example #29
Source File: ddl.py    From android_universal with MIT License 5 votes vote down vote up
def against(self, target):
        """Return a copy of this DDL against a specific schema item."""

        self.target = target 
Example #30
Source File: ddl.py    From android_universal with MIT License 5 votes vote down vote up
def __call__(self, target, bind, **kw):
        """Execute the DDL as a ddl_listener."""

        if self._should_execute(target, bind, **kw):
            return bind.execute(self.against(target))