Python sqlalchemy.schema.DropConstraint() Examples

def drop_constraint(self, const):
        self._exec(schema.DropConstraint(const)) 

def drop_constraint(self, const):
        self._exec(schema.DropConstraint(const)) 

def drop_all_tables(engine, inspector, schema=None, include_names=None):
    from sqlalchemy import Column, Table, Integer, MetaData, \
        ForeignKeyConstraint
    from sqlalchemy.schema import DropTable, DropConstraint

    if include_names is not None:
        include_names = set(include_names)

    with engine.connect() as conn:
        for tname, fkcs in reversed(
                inspector.get_sorted_table_and_fkc_names(schema=schema)):
            if tname:
                if include_names is not None and tname not in include_names:
                    continue
                conn.execute(DropTable(
                    Table(tname, MetaData(), schema=schema)
                ))
            elif fkcs:
                if not engine.dialect.supports_alter:
                    continue
                for tname, fkc in fkcs:
                    if include_names is not None and \
                            tname not in include_names:
                        continue
                    tb = Table(
                        tname, MetaData(),
                        Column('x', Integer),
                        Column('y', Integer),
                        schema=schema
                    )
                    conn.execute(DropConstraint(
                        ForeignKeyConstraint(
                            [tb.c.x], [tb.c.y], name=fkc)
                    )) 

def drop_all_tables(engine, inspector, schema=None, include_names=None):
    from sqlalchemy import Column, Table, Integer, MetaData, \
        ForeignKeyConstraint
    from sqlalchemy.schema import DropTable, DropConstraint

    if include_names is not None:
        include_names = set(include_names)

    with engine.connect() as conn:
        for tname, fkcs in reversed(
                inspector.get_sorted_table_and_fkc_names(schema=schema)):
            if tname:
                if include_names is not None and tname not in include_names:
                    continue
                conn.execute(DropTable(
                    Table(tname, MetaData(), schema=schema)
                ))
            elif fkcs:
                if not engine.dialect.supports_alter:
                    continue
                for tname, fkc in fkcs:
                    if include_names is not None and \
                            tname not in include_names:
                        continue
                    tb = Table(
                        tname, MetaData(),
                        Column('x', Integer),
                        Column('y', Integer),
                        schema=schema
                    )
                    conn.execute(DropConstraint(
                        ForeignKeyConstraint(
                            [tb.c.x], [tb.c.y], name=fkc)
                    )) 

