python3.5.2 mysql Exccel

最新推荐文章于 2023-03-27 20:34:27 发布

东北小硬人

最新推荐文章于 2023-03-27 20:34:27 发布

阅读量562

点赞数

分类专栏：技术文档文章标签： python mysql

本文链接：https://blog.csdn.net/alibert/article/details/52090798

版权

技术文档专栏收录该内容

23 篇文章 0 订阅

订阅专栏

# -* - 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("----------------------------------------------")