On Jul 5, 2010, at 9:10 AM, Chris Withers wrote:
> 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'],
> )
why don't you create a ForeignKeyConstraint that only has the "name" field ?
DropConstraint doesn't need anything more than just the name.
> )
> t = Table(table_name,metadata,*fks,
> autoload=True,autoload_with=engine)
also here, again we only need a name. Make yourself a fake table:
t = Table(table_name, metadata, Column('dummy', Integer))
> tbs.append(t)
> for fkc in fks:
> conn.execute(DropConstraint(fkc,cascade=True))
>
> for table in tbs:
> conn.execute(DropTable(table))
> 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?
that's because you're reflecting the tables, which also reflects their related
tables.
--
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.
