【python】python連接Oracle數據庫

• 2022 年 5 月 21 日
• 筆記
• Python

python連接Oracle數據庫

查看Oracle版本

select * from v$version

下載對應版本的InstantClient

下載網址

InstantClient

1.解壓InstantClient

2.環境變量

3.將其解壓目錄下的oci.dll、oraocieixx.dll、oraoccixx.dll文件複製到python安裝目錄的Lib/site-packages文件夾下

安裝cx_Oracle驅動包

pip insatll cx_Oracle

Python與Oracle交互操作

#連接
import cx_Oracle
db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl")

#配置監聽並連接
import cx_Oracle
moniter = cx_Oracle.makedsn('192.168.2.1',1521,'orcl')
db = cx_Oracle.connect('scott','a123456',moniter)

查詢一條記錄

import cx_Oracle
# 注意：一定要加下面這兩行代碼，負責會中文亂碼；
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl")
cursor = db.cursor()

cursor.execute('select count(*) from emp1')
aa = cursor.fetchone()
print(aa)
cursor.execute('select ename,deptno,sal from emp1')     
for i in range(aa[0]):
    a,b,c = cursor.fetchone()
    d = "我的名字叫{}，所在部門是{}，工資是{}美元".format(a,b,c)
    display(d)
db.close()

獲取所有記錄

import cx_Oracle
# 注意：一定要加下面這兩行代碼，負責會中文亂碼；
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl")
cursor = db.cursor()

cursor.execute('select ename,deptno,sal from emp1')    
aa = cursor.fetchall()
# print(aa)
for a,b,c in aa:
    d = "我的名字叫{}，所在部門是{}，工資是{}美元".format(a,b,c)
    display(d)
db.close()

轉換為DataFrame

import cx_Oracle
import pandas as pd
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl")
cursor = db.cursor()

df1 = pd.read_sql("select * from emp where deptno=20",db)
display(df1)

df2 = pd.read_sql("select * from emp where deptno=30",db)
display(df2)

解決中文亂碼問題

方法一
C:\Users\AA>set nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

方法二
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

參考

• //blog.csdn.net/qq_40907977/article/details/107906057