我使用 python 成功连接到 oracle 数据库cx_oracle但我无法使用 sqlalchemy 连接到数据库

Question

import cx_Oracle
import sqlalchemy
import pandas as pd
from sqlalchemy import create_engine
hostname = r'myhost'
port = '0000'
sid = 'ssss'
username = 'myusername'
password = 'mypass'

dsn_tns = cx_Oracle.makedsn(hostname,port,sid) 
conn = cx_Oracle.connect(username, password=password, dsn=dsn_tns) 
conn.connect()
print('Connected to oracle using cx_oracle')
engine = create_engine(conn, connect_args={ "encoding": "UTF-8", "nencoding": "UTF-8"})
engine.connect()
print('Connected to sql alchemy')

这段代码在使用cx_oracle连接到oracle数据库后显示一个错误，它不能连接到sqlalchemy

出来把

使用cx_oracle Traceback(最近一次调用)连接到oracle:文件"D:ProjectPython脚本连接到oracleSql sqlalchemy_connect.py"，第16行engine = create_engine(conn, connect_args={"encoding";" UTF-8"， "nencoding";" UTF-8"})文件"，第2行，create_engine
File"C: 用户 mikim AppData 本地Python 程序 Python310 lib 网站 sqlalchemy util deprecations.py",第277行，警告return fn(*args， **kwargs) # type: ignore[no-any-return] File "C:UsersmikimAppDataLocalProgramsPythonPython310libsite-packagessqlalchemyenginecreate.py"，第549行，在create_engineu, plugins, kwargs = u._instantiate_plugins(kwargs) AttributeError: 'cx_Oracle. 'Connection对象没有属性"_instantiate_plugins">

Answer 1

安装最新版本:

python3 -m pip install sqlalchemy pandas oracledb --upgrade

确保您有SQLAchemy 2和pandas 2(对于本例—旧版本中所需的驱动程序和引擎创建语法略有不同)。请注意，python-oracledb是cx_Oracle的新名称。

在SQL*Plus中创建表:

drop table t purge;
create table t (k number, v varchar2(20));
insert into t (k, v) values (1234, 'neutral');
insert into t (k, v) values (5678, 'alkaline');
commit;

创建一个文件，t.p:

import os
import getpass
import platform
from sqlalchemy import create_engine, text
import pandas as pd
un = os.environ.get("PYTHON_USERNAME")
cs = os.environ.get("PYTHON_CONNECTSTRING")
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')
engine = create_engine(
f'oracle+oracledb://:@',
connect_args={
"user": un,
"password": pw,
"dsn": cs
})
query = """select * from t"""
df = pd.read_sql_query(text(query), engine)
print(df)

设置环境:

export PYTHON_USERNAME='cj'
export PYTHON_CONNECTSTRING='localhost/freepdb1'

运行:

$ python3 t.py
Enter password for cj@localhost/freepdb1: XXXXX
k         v
0  1234   neutral
1  5678  alkaline