Chris Withers wrote:
Hi All,
I'm looking to use:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DropEverything
Sadly, the recipe uses hard-coded sql which doesn't work with MySQL.
I'd like to change it so that it's database-agnostic like the rest of
SQLAlchemy.
My hunting has lead me to:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalchemy.schema.DropConstraint
...but I'm not sure how to plumb that in given that the recipe only
provides a constraint name and table name, not the requisite objects.
Well, my current attempt is:
from sqlalchemy.engine import reflection
from sqlalchemy.schema import (
MetaData,
Table,
DropTable,
ForeignKeyConstraint,
DropConstraint,
)
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 = []
for table_name in inspector.get_table_names():
fks = []
for fk in inspector.get_foreign_keys(table_name):
fks.append(
ForeignKeyConstraint(
columns=fk['constrained_columns'],
refcolumns=fk['referred_columns'],
name=fk['name'],
)
)
t = Table(table_name,metadata,*fks,
autoload=True,autoload_with=engine)
tbs.append(t)
for fkc in fks:
conn.execute(DropConstraint(fkc,cascade=True))
for table in tbs:
conn.execute(DropTable(table))
trans.commit()
This feels pretty clunky and slow to me. Are there better ways?
With the above, I did suffer with the following a few times:
sqlalchemy.exc.InvalidRequestError: Table 'some_table' is already
defined for this MetaData instance. Specify 'useexisting=True' to
redefine options and columns on an existing Table object.
This doesn't make sense, assuming inspector.get_table_names() only
returns each table name once... Which it does, right?
cheers,
Chris
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
