使用Python、SQLAlchemy与oracledb连接Oracle报错，求可行解决方案

问题背景 #

用户使用Python结合SQLAlchemy（版本2.0.28）和oracledb（版本2.0.1）连接Oracle数据库，编写的代码如下：

import os
import oracledb
from sqlalchemy import create_engine
from sqlalchemy import text

print(oracledb.__file__)

username = os.environ.get("PYTHON_USERNAME")
password = os.environ.get("PYTHON_PASSWORD")

cp = oracledb.ConnectParams()
cp.parse_connect_string(os.environ.get("PYTHON_CONNECTSTRING"))

thick_mode = None

engine = create_engine(
    f'oracle+oracledb://{username}:{password}@{cp.host}:{cp.port}/?service_name={cp.service_name}',
    thick_mode=thick_mode)

stmt = 'SELECT UNIQUE CLIENT_DRIVER FROM V$SESSION_CONNECT_INFO WHERE SID = SYS_CONTEXT(''USERENV'', ''SID'')'
with engine.connect() as connection:
    print(connection.scalar(text(stmt)))

对应的.env文件内容：

PYTHON_USERNAME="scott"
PYTHON_PASSWORD="tiger"
PYTHON_CONNECTSTRING="192.168.1.3:1521/orclpdb"

运行脚本时触发以下错误：

C:\Users\HI>C:\Users\HI\AppData\Local\Programs\Python\Python312\python.exe "C:\Users\HI\Desktop\New folder\streamlit\langchain\sa-pydb.py"
C:\Users\HI\AppData\Local\Programs\Python\Python312\Lib\site-packages\oracledb\__init__.py
Traceback (most recent call last):
  File "C:\Users\HI\Desktop\New folder\streamlit\langchain\sa-pydb.py", line 13, in <module>
    cp.parse_connect_string(os.environ.get("PYTHON_CONNECTSTRING"))
  File "C:\Users\HI\AppData\Local\Programs\Python312\Lib\site-packages\oracledb\connect_params.py", line 666, in parse_connect_string
    self._impl.parse_connect_string(connect_string)
  File "src\\oracledb\\impl/base/connect_params.pyx", line 551, in oracledb.base_impl.ConnectParamsImpl.parse_connect_string
TypeError: descriptor 'strip' for 'str' objects doesn't apply to a 'NoneType' object

错误原因 #

核心问题是os.environ.get("PYTHON_CONNECTSTRING")返回了None值，导致cp.parse_connect_string()方法接收到非字符串参数，触发strip方法的类型错误。这是因为Python默认不会自动读取.env文件中的配置，系统环境变量中不存在对应的键值对。

可行解决方案 #

方法一：使用python-dotenv加载.env文件 #

这是最规范的解决方案，适合开发环境：

1. 安装依赖包：

   pip install python-dotenv
   

2. 在脚本开头添加加载.env文件的代码：

   from dotenv import load_dotenv
   # 加载当前目录下的.env文件，若文件在其他路径可传入绝对路径
   load_dotenv()
   

   修改后的完整代码：

   import os
   from dotenv import load_dotenv
   import oracledb
   from sqlalchemy import create_engine
   from sqlalchemy import text
   
   load_dotenv()
   print(oracledb.__file__)
   
   username = os.environ.get("PYTHON_USERNAME")
   password = os.environ.get("PYTHON_PASSWORD")
   
   cp = oracledb.ConnectParams()
   cp.parse_connect_string(os.environ.get("PYTHON_CONNECTSTRING"))
   
   thick_mode = None
   
   engine = create_engine(
       f'oracle+oracledb://{username}:{password}@{cp.host}:{cp.port}/?service_name={cp.service_name}',
       thick_mode=thick_mode)
   
   stmt = 'SELECT UNIQUE CLIENT_DRIVER FROM V$SESSION_CONNECT_INFO WHERE SID = SYS_CONTEXT(''USERENV'', ''SID'')'
   with engine.connect() as connection:
       print(connection.scalar(text(stmt)))
   

方法二：手动设置环境变量（Windows命令行） #

在运行脚本前，先在命令行手动配置环境变量：

set PYTHON_USERNAME=scott
set PYTHON_PASSWORD=tiger
set PYTHON_CONNECTSTRING=192.168.1.3:1521/orclpdb
python sa-pydb.py

方法三：直接在代码中硬编码参数（仅临时测试用） #

不建议生产环境使用，仅用于快速验证连接：

import os
import oracledb
from sqlalchemy import create_engine
from sqlalchemy import text

print(oracledb.__file__)

username = "scott"
password = "tiger"
connect_string = "192.168.1.3:1521/orclpdb"

cp = oracledb.ConnectParams()
cp.parse_connect_string(connect_string)

thick_mode = None

engine = create_engine(
    f'oracle+oracledb://{username}:{password}@{cp.host}:{cp.port}/?service_name={cp.service_name}',
    thick_mode=thick_mode)

stmt = 'SELECT UNIQUE CLIENT_DRIVER FROM V$SESSION_CONNECT_INFO WHERE SID = SYS_CONTEXT(''USERENV'', ''SID'')'
with engine.connect() as connection:
    print(connection.scalar(text(stmt)))

验证步骤 #

可以在代码中添加打印语句，确认环境变量是否正确加载：

print("Username:", os.environ.get("PYTHON_USERNAME"))
print("Password:", os.environ.get("PYTHON_PASSWORD"))
print("Connect String:", os.environ.get("PYTHON_CONNECTSTRING"))

如果输出均为None，说明.env文件未被正确加载，优先使用方法一解决。

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