用mysql+python自动生成统计报表

import xlrd
import xlwt
from xlutils.copy import copy
import pymysql

database=pymysql.connect("ip","username","pwd","sqlname",charset='utf8')

#初始化一个指针
cursor=database.cursor()
#需要查询的内容   GROUP BY company表示以公司名进行分组
sql="SELECT company,COUNT(company),SUM(weight),SUM(weight*price)FROM  data  GROUP BY company"
#运行sql语句
cursor.execute(sql)
#结果存储在result里面
result=cursor.fetchall()

#读取数据
for i in result:
	if i[0]=='张三粮配':
		a_num=i[1]
		a_weight=i[2]
		a_total_price=i[3]
	elif i[0]=='李四粮食':
		b_num=i[1]
		b_weight=i[2]
		b_total_price=i[3]
	elif i[0]=='王五小麦':
		c_num=i[1]
		c_weight=i[2]
		c_total_price=i[3]
	elif i[0]=='赵六麦子专营':
		d_num=i[1]
		d_weight=i[2]
		d_total_price=i[3]

tem_excel=xlrd.open_workbook('path'.formatting_info=True)
tem_sheet=tem_excel.sheet_by_index(0)

new_excel=copy(tem_excel)
new_sheet=new_excel.get_sheet(0)

style=xlwt.XFStyle()

font=xlwt.Font()
font.name='微软雅黑'
font.bold=True
#字体的高度等于  字号*20
font.height=360

style.font=font
borders=xlwt.Borders()
borders.top=xlwt.Borders.THIN
borders.bottom=xlwt.Borders.THIN
borders.left=xlwt.Borders.THIN
borders.right=xlwt.Borders.THIN
style.borders=borders

#对齐
alignment=xlwt.Alignment()
#水平
alignment.horz=xlwt.Alignment.HORZ_CENTER
#竖直
alignment.vert=xlwt.Alignment.VERT_CENTER
style.alignment=alignment


new_sheet.write(2,1,len(a_weight),style)
new_sheet.write(2,2,round(sum(a_weight),2),style)
new_sheet.write(2,2,round(sum(a_total_price),2),style)
new_sheet.write(2,1,len(b_weight),style)
new_sheet.write(2,2,round(sum(b_weight),2),style)
new_sheet.write(2,2,round(sum(b_total_price),2),style)
new_sheet.write(2,1,len(c_weight),style)
new_sheet.write(2,2,round(sum(c_weight),2),style)
new_sheet.write(2,2,round(sum(c_total_price),2),style)
new_sheet.write(2,1,len(d_weight),style)
new_sheet.write(2,2,round(sum(d_weight),2),style)
new_sheet.write(2,2,round(sum(d_total_price),2),style)

new_excel.save('path')