python调用mssql存储过程，生成图表，合成图片，定时发送到企业微信群
-----------------------实用主义的非典型IT搬运工-----------------------
之前用python写过一段脚本，实现从数据库提取数据，然后定时调用企业微信群机器人的Webhook接口发送到企业微信群。单纯看文字肯定没有看图表舒服，所以就利用matplotlib模块，把取出的数据生成图表，再发送图片消息。一个图一个图地发当然不如把图表放到一张图上看着好看，于是改进了一下，把几条曲线放在一张图表中。为了更美观一点，我又做了个背景图，把生成的图表与背景图合并成一张图再发送到群，这样看起来就没那么“粗糙”了，可以稍微满足一下完美主义的女性同事对于视觉感观的要求。最终发送的图片消息如下（真实数据，怕有泄密风险，所以有码）：
实现这样的功能，虽然不算复杂，但是也用到了几个python高频使用的功能模块，所以记录一下，以此为基础可以举一反三，可以完成更多的更复杂的实用功能脚本，希望对有类似需求的朋友有所帮助吧。
先捋一下用到了python的哪些模块：
import time # 时间模块，用来按时间生成文件名用的
import pymssql # 用于读取mssql数据库
import requests # 用于请求Webhook接口发消息
import matplotlib.pyplot as plt # 用于图表的生成
import hashlib # 企业微信群机器人的图表消息需要把图片文件的hash值
import os # 磁盘文件读写
import base64 # 企业微信群机器人图片消息需要生成图片的base64值
from PIL import Image # 这个是用来处理图片的
我的脚本编写和运行的环境是：win10，python3.7.4，spyder3.3.6，spyder-kernels.5.2。之所以要说一下运行环境，是因为我在编写测试脚本没有问题以后，把脚本拷到另一台作为消息发送服务器的电脑上，结果运行到读取数据库存储过程的时候报错。最后把服务器环境重装，按照开发环境重新配置就没问题了。应该是pymssql模块对于python3不同版本有兼容问题。
模块安装可以用pip 命令的方式，选择国内镜像安装会比较快：
安装pymssql模块
pip install -i https://pypi.douban.com/simple pymssql==2.1.4
安装pillow模块
pip install -i https://pypi.douban.com/simple pillow
安装spydeer
pip install -i https://pypi.douban.com/simple spyder==3.3.6
当然也可以通过anaconda来安装spyder
这个程序的系统流程如下：
使用pymssql从mssql数据库查询数据可以有两种方式，一是建立连接后使用SQL语句查询，可以查表或视图。第二种方式是调用mssql中的存储过程，这个适用于需要用到复杂的sql语句提取数据的情况。我的脚本中使用的是无参数的存储过程调用。
def querySQL():
# 数据库连接配置
config_dict = {
'user': 'sa',
'password': '********',
'host': '192.*********',
'database': '**********'
}
def conn():
connect = pymssql.connect(**config_dict)
if connect:
print("connect success!!!")
return connect
else:
print("连接失败！请检查配置信息！")
conn = conn()
cursor = conn.cursor()
# 调用存储过程，无参数传入，返回数据和数据列数
cursor.callproc('sp_*************') # 调用存储过
cursor.nextset() # 这个地方只有这么写才有效，直接用fetchall()没有任何结果
results = cursor.fetchall()
conn.commit()
col=len(results[0]) # 取得字段数
return results, col # 返回数据和字段数，字段数用于生成消息的时候可以控制循环
cursor.close()
conn.close()
数据取回以后就是生成图表的过程了，我没有写成函数，直接写到主程序里了，应该可以写成函数
resultdata, col = querySQL()
# print(resultdata)
# print(col) 查询到的表的列名
datelist = []
for i in range(len(resultdata)):
datelist.append(resultdata[i][0])
# print(datelist)
saleslist = []
storeNums = []
saleOrders = []
newMember = []
oldMember = []
for j in range(len(resultdata)):
saleslist.append(int(resultdata[j][1])) # print(saleslist) # 销售数据清单
storeNums.append(int(resultdata[j][2]))
saleOrders.append(int(resultdata[j][3]))
newMember.append(int(resultdata[j][4]))
oldMember.append(int(resultdata[j][5]))
# 刻度和序列值
x_data = datelist
y_data = saleslist
# plt.plot(x_data, y_data)
# 设置画布大小
fig = plt.figure(num = 1, figsize=(10, 14))
# 此处figsize=(x,y)参数代表的意思是图表的大小，默认分辨率是100，那么(10,14)代表的图片大小是1000*1400
ax1 = fig.add_subplot(411) # 在画布中增加子图表，(411)代表把画布分成4行1列区域，在1区域绘图
ax1.set_title("最近7日门店销售业绩")
ax1.plot(x_data, y_data, label='金额', linewidth=3, color='black', marker='o', markerfacecolor='r',
markersize=10) # 标记点
ax1.set_ylabel("业绩额",fontsize=16) # 添加y轴标签，设置字体大小为16
# 设置数字标签
for a, b in zip(x_data, y_data):
ax1.text(a, b, b, ha='center', va='bottom', fontsize=14) # 显示折线点数值
# 店数图表
# storeNums = [138,117,134,185,194,144,102]
ax2 = fig.add_subplot(412)
ax2.set_title("最近7日有业绩店数")
ax2.plot(x_data ,storeNums , label='有业绩店数', linewidth=3, color='r', marker='o', markerfacecolor='b')
ax2.set_ylabel("店数",fontsize=16)#添加y轴标签，设置字体大小为16
for a, b in zip(x_data, storeNums):
ax2.text(a, b, b, ha='center', va='bottom', fontsize=14) # 显示折线点数值
# 单数图表
ax3 = fig.add_subplot(413)
ax3.set_title("最近7日订单数")
ax3.plot(x_data ,saleOrders , label='订单数', linewidth=3, color='green', marker='o', markerfacecolor='b')
ax3.set_ylabel("单数",fontsize=16)#添加y轴标签，设置字体大小为16
for a, b in zip(x_data, saleOrders):
ax3.text(a, b, b, ha='center', va='bottom', fontsize=14) # 显示折线点数值
# 会员图表
ax4 = fig.add_subplot(414)
ax4.set_title("最近7日消费会员数")
ax4.plot(x_data, newMember , label='新消费会员数', linewidth=3, color='b', marker='o', markerfacecolor='r')
ax4.plot(x_data, oldMember , label='老消费会员数', linewidth=3, color='c', marker='o', markerfacecolor='r')
ax4.set_ylabel("消费会员",fontsize=16)#添加y轴标签，设置字体大小为16
plt.legend(labels=['新会员数','老会员数'],loc='best')
for a, b, c in zip(x_data, newMember,oldMember):
ax4.text(a, b, b, ha='center', va='bottom', fontsize=14) # 显示折线点数值
ax4.text(a, c, c, ha='center', va='bottom', fontsize=14) # 显示第二条线的数值
plt.subplots_adjust(wspace =0, hspace =0.5)#调整子图间距
# 取当前时间为文件名
pic_full_name = './' + time.strftime("%Y%m%d%H%M%S", time.localtime()) + '.jpg'
plt.savefig(pic_full_name)
接下来就是把生成的图表再拼合到一个背景图中
# 把报告合成到背景图里面
report_img_name = pic_full_name + 'rp.png'
bg_img = Image.open("reportbg.png")
rp_img = Image.open(pic_full_name)
rp_img_x = (bg_img.size[0]-rp_img.size[0])//2
rp_img_position = (rp_img_x, 155, int(rp_img_x + rp_img.size[0]), int(155 + rp_img.size[1]))
# bg_img.paste(rp_img,rp_img_position)
bg_img.paste(rp_img,(rp_img_x,155))
# bg_img.show()
bg_img.save(report_img_name)
最后就是按照企业微信群机器人的消息格式要求，生成消息内容并发送
# 以下生成图片消息
pic_md5 = get_file_md5(report_img_name)
pic_base64s = get_file_base64(report_img_name)
# plt.show() # 显示图表
out_mk_msg = "### 最近七天市场业绩：\n"
for i in range(len(resultdata)):
out_mk_msg = out_mk_msg + r">日期:%s , 业绩:<font color = \"warning\">%d</font> , 店数:%s , 单数:%s, 新会员：%s , 老会员：%s " % (
resultdata[i][0], resultdata[i][1], resultdata[i][2], resultdata[i][3], resultdata[i][4], resultdata[i][5]) + "\n"
out_mk_msg = '{"content": "%s"}' % out_mk_msg
# print(out_mk_msg)
# 调用postmsg向接口提交数据，分别提并markdwon格式及图片格式消息
result = postmsg(url, out_mk_msg, "markdown")
# print(result)
out_pic_msg = '{"base64":"%s", "md5":"%s"}' % (pic_base64s, pic_md5)
result = postmsg(url, out_pic_msg, "image") # 发送消息
以下是完整代码，把它加到windows任务计划管理器中就可以实现定时从数据库取数并发到企业微信群里了
# -*- coding: utf-8 -*-
# s = '中文' # 注意这里的 str 是 str 类型的，而不是 unicode
# s.encode('gb2312')
# https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=a82174fa-eeed-45de-bf50-d70e6877ac29 test机器人
import time
import pymssql
import requests
import matplotlib.pyplot as plt
import hashlib
import os
import base64
from PIL import Image
plt.rcParams['font.sans-serif'] = ['SimHei'] # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False # 用来正常显示负号
# 获取文件的Base64编码
def get_file_base64(filepath):
if not os.path.isfile(filepath):
return
with open(filepath, "rb") as f:
image = f.read()
image_base64 = str(base64.b64encode(image), encoding='utf-8') # 这里要说明编码，否则不成功
return image_base64
# 获取文件md5函数
def get_file_md5(filepath):
# 获取文件的md5
if not os.path.isfile(filepath):
return
myhash = hashlib.md5()
f = open(filepath, "rb")
while True:
b = f.read(8096)
if not b:
break
myhash.update(b)
f.close
# print(myhash.hexdigest())
return myhash.hexdigest()
# 发送消息函数, msgtype定义：text 发送字符串消息，markdown 发送图片消息，image 发送图片消息， news 发送图文消息
def postmsg(url, post_data, msgtype):
# sss = "这是一条用python发送的测试信息，请忽略！"
post_data = '{"msgtype" : "%s", "%s" : %s}' % (msgtype, msgtype, post_data)
# post_data = '{"msgtype": "markdown","markdown": {"content": "%s"}}' % sss
# print(post_data)
if url == '':
print('URL地址为空！')
else:
r = requests.post(url, data=post_data.encode())
rstr = r.json()
if r.status_code == 200 and 'error' not in rstr:
result = '发送成功'
return result
else:
return 'Error'
def querySQL():
# 数据库连接配置
config_dict = {
'user': 'sa',
'password': '*********',
'host': '192.168.2.*****',
'database': '*******'
}
def conn():
connect = pymssql.connect(**config_dict)
if connect:
print("connect success!!!")
return connect
else:
print("连接失败！请检查配置信息！")
conn = conn()
cursor = conn.cursor()
# 调用 p1 存储过程，无参数传入，返回数据和数据列数
cursor.callproc('sp_************')
cursor.nextset()
results = cursor.fetchall()
conn.commit()
col=len(results[0])
return results, col
cursor.close()
conn.close()
# print(len(results[0]))
# print(results)
if __name__ == '__main__':
url = "https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=******************************" # Webhook地址
resultdata, col = querySQL()
# print(resultdata)
# print(col) 查询到的表的列名
datelist = []
for i in range(len(resultdata)):
datelist.append(resultdata[i][0])
# print(datelist)
saleslist = []
storeNums = []
saleOrders = []
newMember = []
oldMember = []
for j in range(len(resultdata)):
saleslist.append(int(resultdata[j][1])) # print(saleslist) # 销售数据清单
storeNums.append(int(resultdata[j][2]))
saleOrders.append(int(resultdata[j][3]))
newMember.append(int(resultdata[j][4]))
oldMember.append(int(resultdata[j][5]))
# 刻度和序列值
x_data = datelist
y_data = saleslist
# plt.plot(x_data, y_data)
# 设置画布大小
fig = plt.figure(num = 1, figsize=(10, 14))
ax1 = fig.add_subplot(411)
ax1.set_title("最近7日门店销售业绩")
ax1.plot(x_data, y_data, label='金额', linewidth=3, color='black', marker='o', markerfacecolor='r',
markersize=10) # 标记点
ax1.set_ylabel("业绩额",fontsize=16) # 添加y轴标签，设置字体大小为16
# 设置数字标签
for a, b in zip(x_data, y_data):
ax1.text(a, b, b, ha='center', va='bottom', fontsize=14) # 显示折线点数值
# 店数图表
ax2 = fig.add_subplot(412)
ax2.set_title("最近7日有业绩店数")
ax2.plot(x_data ,storeNums , label='有业绩店数', linewidth=3, color='r', marker='o', markerfacecolor='b')
ax2.set_ylabel("店数",fontsize=16)#添加y轴标签，设置字体大小为16
for a, b in zip(x_data, storeNums):
ax2.text(a, b, b, ha='center', va='bottom', fontsize=14) # 显示折线点数值
# 单数图表
ax3 = fig.add_subplot(413)
ax3.set_title("最近7日订单数")
ax3.plot(x_data ,saleOrders , label='订单数', linewidth=3, color='green', marker='o', markerfacecolor='b')
ax3.set_ylabel("单数",fontsize=16)#添加y轴标签，设置字体大小为16
for a, b in zip(x_data, saleOrders):
ax3.text(a, b, b, ha='center', va='bottom', fontsize=14) # 显示折线点数值
# 会员图表
ax4 = fig.add_subplot(414)
ax4.set_title("最近7日消费会员数")
ax4.plot(x_data, newMember , label='新消费会员数', linewidth=3, color='b', marker='o', markerfacecolor='r')
ax4.plot(x_data, oldMember , label='老消费会员数', linewidth=3, color='c', marker='o', markerfacecolor='r')
ax4.set_ylabel("消费会员",fontsize=16)#添加y轴标签，设置字体大小为16
plt.legend(labels=['新会员数','老会员数'],loc='best')
for a, b, c in zip(x_data, newMember,oldMember):
ax4.text(a, b, b, ha='center', va='bottom', fontsize=14) # 显示折线点数值
ax4.text(a, c, c, ha='center', va='bottom', fontsize=14) # 显示第二条线的数值
plt.subplots_adjust(wspace =0, hspace =0.5)#调整子图间距
# 取当前时间为文件名
pic_full_name = './' + time.strftime("%Y%m%d%H%M%S", time.localtime()) + '.jpg'
plt.savefig(pic_full_name)
# 把报告合成到背景图里面
report_img_name = pic_full_name + 'rp.png'
bg_img = Image.open("reportbg.png")
rp_img = Image.open(pic_full_name)
rp_img_x = (bg_img.size[0]-rp_img.size[0])//2
rp_img_position = (rp_img_x, 155, int(rp_img_x + rp_img.size[0]), int(155 + rp_img.size[1]))
# bg_img.paste(rp_img,rp_img_position)
bg_img.paste(rp_img,(rp_img_x,155))
# bg_img.show()
bg_img.save(report_img_name)
# 以下生成图片消息
pic_md5 = get_file_md5(report_img_name)
pic_base64s = get_file_base64(report_img_name)
# plt.show() # 显示图表
out_mk_msg = "### 最近七天市场业绩：\n"
for i in range(len(resultdata)):
out_mk_msg = out_mk_msg + r">日期:%s , 业绩:<font color = \"warning\">%d</font> , 店数:%s , 单数:%s, 新会员：%s , 老会员：%s " % (
resultdata[i][0], resultdata[i][1], resultdata[i][2], resultdata[i][3], resultdata[i][4], resultdata[i][5]) + "\n"
out_mk_msg = '{"content": "%s"}' % out_mk_msg
# print(out_mk_msg)
# 调用postmsg向接口提交数据，分别提并markdwon格式及图片格式消息
result = postmsg(url, out_mk_msg, "markdown")
# print(result)
out_pic_msg = '{"base64":"%s", "md5":"%s"}' % (pic_base64s, pic_md5)
result = postmsg(url, out_pic_msg, "image") # 发送消息
# print(result)
这个脚本虽然是针对企业微信的群机器人写的，但思路和方法也适用于其它很多类似场景。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
