使用SQLAlchemy连接Oracle时遭遇ORA-12505错误求助

问题背景 #

尝试通过依赖cx_Oracle的SQLAlchemy实现Python与Oracle数据库的连接，后续计划将代码用于LangChain对接OpenAI，但运行代码时触发ORA-12505错误。已确认可通过sqlplus以SCOTT用户成功连接orclpdb实例，但执行Python代码时失败。

现有代码与连接信息 #

app.py代码 #

from sqlalchemy import create_engine
import cx_Oracle
import config

sid = cx_Oracle.makedsn(config.DATABASE['host'], config.DATABASE['port'], sid=config.DATABASE['database'])

cstr = '{drivername}://{user}:{password}@{sid}'.format(
    drivername = config.DATABASE['drivername'],
    user=config.DATABASE['username'],
    password=config.DATABASE['password'],
    sid=sid
)

engine =  create_engine(
    cstr,
#    convert_unicode=False,
    pool_recycle=10,
    pool_size=50,
    echo=True
)

stmt = 'select * from emp'
with engine.connect() as conn:
     result = conn.execute(stmt)

for row in result:
    print(row)

config.py代码 #

DATABASE = {
'drivername' : 'oracle',
'host'        : '192.168.1.3',
'port'        : '1521',
'database'    :'orclpdb',
'username'    :'scott',
'password'    :'tiger'
}

成功的sqlplus连接示例 #

C:\Users\HI\Desktop\New folder>sqlplus scott/tiger@//DESKTOP-CKEA6JR:1521/orclpdb
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 7 15:22:24 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Last Successful login time: Thu Mar 07 2024 14:55:54 +05:30
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> sho con_name
CON_NAME
------------------------------
ORCLPDB
SQL>
SQL> alter system register;
System altered.

执行命令 #

C:\Users\HI\AppData\Local\Programs\Python\Python312\python.exe app.py

问题原因 #

ORA-12505错误是由于监听无法识别请求中的SID。观察成功的sqlplus连接使用的是服务名格式（//主机:端口/服务名），但代码中使用cx_Oracle.makedsn时传入的是sid参数，而orclpdb作为可插拔数据库（PDB），通常使用服务名而非SID作为连接标识。

解决方案 #

提供两种修改方式，任选其一即可：

方式一：直接构造服务名格式的连接串 #

放弃使用cx_Oracle.makedsn，直接按照sqlplus的连接格式构造字符串：

from sqlalchemy import create_engine
import config

# 直接构造服务名格式的连接串
cstr = '{drivername}://{user}:{password}@{host}:{port}/{database}'.format(
    drivername=config.DATABASE['drivername'],
    user=config.DATABASE['username'],
    password=config.DATABASE['password'],
    host=config.DATABASE['host'],
    port=config.DATABASE['port'],
    database=config.DATABASE['database']
)

engine = create_engine(
    cstr,
    pool_recycle=10,
    pool_size=50,
    echo=True
)

stmt = 'select * from emp'
with engine.connect() as conn:
     result = conn.execute(stmt)

for row in result:
    print(row)

方式二：修改makedsn参数为service_name #

如果需要继续使用cx_Oracle.makedsn，将参数从sid改为service_name：

from sqlalchemy import create_engine
import cx_Oracle
import config

# 使用service_name参数指定PDB服务名，而非sid
sid = cx_Oracle.makedsn(config.DATABASE['host'], config.DATABASE['port'], service_name=config.DATABASE['database'])

cstr = '{drivername}://{user}:{password}@{sid}'.format(
    drivername=config.DATABASE['drivername'],
    user=config.DATABASE['username'],
    password=config.DATABASE['password'],
    sid=sid
)

engine = create_engine(
    cstr,
    pool_recycle=10,
    pool_size=50,
    echo=True
)

stmt = 'select * from emp'
with engine.connect() as conn:
     result = conn.execute(stmt)

for row in result:
    print(row)

说明 #

可插拔数据库（PDB）的SID通常与容器数据库（CDB）一致，而服务名是PDB的专属标识。代码需与sqlplus的连接方式保持一致，使用服务名才能正确连接到目标PDB实例。

内容的提问来源于stack exchange，提问作者nate brown