Python 导出 OceanBase表 python导出oracle数据

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

1 import openpyxl
 2 from openpyxl import Workbook
 3 import cx_Oracle
 4 
 5 # 连接数据库，获取游标
 6 con = cx_Oracle.connect('username/password@DBA01')
 7 cur = con.cursor()
 8 
 9 # 操作sql语句，将需要导出的数据表名称放在txt文档中，遍历读取每一行获取表格名称
10 with open("数据表名称.txt","r") as f:
11     for line in f.readline():
12         try:
13             table = line.strip('\n')
14             sql = "select * from %s"%(table)
15             cur.execute(sql)          # 执行sql查询
16         except Exception as e:
17             print("导出失败数据表为%s，失败原因为"%(table),e)
18             continue
19         results = cur.fetchall()     # 获取所有查询结果
20 
21         # 获取行和列
22         rows = len(results)
23         if len(results):
24             cols = len(results[0])
25 
26         # 创建表格
27         wb = Workbook()
28         ws = wb.create_sheet("%s"%(table),0)
29 
30         # 获取表头的字段值，即标题行
31         db_title = [i[0] for i in cur.description]
32         for i,description in enumerate(db_title):
33             ws.cell(row=1, colum=1+i).value=description
34 
35         # 循环查询结果行和列，存在excel中
36         for m in range(rows):
37             for n in range(cols):
38                 ws.cell(row=m+2,colum=n+1).value=results[2][n]
39         wb.save("d:/{}.xlsx".format(table))
40 
41 # 关闭游标和链接
42 cur.close()
43 con.close()