无需Oracle Instant Client，如何用Pandas+SQLAlchemy连接Oracle？

尝试用Pandas搭配SQLAlchemy连接Oracle数据库，代码如下：

from sqlalchemy.engine import create_engine
import pandas as pd 
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'USER'   
PASSWORD = 'PASS'   
HOST = 'HOST'       
PORT = 1521         
SERVICE = 'Service' 
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE
engine = create_engine(ENGINE_PATH_WIN_AUTH)
#test query
test_df = pd.read_sql_query('SELECT * FROM table a WHERE rownum < 2', engine)

收到报错：

DatabaseError: (cx_Oracle.DatabaseError) DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help
(Background on this error at: https://sqlalche.me/e/14/4xp6)

因公司笔记本无法访问Oracle官网，也无法通过其他途径获取Oracle Instant Client库（USB端口被封、无云存储权限等），但可以用DBeaver正常查询该数据库，询问是否有无需Instant Client的Python连接方案。

1. 复用DBeaver内置的Oracle客户端库 #

DBeaver自带Oracle驱动相关库，直接复用即可：

• 找到DBeaver安装目录下的Oracle驱动文件夹，通常路径类似 C:\Program Files\DBeaver\drivers\oracle
• 将该路径添加到系统PATH环境变量，或者在Python代码启动时临时注入路径：

import os
# 替换为你的DBeaver Oracle驱动实际路径
dbeaver_oracle_path = r"C:\Program Files\DBeaver\drivers\oracle"
os.environ["PATH"] = dbeaver_oracle_path + ";" + os.environ["PATH"]

# 后续执行原有连接代码
from sqlalchemy.engine import create_engine
import pandas as pd 
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'USER'   
PASSWORD = 'PASS'   
HOST = 'HOST'       
PORT = 1521         
SERVICE = 'Service' 
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE
engine = create_engine(ENGINE_PATH_WIN_AUTH)
test_df = pd.read_sql_query('SELECT * FROM table a WHERE rownum < 2', engine)

2. 通过ODBC数据源连接 #

利用系统ODBC配置复用现有连接能力：

• 打开Windows「ODBC数据源管理器（64位）」，添加Oracle类型的ODBC数据源，填入和DBeaver一致的连接参数（主机、端口、服务名、账号密码）
• 安装pyodbc包：pip install pyodbc
• 使用SQLAlchemy配合ODBC连接：

from sqlalchemy.engine import create_engine
import pandas as pd

# 替换为你配置的ODBC数据源名称
odbc_dsn = "你的Oracle ODBC数据源名称"
engine = create_engine(f"oracle+pyodbc://{USERNAME}:{PASSWORD}@{odbc_dsn}")
test_df = pd.read_sql_query('SELECT * FROM table a WHERE rownum < 2', engine)

3. 使用纯Python驱动oracledb的薄模式 #

oracledb支持无需Oracle客户端的纯Python连接模式：

• 安装oracledb包：pip install oracledb
• 用薄模式直接连接：

import oracledb
import pandas as pd

# 启用薄模式，无需客户端库
oracledb.init_oracle_client(lib_dir=None)
# 建立连接
connection = oracledb.connect(
    user=USERNAME,
    password=PASSWORD,
    dsn=f"{HOST}:{PORT}/{SERVICE}"
)
# 查询数据
test_df = pd.read_sql('SELECT * FROM table a WHERE rownum < 2', connection)
connection.close()

内容的提问来源于stack exchange，提问作者Rafa.Rugamas