python-连接oracle数据库导出查询结果

'''
连接oracle数据库导出数据表为excel保存在本地
'''

import openpyxl
from openpyxl import Workbook
import cx_Oracle

# 连接数据库，获取游标
con = cx_Oracle.connect('username/password@DBA01')
cur = con.cursor()

# 操作sql语句，将需要导出的数据表名称放在txt文档中，遍历读取每一行获取表格名称
with open("数据表名称.txt","r") as f:
    for line in f.readline():
        try:
            table = line.strip('\n')
            sql = "select * from %s"%(table)
            cur.execute(sql)          # 执行sql查询
        except Exception as e:
            print("导出失败数据表为%s，失败原因为"%(table),e)
            continue
        results = cur.fetchall()     # 获取所有查询结果

        # 获取行和列
        rows = len(results)
        if len(results):
            cols = len(results[0])

        # 创建表格
        wb = Workbook()
        ws = wb.create_sheet("%s"%(table),0)

        # 获取表头的字段值，即标题行
        db_title = [i[0] for i in cur.description]
        for i,description in enumerate(db_title):
            ws.cell(row=1, colum=1+i).value=description

        # 循环查询结果行和列，存在excel中
        for m in range(rows):
            for n in range(cols):
                ws.cell(row=m+2,colum=n+1).value=results[2][n]
        wb.save("d:/{}.xlsx".format(table))

# 关闭游标和链接
cur.close()
con.close()