# -* - coding: UTF-8 -* - #! /usr/bin/python import pymysql import xlrd import xlwt from datetime import * print("-----------------------------------------") print("---------------Excel生成开始----------------") d1 = datetime.now().strftime("%Y-%m-%d %H:%M:%S") head_list = ['航班号','起飞机场','降落机场','计划起飞','计划落地','预计起飞','预计落地','实际起飞','延误']; style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00') style1 = xlwt.easyxf(num_format_str='D-MMM-YY') wb = xlwt.Workbook() ws = wb.add_sheet('统计') conn = pymysql.connect(user='root', passwd='root', host='172.19.10.30', db='efss') cur = conn.cursor() sql = """SELECT f.flno AS '航班号',f.adepe AS '起飞机场', f.adese AS '降落机场', DATE_FORMAT(DATE_ADD(f.sobt, INTERVAL 8 HOUR), '%H:%i') AS '计划起飞', DATE_FORMAT(DATE_ADD(f.sibt, INTERVAL 8 HOUR), '%H:%i') AS '计划落地', DATE_FORMAT(DATE_ADD(f.eobt, INTERVAL 8 HOUR), '%H:%i') AS '预计起飞', DATE_FORMAT(DATE_ADD(f.eldt, INTERVAL 8 HOUR), '%H:%i') AS '预计落地', DATE_FORMAT(DATE_ADD(f.atot, INTERVAL 8 HOUR), '%H:%i') AS '实际起飞', TIMESTAMPDIFF(MINUTE,f.sobt , f.atot ) AS '延误' FROM efss.flight_info f INNER JOIN efss.tis_flight_info t ON t.ifpsId = f.id WHERE f.sobt >= CONCAT(SUBSTR(DATE_FORMAT(DATE_SUB(NOW() ,INTERVAL 1 DAY),'%Y%m%d%H%i%s'),1,8),'1600') AND f.sobt < CONCAT(SUBSTR(DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'),1,8),'0230') AND f.adepe = 'ZSPD' AND (TIMESTAMPDIFF(MINUTE,f.sobt , f.atot ) > 30 OR ((f.sobt IS NOT NULL OR f.sobt='') AND (f.atot='' OR f.atot IS NULL))) GROUP BY f.id ORDER BY f.sobt ASC""" sql1 = sql.encode("utf-8").decode("latin1") cur.execute(sql1) for index in range(len(head_list)): ws.write(0, index, head_list[index], style0); for r in cur: print("row_number:" , (cur.rownumber) ) a = cur.rownumber; print("a=:" , a ) ws.write(int(a), 0, str(r[0]), style1) ws.write(int(a), 1, str(r[1]), style1) ws.write(int(a), 2, str(r[2]), style1) ws.write(int(a), 3, str(r[3]), style1) ws.write(int(a), 4, str(r[4]), style1) ws.write(int(a), 5, str(r[5]), style1) ws.write(int(a), 6, str(r[6]), style1) ws.write(int(a), 7, str(r[7]), style1) ws.write(int(a), 8, str(r[8]), style1) wb.save('/home/hdkg/python/'+ d1[0:10] +'.xls') cur.close() conn.close(); print("------------------Excel生成完成------------------") print("----------------------------------------------")