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

迷南。 2023-01-14 09:00 315阅读 0赞

### 文章目录 ###

*  一.cx\_Oracle模块介绍
 *   *  1.1 pip安装cx\_Oracle模块
     *  1.2 pycharm安装cx\_Oracle模块
     *  1.3 cx\_Oracle模块常用语句
 *  二.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，这个有待改进  
![image.png][]

# 参考: #

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

[image.png]: /images/20221022/88b0dd94fb914adab1d0db44034d8a32.png