All geek questions in one place

SqlAlchemy Oracle table reflection does not belong

I am working with SQLAlchemy to run SQL queries against an Oracle database. I read access to the database, but a user that I don’t have has any of the tables I'm working with.

Database updates on a regular basis, so instead of explicitly listing MetaData, I was hoping to use reflection. I found this question that describes a problem similar to what I have. However, I have no way to change the ownership of the tables and somehow modify the database. I just got read access.

Is there a way to reflect Oracle tables in SQLAlchemy if I don't have rights to these tables?

(Edit)

Code example:

engine = create_engine('ORACLE CONNECTION STRING') metadata = MetaData() students = Table('students', metadata, autoload=True, autoload_with=engine)

I get sqlalchemy.exc.NoSuchTableError: students exception

However, when I run the following:

 results = engine.execute('SELECT * FROM students') for r in results: print(r)

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

So instead of trying to mirror one table, I try to mirror them all:

 metadata.reflect(bind=engine) print(metadata.tables)

The output is immutabledict({}) .

So in essence it’s nothing. All these tables belong to user A when I log in with read-only user B.

+6
source share
1 answer

You might be able to flip other tables if you specify the targeting of the schema (account) that you plan:

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

Thus, you will display all readable tables belonging to "userA". I'm not sure why you can request students using engine.execute , though.

+5
source

Source: https://habr.com/ru/post/974219/

More articles:


All Articles