【干货攻略】dmPython连接数据库将查出的sql转化为excel表格

vlambda
2022-02-10

【干货攻略】dmPython连接数据库将查出的sql转化为excel表格

-----正文-----

在日常运维中，我们有时候需要将sql转化为excel表，本文介绍通过dmPython连接数据库进而将sql转化为excel表格的方法。

环境说明

名称
	192.168.3.90
DM数据库版本	DM Database Server x64 V7.6.0.161-Build(2019.05.23-107264)ENT
python版本	建议使用python3
dmpython版本	dmpython 2.3

1、部署dmPython,测试连接数据库

1.1、安装python --提前准备

1.2、安装DM数据库 --提前准备

安装目录：DM_HOME= /home/dmdba/dmdbms

1.3、安装DmPython(建议使用Dmpython的源码包，可联系达梦同事提供)

1.3.1 解压安装包

[root@localhost ~]# cd /root/python/pyodbc-4.0.30/build/lib.linux-x86_64-2.7[root@localhost lib.linux-x86_64-2.7]# unzip dmPython.zip[root@localhost lib.linux-x86_64-2.7]# cd dmPython

1.3.2 设置环境变量，避免安装报错缺少对应的库文件

export DM_HOME= /home/dmdba/dmdbmsexport LD_LIBRARY_PATH=$ LD_LIBRARY_PATH:/root/python/pyodbc-4.0.30/build/lib.linux-x86_64-2.7:/home/dmdba/dmdbms/bin

1.3.3 安装dmPython

[root@localhost dmPython]# python setup.py install

1.3.4测试连接数据库

#!/usr/bin/python# -*- coding: UTF-8 -*- import dmPythonimport sysreload(sys)sys.setdefaultencoding('utf-8')# 打开数据库连接conn = dmPython.connect(user='SYSDBA',password='SYSDBA',server='192.168.3.90',port=5236) # 使用cursor()方法获取操作游标cur = conn.cursor() # 如果数据表已经存在使用 execute() 方法删除表。cur.execute("DROP TABLE IF EXISTS EMPLOYEE") # 创建数据表SQL语句sql1 =""" CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT,  SEX CHAR(1), INCOME FLOAT )"""cur.execute(sql1)# 查询A表中的数据sql2 = ""u"select * from A"""cur.execute(sql2)A = cur.fetchone()#由于有乱码转换字符集B = [i.decode('utf-8') if isinstance(i, str) else i for i in A]case_list_righ = str(B).replace('u\'','\'')print case_list_righ.decode("unicode-escape")# 关闭数据库连接conn.close()

2、安装excel用到的模块setuptools-33.1.1 xlwt-1.3.0

2.1、下载链接：

链接：https://pan.baidu.com/s/1VsZY_zndVotC5NIEcr9QdA

提取码：1109

2.2、解压并安装：

[root@localhost ~]# cd setuptools-33.1.1[root@localhost setuptools-33.1.1]# python setup.py install[root@localhost ~]# cd xlwt-1.3.0[root@localhost xlwt-1.3.0]# python setup.py install

中间我安装setuptools-33.1.1过程中报错缺少zlib，可先yum install zlib*

3、测试sql语句转为excel

#!/usr/bin/python# -*- coding: UTF-8 -*- import dmPython import xlwt import datetime import timedef dm_execute(): # 连接 dm 数据库 conn = dmPython.connect(user='SYSDBA',password='SYSDBA',server='192.168.3.90',port=5236) curs = conn.cursor() sql = ''u'select * from A''' curs.execute(sql) rows = curs.fetchall()  w = xlwt.Workbook() style = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 为样式创建字体 font.name = u"微软雅黑" style.font = font # 为样式设置字体 sheet_name = 'student' + time.strftime("%Y-%m-%d") #定义sheet名称 filename = 'student_' + time.strftime("%Y-%m-%d") + '.xls' #定义excel名称  ws = w.add_sheet(sheet_name, cell_overwrite_ok=True)  # 将 title 作为 Excel 的列名 title = u"id,name,sex,phone,email,BM_ID" title = title.split(",") for i in range(len(title)): ws.write(0, i, title[i], style) # # 开始写入数据库查询到的数据 for i in range(len(rows)): row = rows[i] for j in range(len(row)): if row[j]: item = row[j] ws.write(i + 1, j, item, style)  # 写文件完成，开始保存xls文件 out_path = '/tmp/student_' + time.strftime("%Y-%m-%d") + '.xls' w.save(out_path) conn.close() return out_path  if __name__ == '__main__': out_path = dm_execute()