Python 3.9使用oracledb连接Oracle数据库时候报错
环境：
Python 3.9 for Windows
oracledb 1.2
Oracle 11g on Susie Linux
Oracle数据库实际上是运行在本地的一台虚拟机上的，使用其他数据库工具可以连接并获得SQL结果。
在Python环境下，最简单的connect语句都无法连接。
import oracledb
oracle = oracledb.connect(dsn="SYSADM/SYSADM@172.16.136.100:1521/HR92DMO")
从报错来看似乎是连接上数据库以后出了一些问题。
Traceback (most recent call last):
File "C:\Users\softl\PycharmProjects\FuBoDTS\main.py", line 3, in
oracle = oracledb.connect(dsn="SYSADM/SYSADM@172.16.136.100:1521/HR92DMO")
File "C:\Users\softl\PycharmProjects\FuBoDTS\venv\lib\site-packages\oracledb\connection.py", line 1012, in connect
return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
File "C:\Users\softl\PycharmProjects\FuBoDTS\venv\lib\site-packages\oracledb\connection.py", line 135, in __init__
impl.connect(params_impl)
File "src\oracledb\impl/thin/connection.pyx", line 318, in oracledb.thin_impl.ThinConnImpl.connect
File "src\oracledb\impl/thin/connection.pyx", line 206, in oracledb.thin_impl.ThinConnImpl._connect_with_params
File "src\oracledb\impl/thin/connection.pyx", line 177, in oracledb.thin_impl.ThinConnImpl._connect_with_description
File "src\oracledb\impl/thin/connection.pyx", line 118, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "src\oracledb\impl/thin/protocol.pyx", line 230, in oracledb.thin_impl.Protocol._connect_phase_two
File "src\oracledb\impl/thin/protocol.pyx", line 344, in oracledb.thin_impl.Protocol._process_message
File "src\oracledb\impl/thin/protocol.pyx", line 321, in oracledb.thin_impl.Protocol._process_message
File "src\oracledb\impl/thin/messages.pyx", line 287, in oracledb.thin_impl.Message.send
File "src\oracledb\impl/thin/messages.pyx", line 1537, in oracledb.thin_impl.AuthMessage._write_message
File "src\oracledb\impl/thin/messages.pyx", line 1345, in oracledb.thin_impl.AuthMessage._generate_verifier
KeyError: 'AUTH_PBKDF2_CSK_SALT'
如果将连接数据库的IP地址随便改一个错误的，倒是能看到程序尝试了很久以后报无应答，所以猜想应该是连接中某些参数或者环境的问题。
自问自答，这真的是一个愚蠢的问题，Oracle客户端版本和服务器版本不一致。
1、删除了开发机上的Oracle客户端，这一步也是麻烦不断，反正多次重启和用命令行删除文件后成功删除。
2、下载正确版本的instant client，其实就是个zip文件，解压缩就可以免安装使用；
3、在Python代码里面指定几个必要的目录，再运行代码就成功了。
import oracledb
#初始化Oracle环境参数
#lib_dir=Oracle客户端安装的主目录
#config_dir=tnsnames.ora所在的目录，SID配置在这个文件里面
oracledb.init_oracle_client(lib_dir=r"C:\Oracle\instantclient_12_1", config_dir=r"C:\Oracle\instantclient_12_1\network\admin")
connection = oracledb.connect(user="SYSADM", password="SYSADM", dsn="172.16.136.100/HR92DMO")
cur = connection.cursor()
cur.execute("select * from PS_COMPANY_TBL")
while True:
row = cur.fetchone()
if row is None:
break
print(row)
配置db_config.py
user = os.environ.get("PYTHON_USER", "pythondemo")
dsn = os.environ.get("PYTHON_CONNECT_STRING", "localhost/orclpdb")
pw = os.environ.get("PYTHON_PASSWORD")
if pw is None:
pw = getpass.getpass("Enter password for %s: " % user)
-- Default database username
def user = "pythondemo"
-- Default database connection string
def connect_string = "localhost/orclpdb"
-- Prompt for the password
accept pw char prompt 'Enter database password for &user: ' hide
配置db_connect.py
import oracledb
import db_config
con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
print("Database version:", con.version)