1 # -*- coding: utf-8 -*-
2 import pymysql
3 from datetime import datetime
4 from datetime import timedelta
5 import time,yaml
6 import pdb,json
7
8 def get_key_by_elenment(element):
9
10 key=''
11 if element=='rain_20':
12 key='PRE_DAY_20_20'
13 elif element=='tem_ave':
14 key='TEM_DAY_AVG'
15 elif element=='tem_max':
16 key='TEM_DAY_MAX'
17 elif element=='tem_min':
18 key='TEM_DAY_MIN'
19
20 return key
21 def get_history_surf(start_time,end_time,element):
22
23 key=get_key_by_elenment(element)
24 #获取全国历史站点数据 目前能查 2001到2014年的历史数据 2739个地面站
25 time1=datetime.strptime(start_time+'0000',"%Y%m%d%H%M%S")
26 time2=datetime.strptime(end_time+'0000',"%Y%m%d%H%M%S")
27
28 st_time=time1.strftime("%Y-%m-%d %H:%M:%S")
29 ed_time=time2.strftime("%Y-%m-%d %H:%M:%S")
30
31 # 打开数据库连接
32 db = pymysql.connect("*****","user","pwd","表名", port=19100 )
33 # 使用 cursor() 方法创建一个游标对象 cursor 1990-01-03 00:00:00
34 cursor = db.cursor()
35 sql="select STATIONID ,LAT,LON,ALT,D_DATETIME,%s from history_surf_cli_chn_mul_day where D_DATETIME between ' %s' and '%s'" %(key,st_time,ed_time) +" and STATIONID like '5%'"
36 # 使用 execute() 方法执行 SQL 查询
37 # pdb.set_trace()
38 # print(st_time,ed_time)
39 print(sql)
40 cursor.execute(sql)
41 # 使用 fetchone() 方法获取单条数据.
42 data = cursor.fetchall()
43 # for da in data[:] :
44 # print(da)
45
46 db.close()
47
48 return data
49 def get_stn_by_region(level,region):
50
51 #判断区域 分别运行 不同的sql
52 if level== 'area':
53 info=get_yaml_data(r'E:\HUAXIN\huaxin-qifu\gww\yushuai\src\insure\region.yaml')
54
55 if region in info:
56 admincodes=info[region]['sid'] # .split(',')
57 pid =info[region]['pid']
58 else:
59 print('wrong info') #stationId
60
61 sql="select * from taiping_sta_info_surf_chn1 WHERE adminCode in (%s)"%(admincodes)
62 print(sql)
63 # pdb.set_trace()
64 elif level=='pro':
65 sql="select * from taiping_sta_info_surf_chn1 WHERE provinceCode in (%s)"%(region)
66 elif level=='county':
67 sql="select * from taiping_sta_info_surf_chn1 WHERE adminCode in (%s)"%(region)
68 else:
69 print('wrong input')
70
71 db = pymysql.connect("*","*","*","*", port=19100 )
72 # 使用 cursor() 方法创建一个游标对象 cursor 1990-01-03 00:00:00
73 cursor = db.cursor()
74 # 使用 execute() 方法执行 SQL 查询
75 # pdb.set_trace()
76 cursor.execute(sql)
77 # 使用 fetchone() 方法获取单条数据.
78 data = cursor.fetchall()
79 # for da in data[:] :
80 # print(da)
81 # print ("Database version : %s " % data)
82 # 关闭数据库连接
83 db.close()
84
85 return data
86
87
88
89 def get_yaml_data(yaml_file):
90 # 打开yaml文件
91 # print("***获取yaml文件数据***")
92 file = open(yaml_file, 'r', encoding="utf-8")
93 file_data = file.read()
94 file.close()
95
96 # print(file_data)
97 # print("类型：", type(file_data))
98
99 # 将字符串转化为字典或列表
100 # print("***转化yaml数据为字典或列表***")
101 # pdb.set_trace()
102 data = yaml.load(file_data)
103 # print(data)
104 # print("类型：", type(data))
105 # pdb.set_trace()
106 return data
107
108 def main1():
109 all_city={}
110 with open(r'E:\HUAXIN\huaxin-qifu\gww\yushuai\src\insure\DATA\county1.txt','r', encoding="utf-8") as f:
111 lines=f.readlines()
112 for line in lines:
113 # pdb.set_trace()
114 # print(line.split('\t')[1][1:] )
115 all_city.update({line.split('\t')[1][1:]:line.split('\t')[0]})
116
117
118 workbook=openpyxl.load_workbook(r'C:\Users\YS\Desktop\111.xlsx')
119
120 worksheet=workbook.worksheets[0]
121
122 for index,row in enumerate(worksheet.rows):
123
124 if index==0:
125 print(row[8].value) #每一行的一个row[0]就是第一列
126 else:
127 # print(row[8].value)
128 # pdb.set_trace()
129 if row[8].value in all_city:
130 row[5].value=all_city[row[8].value]
131 print(row[8].value,all_city[row[8].value])
132
133 workbook.save(filename=r'C:\Users\YS\Desktop\111.xlsx')
134
135 if __name__ == '__main__':
136
137 # data=get_history_surf('2010101010','2010101110','tem_ave') # 接收时间段 要素
138 # data=get_stn_by_region('area','DongBei') # 传入区域返回对应区域的站点信息 参数 [area 区域名称] [pro admincode] [county admincode]
139 # data=get_stn_by_region('pro','110000') # 传入区域返回对应区域的站点信息 参数 area 区域 pro 省 county 县
140 data=get_stn_by_region('county','110107') # 传入区域返回对应区域的站点信息 参数 area 区域 pro 省 county 县
141 print(len(data))
