By far the easiest approach is to modify the username you're coming into Oracle as so that the schema in question is the default. Or if you can, create Oracle synonyms (i.e. CREATE SYNONYM) in the default schema that link to the schema-qualified tables.
Otherwise SQLA doesn't have a lot of ability to change the "schema" name. The tometadata() approach you've used is the best it has, however you'd need to declare all new classes against those Table objects. A recipe for doing this is here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName. Another way you might do it is to put an event handler that modifies all the SQL to replace a particular "schema" name with something else, or nothing, like s/someschema./someotherschema./ type of thing. You can do this with the before_execute or before_cursor_execute events: engine = create_engine(...) @event.listens_for(engine, "before_cursor_execute", retval=True) def replace_schema(conn, cursor, statement, parameters, context, executemany): statement = statement.sub("someschema.", "") return statement, parameters On Jun 6, 2012, at 3:51 PM, Shawn Wheatley wrote: > Hi, > > I'm trying to use my declarative models to copy data from an Oracle database > with a non-default schema name to a SQLite database (which has no schema > name, or at least a default name that can't be changed). Copying from Oracle > to Oracle has not been a problem for me, but Oracle to SQLite will not work. > The problem for me is that the schema definition used for SQL generation is > on the table. I went through a fruitless exercise of calling "tometadata" on > every table in the metadata created by the generated declarative base class, > copying into a new MetaData object. I then swapped the metadata on the > declarative base and ran my query, with the intention of swapping it back > after. No luck. > > The purpose of my project is to surgically extract related data for a small > subset of accounts from our production database and bring it down to a local > SQLite database. Does anybody have experience doing this? Am I going about > this the wrong way? > > Thanks for any help, > Shawn > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/Y6z2q5U_B8gJ. > To post to this group, send email to sqlalchemy@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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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.
