On Sep 21, 2012, at 11:20 AM, Hank wrote:
> I'm running into a very strange problem when I attempt to reflect against my
> Oracle DB. I've already pinged the IRC channel, and asked a question on
> StackOverflow. I'm pasting that question verbatim below. If you need anything
> else, please let me know.
>
> I'm attempting to reverse engineer an existing Oracle schema into some
> declarative SQLAlchemy models. My problem is that when I use
> [`MetaData.reflect`](http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html?highlight=metadata.reflect#sqlalchemy.schema.MetaData.reflect),
> it doesn't find the tables in my schema, just a Global Temp Table. However,
> I can still query against the other tables.
>
> I'm using SQLAlchemy 0.7.8, CentOS 6.2 x86_64, python 2.6, cx_Oracle 5.1.2
> and Oracle 11.2.0.2 Express Edition. Here's a quick sample of what I'm
> talking about:
>
> >>> import sqlalchemy
> >>> engine =
> sqlalchemy.create_engine('oracle+cx_oracle://user:pass@localhost/xe')
> >>> md = sqlalchemy.MetaData(bind=engine)
> >>> md.reflect()
> >>> md.tables
> immutabledict({u'my_gtt': Table(u'my_gtt',
> MetaData(bind=Engine(oracle+cx_oracle://user:pass@localhost/xe)),
> Column(u'id', NUMBER(precision=15, scale=0, asdecimal=False),
> table=<my_gtt>), Column(u'parent_id', NUMBER(precision=15, scale=0,
> asdecimal=False), table=<my_gtt>), Column(u'query_id', NUMBER(precision=15,
> scale=0, asdecimal=False), table=<my_gtt>), schema=None)})
> >>> len(engine.execute('select * from my_regular_table').fetchall())
> 4
how are these tables represented in the default schema of that database
connection? if they are referred to via oracle synonyms, then they don't exist
there as tables, they're in some other schema.
The Oracle dialect features a helper called "oracle_resolve_synonyms", which
will cause the dialect to resolve a synonym-named table, but that only works
within the Table() call itself, that is, Table("somename", autoload=True,
oracle_resolve_synonyms=True). Right now the metadata.reflect() call does not
make use of the "resolve_synonyms" feature.
So assuming this is what's going on, you'd need to specify the schema where the
tables are actually present, such as:
md.reflect(schema="mytables")
when you do that, you'll get Table objects back which will include "mytables"
as the "schema" argument. When these Table objects are used, you'll see the
schema explicitly referenced, that is, "select * from myschema.mytable".
--
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.
