Python sqlalchemy.schema.DropTable() Examples

def create_table(request, sa_table, database, loop, create_entries):
    async def f(rows):
        create_expr = CreateTable(sa_table)
        async with database.acquire() as conn:
            await conn.execute(create_expr)
            values = create_entries(rows)
            query1 = sa_table.insert().values(values)
            await conn.execute(query1)
            await conn.execute('commit;')
        return sa_table

    yield f

    async def fin():
        drop_expr = DropTable(sa_table)
        async with database.acquire() as conn:
            await conn.execute(drop_expr)
            await conn.execute('commit;')

    loop.run_until_complete(fin()) 

def drop_table(self, table):
        self._exec(schema.DropTable(table)) 

def test_create_table(sa_connect):
    conn = await sa_connect()
    res = await conn.execute(DropTable(tbl))
    with pytest.raises(sa.ResourceClosedError):
        await res.fetchmany()

    with pytest.raises(aiomysql.ProgrammingError):
        await conn.execute("SELECT * FROM sa_tbl")

    res = await conn.execute(CreateTable(tbl))
    with pytest.raises(sa.ResourceClosedError):
        await res.fetchmany()

    res = await conn.execute("SELECT * FROM sa_tbl")
    assert 0 == len(await res.fetchall()) 

def test_raw_insert_with_executemany(sa_connect):
    conn = await sa_connect()
    # with pytest.raises(sa.ArgumentError):
    await conn.execute(
        "INSERT INTO sa_tbl (id, name) VALUES (%(id)s, %(name)s)",
        [{"id": 2, "name": 'third'}, {"id": 3, "name": 'forth'}])
    await conn.execute(
        tbl.update().where(
            tbl.c.id == bindparam("id")
        ).values(
            {"name": bindparam("name")}
        ),
        [
            {"id": 2, "name": "t2"},
            {"id": 3, "name": "t3"}
        ]
    )
    with pytest.raises(sa.ArgumentError):
        await conn.execute(
            DropTable(tbl),
            [{}, {}]
        )
    with pytest.raises(sa.ArgumentError):
        await conn.execute(
            {},
            [{}, {}]
        ) 

def drop_table(self, table):
        self._exec(schema.DropTable(table)) 

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 _prep_testing_database(options, file_config):
    from sqlalchemy.testing import config
    from sqlalchemy import schema, inspect

    if options.dropfirst:
        for cfg in config.Config.all_configs():
            e = cfg.db
            inspector = inspect(e)
            try:
                view_names = inspector.get_view_names()
            except NotImplementedError:
                pass
            else:
                for vname in view_names:
                    e.execute(schema._DropView(schema.Table(vname, schema.MetaData())))

            if config.requirements.schemas.enabled_for_config(cfg):
                try:
                    view_names = inspector.get_view_names(schema="test_schema")
                except NotImplementedError:
                    pass
                else:
                    for vname in view_names:
                        e.execute(schema._DropView(
                                    schema.Table(vname,
                                                schema.MetaData(), schema="test_schema")))

            for tname in reversed(inspector.get_table_names(order_by="foreign_key")):
                e.execute(schema.DropTable(schema.Table(tname, schema.MetaData())))

            if config.requirements.schemas.enabled_for_config(cfg):
                for tname in reversed(inspector.get_table_names(
                                        order_by="foreign_key", schema="test_schema")):
                    e.execute(schema.DropTable(
                        schema.Table(tname, schema.MetaData(), schema="test_schema"))) 

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 _assert_drop_w_alter(self, elements, generator, argument):
        self._assert_ddl(
            (schema.DropTable, schema.DropSequence, schema.DropConstraint),
            elements,
            generator,
            argument,
        ) 

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

def _assert_drop_tables(self, elements, generator, argument):
        self._assert_ddl(schema.DropTable, elements, generator, argument) 

def test_create_table(connect):
    conn = await connect()
    res = await conn.execute(DropTable(tbl))
    with pytest.raises(sa.ResourceClosedError):
        await res.fetchmany()

    with pytest.raises(psycopg2.ProgrammingError):
        await conn.execute("SELECT * FROM sa_tbl")

    res = await conn.execute(CreateTable(tbl))
    with pytest.raises(sa.ResourceClosedError):
        await res.fetchmany()

    res = await conn.execute("SELECT * FROM sa_tbl")
    assert 0 == len(await res.fetchall()) 

def connect(make_engine):
    async def go(**kwargs):
        engine = await make_engine(**kwargs)
        with (await engine) as conn:
            try:
                await conn.execute(DropTable(tbl))
            except psycopg2.ProgrammingError:
                pass
            try:
                await conn.execute(DropTable(tbl2))
            except psycopg2.ProgrammingError:
                pass
            await conn.execute("DROP TYPE IF EXISTS simple_enum CASCADE;")
            await conn.execute("""CREATE TYPE simple_enum AS ENUM
                                       ('first', 'second');""")
            try:
                await conn.execute(CreateTable(tbl))
                ret_tbl = tbl
                has_hstore = True
            except psycopg2.ProgrammingError:
                await conn.execute(CreateTable(tbl2))
                ret_tbl = tbl2
                has_hstore = False
        return engine, ret_tbl, has_hstore

    yield go 

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 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_table(self, table):
        self._exec(schema.DropTable(table)) 

def preapre_tables(pg):
    tables = [db.question, db.choice]
    async with pg.acquire() as conn:
        for table in reversed(tables):
            drop_expr = DropTable(table)
            try:
                await conn.execute(drop_expr)
            except psycopg2.ProgrammingError:
                pass

    async with pg.acquire() as conn:
        for table in tables:
            create_expr = CreateTable(table)
            await conn.execute(create_expr) 

