async def delete(self):
""" Delete all the tables and indexes """
async with self._pool.acquire() as conn:
await conn.execute(str(DropIndex(self._i_peers_phone_number)))
await conn.execute(str(DropIndex(self._i_peers_username)))
await conn.execute(str(DropTable(self._t_session)))
await conn.execute(str(DropTable(self._t_peers)))
async def visit_table(self,
table,
drop_ok=False,
_is_metadata_operation=False):
if not drop_ok and not await self._can_drop_table(table):
return
await _Async(table.dispatch.before_drop)(
table,
self.connection,
checkfirst=self.checkfirst,
_ddl_runner=self,
_is_metadata_operation=_is_metadata_operation,
)
for column in table.columns:
if column.default is not None:
await self.traverse_single(column.default)
await self.connection.status(DropTable(table))
await _Async(table.dispatch.after_drop)(
table,
self.connection,
checkfirst=self.checkfirst,
_ddl_runner=self,
_is_metadata_operation=_is_metadata_operation,
)
def drop_all(session):
inspector = reflection.Inspector.from_engine(session.bind)
# 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:
session.execute(DropConstraint(fkc))
for table in tbs:
session.execute(DropTable(table))
session.commit()
def delete_all_tables(db):
"""Drop all tables in the database."""
conn = db.engine.connect()
transaction = conn.begin()
inspector = inspect(db.engine)
metadata = MetaData()
all_schema_tables = get_all_tables(db)
tables = []
all_fkeys = []
for schema, schema_tables in all_schema_tables.items():
for table_name in schema_tables:
fkeys = [ForeignKeyConstraint((), (), name=fk['name'])
for fk in inspector.get_foreign_keys(table_name, schema=schema)
if fk['name']]
tables.append(Table(table_name, metadata, *fkeys, schema=schema))
all_fkeys.extend(fkeys)
for fkey in all_fkeys:
conn.execute(DropConstraint(fkey))
for table in tables:
conn.execute(DropTable(table))
for schema in all_schema_tables:
if schema != 'public':
row = conn.execute('''
SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ')'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = '{}' order by proname;
'''.format(schema))
for stmt, in row:
conn.execute(stmt)
conn.execute(DropSchema(schema))
transaction.commit()
def db_drop_everything(db):
# source: https://www.mbeckler.org/blog/?p=218
# From http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DropEverything
conn = db.engine.connect()
trans = conn.begin()
inspector = reflection.Inspector.from_engine(db.engine)
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:
conn.execute(DropConstraint(fkc))
for table in tbs:
conn.execute(DropTable(table))
trans.commit()
def delete_all_tables(db):
"""Drops all tables in the database"""
conn = db.engine.connect()
transaction = conn.begin()
inspector = Inspector.from_engine(db.engine)
metadata = MetaData()
all_schema_tables = get_all_tables(db)
tables = []
all_fkeys = []
for schema, schema_tables in all_schema_tables.iteritems():
for table_name in schema_tables:
fkeys = [ForeignKeyConstraint((), (), name=fk['name'])
for fk in inspector.get_foreign_keys(table_name, schema=schema)
if fk['name']]
tables.append(Table(table_name, metadata, *fkeys, schema=schema))
all_fkeys.extend(fkeys)
for fkey in all_fkeys:
conn.execute(DropConstraint(fkey))
for table in tables:
conn.execute(DropTable(table))
for schema in all_schema_tables:
if schema != 'public':
conn.execute(DropSchema(schema))
transaction.commit()
def drop_everything(self):
conn = self.engine.connect()
trans = conn.begin()
inspector = reflection.Inspector.from_engine(self.engine)
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:
conn.execute(DropConstraint(fkc))
for table in tbs:
conn.execute(DropTable(table))
trans.commit()
async 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
async def drop_table(self, conn):
"""
删除表
"""
from sqlalchemy.sql.ddl import DropTable
# async with engine.acquire() as conn:
for table in self._tables:
await conn.execute(DropTable(table))
def source(test_dataset, connection):
table = Table(
f"{test_dataset}.source",
metadata,
Column("s1", String),
Column("s2", Date)
)
logger.info(f"Creating table {table}")
connection.execute(CreateTable(table))
yield table
logger.info(f"Dropping table {table}")
connection.execute(DropTable(table))
def db_drop_everything(db):
# From http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DropEverything
conn = db.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(db.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:
conn.execute(DropConstraint(fkc))
for table in tbs:
conn.execute(DropTable(table))
trans.commit()
def test_compile_drop_table_ddl():
drop_statement = DropTable(ddl_test_table)
drop_query, params = connection.compile_query(drop_statement)
assert drop_query == '\nDROP TABLE ddl_test_table'
assert len(params) == 0
def drop_table_sql(self, table: 'sa.Table') -> DropTable:
"""
生成删除表的sql语句
"""
return DropTable(table)