python连接Oracle数据库
查看Oracle版本
select * from v$version
下载对应版本的InstantClient
安装oracle client
Windows
InstantClient
1.解压InstantClient
2.环境变量
3.将其解压目录下的oci.dll、oraocieixx.dll、oraoccixx.dll文件复制到python安装目录的Lib/site-packages文件夹下
Linux
当前环境
cat /etc/redhat-release
安装对应的rpm oracle client
rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
配置环境变量
vi /root/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export ORACLE_HOME=/usr/lib/oracle/12.1/client64/lib
export LD_LIBRARY_PATH=$LD_LIBARARY_PATH:$ORACLE_HOME
export PATH
source /root/.bash_profile
安装cx_Oracle驱动包
pip install 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'
参考
__EOF__
