练习：
写出一个报表统计,统计禅道内项目是会计云课堂2.0 下, 版本号为2.4.0 的bug内容,
报表标题: 日期 模块 已关闭 延期处理 block
代码：
1 import xlwt 2 import pymysql 3 4 #建立数据库连接及执行 5 def connect_db(sql): 6 #创建数据库连接 7 conn = pymysql.connect(host=" ", port=3307, user=\' \', passwd=\' \', db=\'zentao\',charset=\'utf8\') 8 cur = conn.cursor() # 建立游标 9 sql = sql 10 cur.execute(sql) #使用游标对象执行sql语句 11 r_result = cur.fetchall() #获取结果 12 cur.close() 13 conn.close() 14 return r_result 15 #普通sql封装 16 def sql_package(): 17 module_sql="Select id,name from zt_module where root=119 and parent in (0,1265) and id !=1265" 18 return module_sql 19 20 #参数拼接sql封装 21 def sql_package1(module_id,date): 22 closed_sql = "SELECT count(*) FROM zt_bug b inner join zt_module m on b.module=m.id where b.product=119 and b.openedBuild IN (355,356,357) and b.status=\'closed\' and (m.id={} or m.parent={} ) and date_format(b.openedDate,\'%Y-%m-%d\')=\'{}\'".format(module_id,module_id,date) 23 postponed_sql="SELECT count(*) FROM zt_bug b inner join zt_module m on b.module=m.id where b.product=119 and b.openedBuild IN (355,356,357) and b.resolution=\'postponed\'and (m.id={} or m.parent={}) and date_format(openedDate,\'%Y-%m-%d\')=\'{}\'".format(module_id,module_id,date) 24 block_sql="SELECT count(*) FROM zt_bug b inner join zt_module m on b.module=m.id where b.product=119 and b.openedBuild IN (355,356,357) and b.severity=1 and (m.id={} or m.parent={}) and date_format(openedDate,\'%Y-%m-%d\')=\'{}\'".format(module_id,module_id,date) 25 return closed_sql,postponed_sql,block_sql 26 #创建excel表 向表里写内容 27 def write_excel(path, sheetname,date): 28 wb = xlwt.Workbook(encoding=\'utf-8\') # 创建工作簿 29 ws = wb.add_sheet(sheetname) # 括号内参数为表名 30 title = [\'日期\', \'模块\', \'已关闭\', \'延期处理\', \'block\'] 31 tu = connect_db(sql_package()) #模块id name的元组 32 for j in range(0, len(title)): 33 ws.write(0, j, label=title[j]) #写标题 34 ws.write(1, 0, label=date) #写日期 35 for i in range(1,len(tu)+1): 36 ws.write(i,1,label=tu[i-1][1]) #写模块 37 closed = connect_db(sql_package1(tu[i-1][0],date)[0]) 38 ws.write(i,2, label=closed[0][0]) #写已关闭bug数 39 postponed = connect_db(sql_package1(tu[i - 1][0],date)[1]) 40 ws.write(i, 3, label=postponed[0][0]) # 写延期处理bug数 41 block = connect_db(sql_package1(tu[i - 1][0],date)[2]) 42 ws.write(i,4, label= block[0][0]) # 写block的bug数 43 44 wb.save(path) 45 46 47 write_excel(\'D:\\test.xls\', "禅道bug汇总","2020-05-14")