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,

Or maybe I should first ask, is it possible to do such a thing? — user1711699, Oct 01 '12 at 12:37

score 14 · Accepted Answer · edited May 23 '17 at 11:53

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.

Thank you for your answer. But as I can understand, it is not possible to get a list of all schema from the database without being the database administrator. — user1711699, Oct 05 '12 at 09:47
Usually there is no need to be administrator to get permissions for listing tables and schemas in oracle. It depends what permissions user account has, but normally there are such permissions. Try running this query: SELECT OWNER,count(*) table_count FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' GROUP BY OWNER; — vvladymyrov, Oct 05 '12 at 13:59
Thank you again. How can I do this in SQLAlchemy? How can I query the whole database? What is the equivalent of 'all_objects' in sqlalchemy? — user1711699, Oct 08 '12 at 08:48
SQLAlchemy is "Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL." And it is not Oracle Management tool. So there is no any special way of using SQLAlchemy to reflect whole database - you should specify schema. But you can use SQLAlchemy to run the query from my comment. See updated code in my answer. — vvladymyrov, Oct 08 '12 at 21:29

phyatt · Answer 2 · 2018-06-15T15:47:44.230

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.

sqlalchemy Oracle schema

2 Answers2