我有一个只有读访问权限的Oracle数据库连接.当我使用集合字符串连接到数据库时
engine = create_engine(f"oracle+oracledb://{DB_USER}:{DB_PASSWORD}@{DB_URL{DB_PORT}/{DB_SID}")
此连接成功,但此CONNECT/SID的默认数据库模式指向PD_READONLY,其中包含空表.具有正确数据/表的架构是PD_FULL_ACCESS.
使用任何数据库可视化工具,我都可以使用Return the Expect Data查询PD_FULL_ACCESS中的数据.
SELECT date_created, date_modified from PD_FULL_ACCESS.department;
SELECT date_created, date_modified from department;
返回一个有效的错误[Code: 942, SQL State: 42000] ORA-00942: table or view does not exist [Script position: 102 - 105]
,因为缺省的PD_READONLY中没有这样的表.
我的SQL模式如下:
class Department:
__tablename__ = "department"
__sa_dataclass_metadata_key__ = "sa"
id: float = field(
init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)}
)
label: str = field(metadata={"sa": Column(VARCHAR(255), nullable=False)})
active: str = field(
metadata={"sa": Column(CHAR(1), nullable=False, server_default=text("'Y' "))}
)
如何强制SqlalChemy将所有查询(例如,session.query(Department).all()
)指向PD_FULL_ACCESS而不是默认的PD_READONLY
我try 将连接字符串中的schema/service_name指定为:
DB_SERVICE_NAME="PD_READONLY"
engine = create_engine(
f"oracle+oracledb://{DB_USER}:{DB_PASSWORD}@{DB_URL}:{DB_PORT}/?service_name={DB_SERVICE_NAME}")
这给出了错误
sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) DPY-4027: no configuration directory to search for tnsnames.ora
我还试过:
engine = create_engine(f"oracle+oracledb://{DB_USER}:{DB_PASSWORD}@{DB_URL}:{DB_PORT}/{DB_SERVICE_NAME}")
这给出了错误
SID "None" is not registered with the listener at host
上面的两次try 看起来都需要在数据库级别进行tnlister调整.Any solution on the database level is not feasible in this case因为数据库是有很多依赖项的遗留应用程序,不能因为这种情况而更改/修改.
在查询级别上,我try 了一种天真的方法,将模式添加到查询中,如下所示:
db.query("PD_FULL_ACCESS.Department").all()
得到错误:
sqlalchemy.exc.ArgumentError: Textual column expression 'PD_FULL_ACCESS.Department' should be explicitly declared with text('PD_FULL_ACCESS.Department'), or use column('PD_FULL_ACCESS.Department') for more specificity
该项目设置为使用ORM而不是计划SQL查询.