# -*- coding: utf-8 -*-
"""
@use:查询17.11-18.1,18.6-18.8的PM2.5数据,导出到csv
"""
import pymssql
import xlwt
import datetime
from xml.dom.minidom import parse
def do():
try:
doc=parse('baseinfo.xml')
login=doc.getElementsByTagName("login")[0]
ip=login.getAttribute("ip")
user=login.getAttribute("user")
password=login.getAttribute("password")
database=login.getAttribute("database")
timerange=doc.getElementsByTagName("timerange")[0]
start=timerange.getAttribute("start")
end=timerange.getAttribute("end")
start1 =datetime.datetime.strptime(start, '%Y-%m-%d %H:%M:%S')
end1 = datetime.datetime.strptime(end, '%Y-%m-%d %H:%M:%S')
theHour=int((end1-start1).days*24 + (end1-start1).seconds/3600)+1
print(theHour)
conn=pymssql.connect(host=ip,user=user,password=password,database=database)
#查看连接是否成功
cursor = conn.cursor()
# sql = "select a.STATIONCODE,a.MONIDATE,b.LONGITUDE,b.LATITUDE,a.PM25 from T_MON_AIRSTATIONHOUR a join T_BAS_AIRSTATION b on a.STATIONCODE=b.STATIONCODE where MONIDATE='2018-09-21 15:00:00.000'"
#日期区间数据查询
#sql = "select a.STATIONCODE,a.MONIDATE,b.LONGITUDE,b.LATITUDE,a.PM25 from T_MON_AIRSTATIONHOUR a join T_BAS_AIRSTATION b on a.STATIONCODE=b.STATIONCODE where MONIDATE>='"+start+"' and MONIDATE<='"+end+"'"
for h in range(theHour):
monidate=(start1 + datetime.timedelta(hours=h)).strftime('%Y-%m-%d %H:%M:%S')
# print(monidate)
#单个时间数据查询
# sql = "select a.STATIONCODE,a.MONIDATE,b.LONGITUDE,b.LATITUDE,a.PM25 from T_MON_AIRSTATIONHOUR a join T_BAS_AIRSTATION b on a.STATIONCODE=b.STATIONCODE where MONIDATE='"+monidate+"'"
sql="select a.ID,a.RecordTime,b.LNG1,b.LAT1,a.PM25 from T_MIC_DATAAIR a join T_MIC_STATION b on a.ID=b.ID where b.STATIONTYPE='2' and a.RecordTime='"+monidate+"'"
#时间列,转换成 2018-09-21T15:00:00 格式
str_date=str(datetime.datetime.strptime(str(monidate), "%Y-%m-%d %H:%M:%S") )
str_date=str_date[0:10]+'T'+str_date[-8:]
cursor.execute(sql)
#用一个rs变量获取数据
rs = cursor.fetchall()
#创建文件
file = xlwt.Workbook()
#写入sheet名称,2017-05-01T06_00_00 格式
name_sheet=str_date[0:10]+'T'+str_date[-8:-6]+'_00_00'
table = file.add_sheet(name_sheet)
title=['deviceid','date','lon','lat','pm25']
#table.write(行_0开始, 列_0开始, 值)
#写入表头,第一行
for j in range(5):
table.write(0, j, title[j])
#index_i+1 行,index_j列
for index_i,r in enumerate(rs):
for index_j,val in enumerate(r):
if index_j== 1:
val1=str(datetime.datetime.strptime(str(val), "%Y-%m-%d %H:%M:%S") )
str1=val1[0:10]+'T'+val1[-8:]
table.write(index_i+1, index_j, str1)
# '2018-09-21T15:00:00'
else:
table.write(index_i+1, index_j, val)
#保存文件,2018-09-21T15_00_00.csv 名称格式
name_file=name_sheet+'.csv'
file.save(name_file)
except Exception as e:
print(e)
cursor.close()
conn.close()
return
cursor.close()
conn.commit()
conn.close()