使用Python连接Oracle服务器时出现连接被拒绝错误求助

环境信息 #

• Python 3.12.6
• Oracle SQL Developer Version 23.1.1.345

问题详情 #

编写Python脚本测试与Oracle服务器的连通性，脚本逻辑为获取数据库密码后输出连接成功提示，但执行时收到数据库主动拒绝连接的错误，不过通过Oracle SQL Developer可正常登录目标数据库。

测试脚本 #

import getpass
import oracledb

pw = getpass.getpass("Enter password: ")

connection = oracledb.connect(
    user="rep_dlp",
    password=pw,
    dsn="localhost/DEV_PROTECT")

print("Successfully connected to Oracle Database")

cursor = connection.cursor()

错误输出 #

D:\Reporting>python connect.py
Enter password:
Traceback (most recent call last):
  File "src\\oracledb\\impl/thin/connection.pyx", line 322, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "src\\oracledb\\impl/thin/protocol.pyx", line 225, in oracledb.thin_impl.Protocol._connect_phase_one
  File "src\\oracledb\\impl/thin/protocol.pyx", line 362, in oracledb.thin_impl.Protocol._connect_tcp
  File "C:\Program Files\Python312\Lib\socket.py", line 865, in create_connection
    raise exceptions[0]
  File "C:\Program Files\Python312\Lib\socket.py", line 850, in create_connection
    sock.connect(sa)
ConnectionRefusedError: [WinError 10061] No connection could be made because the target machine actively refused it

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "D:\Reporting\connect.py", line 6, in <module>
    connection = oracledb.connect(
                 ^^^^^^^^^^^^^^^^^
  File "C:\Program Files\Python312\Lib\site-packages\oracledb\connection.py", line 1169, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Program Files\Python312\Lib\site-packages\oracledb\connection.py", line 551, in __init__
    impl.connect(params_impl)
  File "src\\oracledb\\impl/thin/connection.pyx", line 424, in oracledb.thin_impl.ThinConnImpl.connect
  File "src\\oracledb\\impl/thin/connection.pyx", line 420, in oracledb.thin_impl.ThinConnImpl.connect
  File "src\\oracledb\\impl/thin/connection.pyx", line 380, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src\\oracledb\\impl/thin/connection.pyx", line 361, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src\\oracledb\\impl/thin/connection.pyx", line 327, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "C:\Program Files\Python312\Lib\site-packages\oracledb\errors.py", line 195, in _raise_err
    raise error.exc_type(error) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=ii7F2d6/yPjm22VqmzWk9g==).
[WinError 10061] No connection could be made because the target machine actively refused it

解决方法 #

1. 检查Oracle监听状态 #

SQL Developer可能使用本地IPC连接，而oracledb默认用TCP/IP连接，需确认监听是否正常：

• 打开命令提示符，执行lsnrctl status查看监听是否启动，且是否监听localhost的1521端口（Oracle默认端口）。
• 若监听未启动，执行lsnrctl start启动监听服务。

2. 修正DSN格式 #

当前DSN可能缺少端口或服务名不正确，尝试以下格式：

• 指定端口：localhost:1521/DEV_PROTECT
• 使用完整TNS格式：(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DEV_PROTECT)))
• 从SQL Developer连接属性中复制准确的主机名、端口、服务名，确保脚本参数完全匹配。

3. 切换oracledb连接模式 #

oracledb默认使用Thin模式，切换到Thick模式（需Oracle客户端支持）可能解决兼容性问题：
在脚本开头添加以下代码：

import oracledb
# 若Oracle客户端未在环境变量中，需指定路径，例如path="C:\\instantclient_21_10"
oracledb.init_oracle_client()

4. 检查防火墙设置 #

确认本地防火墙未阻止Python程序访问Oracle的1521端口，可临时关闭防火墙测试连接是否正常。

5. 验证服务名正确性 #

在SQL Developer中执行以下查询，获取当前连接的服务名，确保脚本中的DEV_PROTECT与结果一致（注意大小写敏感）：

SELECT sys_context('userenv','service_name') FROM dual;

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