本例运行环境为linux,需要提前安装oracle客户端,前面已进行安装

cx_Oracle包依赖oracle客户端中的这些库文件

import cx_Oracle

conn = cx_Oracle.connect('user','password','ip:port/DB',encoding="UTF-8") 
cur = conn.cursor() 

query = cur.execute("select * from tablename where rownum <= 10")
col = [c[0] for c in cur.description] #获取列名
wq = query.fetchall() #获取数据

cur.close()
conn.close()

import pandas as pd
data = pd.DataFrame(wq,columns=col)

自动关闭连接

Connections should be released when they are no longer needed by calling Connection.close(). Alternatively, you may prefer to let connections be automatically cleaned up when references to them go out of scope. This lets cx_Oracle close dependent resources in the correct order. One other approach is the use of a “with” block, for example:

with cx_Oracle.connect(user=user, password=password,
    dsn="dbhost.example.com/orclpdb1",encoding="UTF-8") as connection:
    cursor = connection.cursor()
    cursor.execute("insert into SomeTable values (:1, :2)",(1, "Some string"))
    connection.commit()

 
https://www.oracle.com/database/technologies/instant-client/downloads.html

rpm必须使用root用户安装，zip普通用户解压即可

注意这里使用的是64位客户端，同时强调一下，之前安装的rpm是32位的
rsync -rltDv /mnt/d/soft/instantclient-basic-linux.x64-12.2.0.1.0.zip ./
unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
    
export LD_LIBRARY_PATH=~/app/python/instantclient_12_2:$LD_LIBRARY_PATH

export LD_LIBRARY_PATH=/data/jupyter/instantclient_21_15:$LD_LIBRARY_PATH

python中设置LD_LIBRARY_PATH, 系统中设置后则不需要python重复设备

 
import os  

# 设置LD_LIBRARY_PATH环境变量  
# 注意：这里我们假设instantclient_12_2是你的Oracle Instant Client的路径  
oracle_client_path = "/home/ai-aml/app/python/instantclient_12_2"  
os.environ['LD_LIBRARY_PATH'] = oracle_client_path + ':' + os.environ.get('LD_LIBRARY_PATH', '')  
  
# 验证设置是否成功  
print("LD_LIBRARY_PATH:", os.environ['LD_LIBRARY_PATH'])  
  
# 现在你可以尝试导入cx_Oracle并连接到Oracle数据库了  
import cx_Oracle  
  
# 你的数据库连接代码...
    

  
如果设置了 LD_LIBRARY_PATH，则不需要下面的设置了，但在windows上可能还需要
# 如果报DPI-1047  找不到client的lib  用一下两句
#cx.init_oracle_client(lib_dir=r'/home/ai-aml/app/python/instantclient_12_2')