def test_render_drop_constraint_cascade(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint(
            "a < b",
            name="my_test_constraint",
            deferrable=True,
            initially="DEFERRED",
            table=t,
        )

        self.assert_compile(
            schema.DropConstraint(constraint, cascade=True),
            "ALTER TABLE tbl DROP CONSTRAINT my_test_constraint CASCADE",
        ) 

def test_render_drop_constraint(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint(
            "a < b",
            name="my_test_constraint",
            deferrable=True,
            initially="DEFERRED",
            table=t,
        )

        self.assert_compile(
            schema.DropConstraint(constraint),
            "ALTER TABLE tbl DROP CONSTRAINT my_test_constraint",
        ) 

def _assert_drop_w_alter(self, elements, generator, argument):
        self._assert_ddl(
            (schema.DropTable, schema.DropSequence, schema.DropConstraint),
            elements,
            generator,
            argument,
        ) 

def test_drop_constraint_mariadb(self):
        m = MetaData()
        table_name = "testtbl"
        constraint_name = "constraint"
        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
        Table(table_name, m, Column("data", String(255)), constraint)
        dialect = mysql.dialect()
        dialect.server_version_info = (10, 1, 1, "MariaDB")
        self.assert_compile(
            schema.DropConstraint(constraint),
            "ALTER TABLE %s DROP CONSTRAINT `%s`"
            % (table_name, constraint_name),
            dialect=dialect,
        ) 

def test_drop_constraint_mysql(self):
        m = MetaData()
        table_name = "testtbl"
        constraint_name = "constraint"
        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
        Table(table_name, m, Column("data", String(255)), constraint)
        dialect = mysql.dialect()
        self.assert_compile(
            schema.DropConstraint(constraint),
            "ALTER TABLE %s DROP CHECK `%s`" % (table_name, constraint_name),
            dialect=dialect,
        ) 

def drop_all_objects(self, engine):
        """Drop all database objects.

        Drops all database objects remaining on the default schema of the
        given engine.

        Per-db implementations will also need to drop items specific to those
        systems, such as sequences, custom types (e.g. pg ENUM), etc.

        """

        with engine.begin() as conn:
            inspector = sqlalchemy.inspect(engine)
            metadata = schema.MetaData()
            tbs = []
            all_fks = []

            for table_name in inspector.get_table_names():
                fks = []
                for fk in inspector.get_foreign_keys(table_name):
                    # note that SQLite reflection does not have names
                    # for foreign keys until SQLAlchemy 1.0
                    if not fk['name']:
                        continue
                    fks.append(
                        schema.ForeignKeyConstraint((), (), name=fk['name'])
                        )
                table = schema.Table(table_name, metadata, *fks)
                tbs.append(table)
                all_fks.extend(fks)

            if self.supports_drop_fk:
                for fkc in all_fks:
                    conn.execute(schema.DropConstraint(fkc))

            for table in tbs:
                conn.execute(schema.DropTable(table))

            self.drop_additional_objects(conn) 

def _visit_constraint(self, constraint):
        constraint.name = self.get_constraint_name(constraint)
        self.append(self.process(DropConstraint(constraint, cascade=constraint.cascade)))
        self.execute() 

def drop_all_tables(engine, inspector, schema=None, include_names=None):
    from sqlalchemy import Column, Table, Integer, MetaData, \
        ForeignKeyConstraint
    from sqlalchemy.schema import DropTable, DropConstraint

    if include_names is not None:
        include_names = set(include_names)

    with engine.connect() as conn:
        for tname, fkcs in reversed(
                inspector.get_sorted_table_and_fkc_names(schema=schema)):
            if tname:
                if include_names is not None and tname not in include_names:
                    continue
                conn.execute(DropTable(
                    Table(tname, MetaData(), schema=schema)
                ))
            elif fkcs:
                if not engine.dialect.supports_alter:
                    continue
                for tname, fkc in fkcs:
                    if include_names is not None and \
                            tname not in include_names:
                        continue
                    tb = Table(
                        tname, MetaData(),
                        Column('x', Integer),
                        Column('y', Integer),
                        schema=schema
                    )
                    conn.execute(DropConstraint(
                        ForeignKeyConstraint(
                            [tb.c.x], [tb.c.y], name=fkc)
                    )) 

def drop_all_tables(engine, inspector, schema=None, include_names=None):
    from sqlalchemy import Column, Table, Integer, MetaData, \
        ForeignKeyConstraint
    from sqlalchemy.schema import DropTable, DropConstraint

    if include_names is not None:
        include_names = set(include_names)

    with engine.connect() as conn:
        for tname, fkcs in reversed(
                inspector.get_sorted_table_and_fkc_names(schema=schema)):
            if tname:
                if include_names is not None and tname not in include_names:
                    continue
                conn.execute(DropTable(
                    Table(tname, MetaData(), schema=schema)
                ))
            elif fkcs:
                if not engine.dialect.supports_alter:
                    continue
                for tname, fkc in fkcs:
                    if include_names is not None and \
                            tname not in include_names:
                        continue
                    tb = Table(
                        tname, MetaData(),
                        Column('x', Integer),
                        Column('y', Integer),
                        schema=schema
                    )
                    conn.execute(DropConstraint(
                        ForeignKeyConstraint(
                            [tb.c.x], [tb.c.y], name=fkc)
                    )) 

def drop_all_tables(engine, inspector, schema=None, include_names=None):
    from sqlalchemy import Column, Table, Integer, MetaData, \
        ForeignKeyConstraint
    from sqlalchemy.schema import DropTable, DropConstraint

    if include_names is not None:
        include_names = set(include_names)

    with engine.connect() as conn:
        for tname, fkcs in reversed(
                inspector.get_sorted_table_and_fkc_names(schema=schema)):
            if tname:
                if include_names is not None and tname not in include_names:
                    continue
                conn.execute(DropTable(
                    Table(tname, MetaData(), schema=schema)
                ))
            elif fkcs:
                if not engine.dialect.supports_alter:
                    continue
                for tname, fkc in fkcs:
                    if include_names is not None and \
                            tname not in include_names:
                        continue
                    tb = Table(
                        tname, MetaData(),
                        Column('x', Integer),
                        Column('y', Integer),
                        schema=schema
                    )
                    conn.execute(DropConstraint(
                        ForeignKeyConstraint(
                            [tb.c.x], [tb.c.y], name=fkc)
                    )) 

def drop_constraint(self, const):
        self._exec(schema.DropConstraint(const)) 

def drop_all_tables(engine, inspector, schema=None, include_names=None):
    from sqlalchemy import Column, Table, Integer, MetaData, \
        ForeignKeyConstraint
    from sqlalchemy.schema import DropTable, DropConstraint

    if include_names is not None:
        include_names = set(include_names)

    with engine.connect() as conn:
        for tname, fkcs in reversed(
                inspector.get_sorted_table_and_fkc_names(schema=schema)):
            if tname:
                if include_names is not None and tname not in include_names:
                    continue
                conn.execute(DropTable(
                    Table(tname, MetaData(), schema=schema)
                ))
            elif fkcs:
                if not engine.dialect.supports_alter:
                    continue
                for tname, fkc in fkcs:
                    if include_names is not None and \
                            tname not in include_names:
                        continue
                    tb = Table(
                        tname, MetaData(),
                        Column('x', Integer),
                        Column('y', Integer),
                        schema=schema
                    )
                    conn.execute(DropConstraint(
                        ForeignKeyConstraint(
                            [tb.c.x], [tb.c.y], name=fkc)
                    )) 

def __init__(self,
                 connection_type: SQLConnectionType,
                 db_path: Optional[str] = None):
        if db_path is None:
            db_path = join(data_path(), "hummingbot_trades.sqlite")

        engine_options = {
            "db_engine": global_config_map.get("db_engine").value,
            "db_host": global_config_map.get("db_host").value,
            "db_port": global_config_map.get("db_port").value,
            "db_username": global_config_map.get("db_username").value,
            "db_password": global_config_map.get("db_password").value,
            "db_name": global_config_map.get("db_name").value,
            "db_path": db_path
        }

        if connection_type is SQLConnectionType.TRADE_FILLS:
            self._engine: Engine = self.get_db_engine(
                                                engine_options.get("db_engine"),
                                                engine_options)
            self._metadata: MetaData = self.get_declarative_base().metadata
            self._metadata.create_all(self._engine)

            # SQLite does not enforce foreign key constraint, but for others engines, we need to drop it. 
            # See: `hummingbot/market/markets_recorder.py`, at line 213.
            with self._engine.begin() as conn:
                inspector = inspect(conn)

                for tname, fkcs in reversed(
                        inspector.get_sorted_table_and_fkc_names()):
                    if fkcs:
                        if not self._engine.dialect.supports_alter:
                            continue
                        for fkc in fkcs:
                            fk_constraint = ForeignKeyConstraint((), (), name=fkc)
                            Table(tname, MetaData(), fk_constraint)
                            conn.execute(DropConstraint(fk_constraint))

        self._session_cls = sessionmaker(bind=self._engine)
        self._shared_session: Session = self._session_cls()

        if connection_type is SQLConnectionType.TRADE_FILLS:
            self.check_and_upgrade_trade_fills_db() 

def drop_everything(echo=True):
    """ Pre-gather all named constraints and table names, and drop everything. This is better than using metadata.reflect();
        metadata.drop_all() as it handles cyclical constraints between tables.
        Ref. http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DropEverything
    """
    engine = session.get_engine(echo=echo)
    conn = engine.connect()

    # the transaction only applies if the DB supports
    # transactional DDL, i.e. Postgresql, MS SQL Server
    trans = conn.begin()

    inspector = reflection.Inspector.from_engine(engine)

    # gather all data first before dropping anything.
    # some DBs lock after things have been dropped in
    # a transaction.
    metadata = MetaData()

    tbs = []
    all_fks = []

    for table_name in inspector.get_table_names():
        fks = []
        for fk in inspector.get_foreign_keys(table_name):
            if not fk['name']:
                continue
            fks.append(ForeignKeyConstraint((), (), name=fk['name']))
        t = Table(table_name, metadata, *fks)
        tbs.append(t)
        all_fks.extend(fks)

    for fkc in all_fks:
        try:
            print(str(DropConstraint(fkc)) + ';')
            conn.execute(DropConstraint(fkc))
        except:
            print(format_exc())

    for table in tbs:
        try:
            print(str(DropTable(table)).strip() + ';')
            conn.execute(DropTable(table))
        except:
            print(format_exc())

    trans.commit() 

def drop_everything(engine, keep_tables=[], reset_columns={}):
    """ Drops all tables in the db unless their name is in `keep_tables`.
        `reset_columns` is used to specify the columns that should be reset to
        default value in the tables that we're keeping -
        provided as a dict of table_name: list_of_column_names.
    """

    conn = engine.connect()
    trans = conn.begin()

    inspector = reflection.Inspector.from_engine(engine)

    # gather all data first before dropping anything.
    # some DBs lock after things have been dropped in
    # a transaction.

    metadata = MetaData()

    tbs = []
    all_fks = []

    for table_name in inspector.get_table_names():
        if table_name in keep_tables:
            # Reset certain columns in certain tables we're keeping
            if table_name in reset_columns:
                t = Table(table_name, metadata)

                column_names = reset_columns[table_name]
                for c in inspector.get_columns(table_name):
                    if c['name'] in column_names:
                        assert c['default']

                        q = "UPDATE {0} SET {1}={2};".\
                            format(table_name, c['name'], c['default'])
                        conn.execute(q)
            continue

        fks = []
        for fk in inspector.get_foreign_keys(table_name):
            if not fk['name']:
                continue
            fks.append(ForeignKeyConstraint((), (), name=fk['name']))
        t = Table(table_name, metadata, *fks)
        tbs.append(t)
        all_fks.extend(fks)

    for fkc in all_fks:
        conn.execute(DropConstraint(fkc))

    for table in tbs:
        conn.execute(DropTable(table))

    trans.commit()