甲骨文表的SqlAlchemy反射不拥有 [英] SqlAlchemy Reflection of Oracle Table Not Owned

问题描述

我正在与SQLAlchemy一起对Oracle数据库运行SQL查询.我对数据库具有读取访问权限，但是我拥有的用户不拥有我正在使用的任何表.

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.

数据库会定期更新，因此我希望使用反射而不是明确列出MetaData.我发现了这个问题，它描述了一个类似的问题到我所拥有的.但是，我无法更改表的所有权，也无法以任何方式修改数据库.我只有读取权限.

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.

如果我没有这些表的所有权，是否可以在SQLAlchemy中反映这些表?

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

(编辑)

示例代码:

engine = create_engine('ORACLE CONNECTION STRING')

metadata = MetaData()

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

我收到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)

输出为immutabledict({}).

所以从本质上讲什么都不是.所有这些表均由用户A拥有，在我以用户B的只读身份登录的情况下.

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.

推荐答案

如果您指定要定位的schema(帐户)，则可能会更好地反映其他人的表:

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')

这样，您将反映属于'userA'的所有可读表.不过，我不确定为什么您可以使用engine.execute来查询学生.

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.

这篇关于甲骨文表的SqlAlchemy反射不拥有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！