Python常用模块13-python的cx_Oracle模块

一.cx_Oracle模块介绍

1.1 pip安装cx_Oracle模块

pip安装

C:\Users\Administrator>pip install cx_Oracle
Collecting cx_Oracle
  Using cached cx_Oracle-8.1.0-cp36-cp36m-win_amd64.whl (210 kB)
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-8.1.0

C:\Users\Administrator>

1.2 pycharm安装cx_Oracle模块

需要注意的是 Python解释器将模块名称取为cx-Oracle而不是cx_Oracle
pycharm
File -> Settings -> project:项目名 -> Project Interpreter
然后选择 + ，在该项目下安装cx-Oracle即可

1.3 cx_Oracle模块常用语句

#!/usr/bin/env python
#-*-coding:utf-8-*-
 
import cx_Oracle as cx
 
#连接oracle
con = cx.connect('username/passwd@ip:port/ORACLE_SID')
#也可以写成如下形式
#con = cx.connect('username','passwd','ip:port/ORACLE_SID')
 
#获取游标
cur = con.cursor()
 
#执行sql语句
cur.execute('select * from 表名')
 
#若执行的是update/insert则主要执行commit
#con.commit()
 
#获取全部数据
data = cur.fetchall()
#获取单行数据可以用cur.fetchone()
#获取多行数据可以用cur.fetchmany(3)
 
#打印数据
for v in data:
    print(v)
 
#关闭游标和连接
cur.close()
con.close()

二.cx_Oracle实例

将Oracle里面的数据查询后到处到excel

代码:

import cx_Oracle as cx
from datetime import datetime
import xlsxwriter


# 创建Oracle连接

Host = "10.31.1.126:1521/pdb19c"
User = "zqs_user"
Password = "zqs_user"

conn = cx.connect(User,Password,Host)
cursor = conn.cursor()

sql1 = "select empno,ename , mgr,hiredate from emp where 1 = 1"
headers = ["empno","ename","mgr","hiredate"]

cursor.execute(sql1)

rows = cursor.fetchall()
fields = cursor.description # 获取列名

# 创建一个workbook和worksheet
workbook = xlsxwriter.Workbook('emp02.xlsx')
worksheet = workbook.add_worksheet()

# 新增一个粗体格式
bold = workbook.add_format({'bold': True})

# 写表头
worksheet.write('A1', 'empno', bold)
worksheet.write('B1', 'ename', bold)
worksheet.write('C1', 'mgr', bold)
worksheet.write('D1', 'hiredate', bold)

#数据坐标0,0 ~ row,col   row取决于：result的行数；col取决于fields的总数
for row in range(1,len(rows)+1):
    for col in range(0,len(fields)):
        worksheet.write(row,col,u'%s' % rows[row-1][col])
workbook.close()

# 关闭连接
cursor.close()
conn.close()

测试结果:
时间字段默认带了时分秒，然后数值型控制被设定为了None，这个有待改进

参考:

1.https://www.bbsmax.com/A/Ae5RwBqrdQ/