CODEWITHSUNDEEP
pythonsqloracle-databasesqlalchemy
user2004245
user2004245

Reputation: 399

SqlAlchemy Reflection of Oracle Table Not Owned

I'm working with SQLAlchemy to run SQL queries against an Oracle database. I have read access to the database, but the user I have does not own any of the tables I'm working with.

The database updates on a regular basis, so rather than explicitly listing the MetaData, I was hoping to use reflection. I found this question, that describes an issue similar to what I'm having. However, I don't have a way to change ownership of the tables, nor modify the database in any way. I just have read access.

Is there a way to reflect Oracle tables in SQLAlchemy if I don't have ownership of those tables?

(Edit)

Example Code:

engine = create_engine('ORACLE CONNECTION STRING')

metadata = MetaData()

students = Table('students', metadata, autoload=True, autoload_with=engine)

I receive an exception of sqlalchemy.exc.NoSuchTableError: students

However, when I run the following:

results = engine.execute('SELECT * FROM students')

for r in results:
    print(r)

I receive the output that I expected from the table, which is a tuple of all the fields for each row.

So instead of trying to reflect a single table, I try to reflect all of them:

metadata.reflect(bind=engine)

print(metadata.tables)

The output is immutabledict({}).

So essentially it's nothing. All of these tables are owned by user A where as I'm logging in with a read-only of user B.

Upvotes: 7

Views: 2228

Answers (1)

jkmacc
jkmacc

Reputation: 6427

You might have better luck reflecting someone else's tables if you specify the schema (account) you're targeting:

metadata.reflect(bind=engine, schema='userA')

This way, you'll reflect all readable tables belonging to 'userA'. I'm not sure why you're able to query students using engine.execute, though.

Upvotes: 10

Related Questions