sqlalchemy Oracle schema

Question

I should extract data from an oracle database. How can I find out which schema are defined in the database? When I do not define any schema in the description of Metadata(), I find no tables. thanks for your help,

Answer 1

Default Oracle schema matches the username that was used in Oracle connection. If you don't see any tables - it means the tables are created in another schema.

Looks like you have two questions here:

1) about Oracle schemas - how to find schema and tables in Oracle

2) about SQLAlchemy reflections - how to specify Oracle schema for table

You can find answer for the first question in many places. I.e. here: https://stackoverflow.com/a/2247758/1296661

Answering second question: Table class constructor has schema argument to specify table's schema if it is different from default user's schema. See more here http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#sqlalchemy.schema.Table

Here is the python code to answer second question. You will need to setup db connection and table name values to match your case:

from sqlalchemy import Table
from sqlalchemy.ext.declarative import declarative_base


from sqlalchemy import create_engine
engine = create_engine('oracle://<user_name>:<password>@<hostname>:1521/<instance name>', echo=True)
Base = declarative_base()

reflected_table = Table('<Table name>', 
    Base.metadata, 
    autoload=True, 
    autoload_with=engine, 
    schema='<Schema name other then user_name>')
print [c.name for c in reflected_table.columns]

p = engine.execute("SELECT OWNER,count(*) table_count FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' GROUP BY OWNER");

for r in p:
    print r

Good luck with using sqlschema and reflection feature - it is a lot of fun. You get your python program working with existing database almost without defining schema information in your program.

I'm using this feature with oracle db in production - the only thing I have to define were relations between tables explicitly setting foreign and primary keys.

Answer 2

I found two other ways to set an alternate Oracle schema.

Using MetaData and passing it into the creation of the SqlAlchemy instance or engine.

Or using __table_args__ when defining your models and pass in {'schema': 'other_schema' }

http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.params.schema

How to specify PostgreSQL schema in SQLAlchemy column/foreign key mixin?

But both of these don't help the foreign key calls. For those, you still have to manually prefix with other_schema. before the table name.

The snippet below is in the context of a Flask app with Flask-SqlAlchemy in use.

from sqlalchemy import schema
from flask_sqlalchemy import SQLAlchemy
import os

os.environ['TNS_ADMIN'] = os.path.join( 'oracle_tools', 'network', 'admin')

oracle_connection_string = 'oracle+cx_oracle://{username}:{password}@{tnsname}'.format(
        username='user',
        password='pass',
        tnsname='TNS_SPACE',
    )

oracle_schema = 'user_schema_1'
foreign_key_prefix = oracle_schema + '.'

app.config['SQLALCHEMY_DATABASE_URI'] = oracle_connection_string 


oracle_db_metadata = schema.MetaData(schema=oracle_schema)
db = SQLAlchemy(app, metadata=oracle_db_metadata)

user_groups_table = db.Table(db_table_prefix + 'user_groups', db.Model.metadata,
                             db.Column('...', db.Integer, db.ForeignKey(foreign_key_prefix + 'group.id')),
                             # schema=oracle_schema #  Not necessary because of the use of MetaData above
                             )

class User(db.Model):
    __tablename__ = 'user'
    # __table_args__ = {'schema': oracle_schema } # Not necessary because of the use of MetaData above
    user_name = db.Column('USER_NAME', db.String(250))

And... two other related links:

https://github.com/mitsuhiko/flask-sqlalchemy/issues/172

How to specify PostgreSQL schema in SQLAlchemy column/foreign key mixin?

Hope that helps.