def delete_tables(pg, tables):
    async with pg.acquire() as conn:
        for table in reversed(tables):
            drop_expr = DropTable(table)
            try:
                await conn.execute(drop_expr)
            except psycopg2.ProgrammingError:
                pass 

def delete_tables(pg, tables):
    async with pg.acquire() as conn:
        for table in reversed(tables):
            drop_expr = DropTable(table)
            try:
                await conn.execute(drop_expr)
            except psycopg2.ProgrammingError:
                pass 

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 _prep_testing_database(options, file_config):
    from sqlalchemy.testing import config
    from sqlalchemy import schema, inspect

    if options.dropfirst:
        for cfg in config.Config.all_configs():
            e = cfg.db
            inspector = inspect(e)
            try:
                view_names = inspector.get_view_names()
            except NotImplementedError:
                pass
            else:
                for vname in view_names:
                    e.execute(schema._DropView(
                        schema.Table(vname, schema.MetaData())
                    ))

            if config.requirements.schemas.enabled_for_config(cfg):
                try:
                    view_names = inspector.get_view_names(
                        schema="test_schema")
                except NotImplementedError:
                    pass
                else:
                    for vname in view_names:
                        e.execute(schema._DropView(
                            schema.Table(vname, schema.MetaData(),
                                         schema="test_schema")
                        ))

            for tname in reversed(inspector.get_table_names(
                    order_by="foreign_key")):
                e.execute(schema.DropTable(
                    schema.Table(tname, schema.MetaData())
                ))

            if config.requirements.schemas.enabled_for_config(cfg):
                for tname in reversed(inspector.get_table_names(
                        order_by="foreign_key", schema="test_schema")):
                    e.execute(schema.DropTable(
                        schema.Table(tname, schema.MetaData(),
                                     schema="test_schema")
                    )) 

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 _prep_testing_database(options, file_config):
    from alembic.testing import config
    from alembic.testing.exclusions import against
    from sqlalchemy import schema
    from alembic import util

    from sqlalchemy import inspect

    if options.dropfirst:
        for cfg in config.Config.all_configs():
            e = cfg.db
            inspector = inspect(e)
            try:
                view_names = inspector.get_view_names()
            except NotImplementedError:
                pass
            else:
                for vname in view_names:
                    e.execute(schema._DropView(
                        schema.Table(vname, schema.MetaData())
                    ))

            if config.requirements.schemas.enabled_for_config(cfg):
                try:
                    view_names = inspector.get_view_names(
                        schema="test_schema")
                except NotImplementedError:
                    pass
                else:
                    for vname in view_names:
                        e.execute(schema._DropView(
                            schema.Table(vname, schema.MetaData(),
                                         schema="test_schema")
                        ))

            for tname in reversed(inspector.get_table_names(
                    order_by="foreign_key")):
                e.execute(schema.DropTable(
                    schema.Table(tname, schema.MetaData())
                ))

            if config.requirements.schemas.enabled_for_config(cfg):
                for tname in reversed(inspector.get_table_names(
                        order_by="foreign_key", schema="test_schema")):
                    e.execute(schema.DropTable(
                        schema.Table(tname, schema.MetaData(),
                                     schema="test_schema")
                    ))

            if against(cfg, "postgresql") and util.sqla_100:
                from sqlalchemy.dialects import postgresql
                for enum in inspector.get_enums("*"):
                    e.execute(postgresql.DropEnumType(
                        postgresql.ENUM(
                            name=enum['name'],
                            schema=enum['schema']))) 

def _prep_testing_database(options, file_config):
    from alembic.testing import config
    from alembic.testing.exclusions import against
    from sqlalchemy import schema
    from alembic import util

    if util.sqla_08:
        from sqlalchemy import inspect
    else:
        from sqlalchemy.engine.reflection import Inspector
        inspect = Inspector.from_engine

    if options.dropfirst:
        for cfg in config.Config.all_configs():
            e = cfg.db
            inspector = inspect(e)
            try:
                view_names = inspector.get_view_names()
            except NotImplementedError:
                pass
            else:
                for vname in view_names:
                    e.execute(schema._DropView(
                        schema.Table(vname, schema.MetaData())
                    ))

            if config.requirements.schemas.enabled_for_config(cfg):
                try:
                    view_names = inspector.get_view_names(
                        schema="test_schema")
                except NotImplementedError:
                    pass
                else:
                    for vname in view_names:
                        e.execute(schema._DropView(
                            schema.Table(vname, schema.MetaData(),
                                         schema="test_schema")
                        ))

            for tname in reversed(inspector.get_table_names(
                    order_by="foreign_key")):
                e.execute(schema.DropTable(
                    schema.Table(tname, schema.MetaData())
                ))

            if config.requirements.schemas.enabled_for_config(cfg):
                for tname in reversed(inspector.get_table_names(
                        order_by="foreign_key", schema="test_schema")):
                    e.execute(schema.DropTable(
                        schema.Table(tname, schema.MetaData(),
                                     schema="test_schema")
                    ))

            if against(cfg, "postgresql") and util.sqla_100:
                from sqlalchemy.dialects import postgresql
                for enum in inspector.get_enums("*"):
                    e.execute(postgresql.DropEnumType(
                        postgresql.ENUM(
                            name=enum['name'],
                            schema=enum['schema']))) 

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()