That won't quite work for me - I'm not really 'building up' the metadata, I'm introspecting it using SqlSoup so the schema name gets put there automatically.
I had hoped to do this automatically, but I really only have to copy the schema once, so what I did is just "print repr(table._table)" for each table I was interested in and direct that output to a file. The resulting file was executable python, and nearly exactly what I needed - in a text editor, I just removed all "table=<>" arguments and striped out the schema names. I then ran that script and - viola! I had a sqlite database that exactly matched the structure of the original SQL Server database. I can't even tell you how amazed I am right now. You should all be really proud of yourselves - SQLAlchemy is incredible. On Aug 25, 11:10 am, "Michael Bayer" <[email protected]> wrote: > look into buildng your metadata first with no schema, then copying it to > the MSSQL schema using tometadata(m, schema='myschema'). the schema name > change should be propagated into foreign keys. unfortunately it doesn't > seem to support un-setting the schema name at the moment so that's why the > reverse direction. > > markscottwright wrote: > > > I'm trying to use SQLAlchemy to move a database from SQLServer to > > SQLite (so I can play with it at home on my linux box). I've gotten > > surprisingly far - I'm very impressed with SQLAlchemy! But I'm facing > > a problem with MS Sql's 'schema names' - which other dbs refer to a > > 'owners', I think. Sadly, sqlite doesn't support creating a table > > called 'dbo.abc'. > > > My code so far looks like this: > > > src_db = SqlSoup('mssql://blah blah blah') > > src_db.schema = 'dbo' > > > # dest > > dest_engine = sqlalchemy.create_engine("sqlite:///dest.db", echo=True) > > dest_md = sqlalchemy.MetaData(bind=dest_engine) > > > table_names = ['xxx', ...] > > for table_name in table_names: > > src_table = getattr(src_db, table_name) > > dest_table = src_table._table.tometadata(dest_md) > > dest_table.schema = None > > dest_table.create() > > > This works, until you hit a table for foreign keys. The schema name > > is part of the foreign key name, but sqlite can't deal with them, and > > I get errors that look like this: > > C:\tools\Python25\lib\site-packages\sqlalchemy\schema.pyc in > > _init_existing(self, *args, **kwargs) > > 252 raise exc.ArgumentError( > > 253 "Can't change schema of existing table from > > '%s' to '%s'", > > --> 254 (self.schema, schema)) > > 255 > > 256 include_columns = kwargs.pop('include_columns', None) > > > ArgumentError: ("Can't change schema of existing table from '%s' to > > '%s'", (None, u'dbo')) > > > Sadly, I can't just do a 'dest_table.foreign_keys.clear()' before > > calling create() - it doesn't seem to do anything. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
