excel使用mysql数据库查询_mysql数据库操作以及excel修改-CSDN博客

本文链接：https://blog.csdn.net/weixin_35645077/article/details/113334496

1 #-*- coding: utf-8 -*-

2 importpymysql3 from datetime importdatetime4 from datetime importtimedelta5 importtime,yaml6 importpdb,json7

8 defget_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'

20 returnkey21 defget_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)

46 db.close()47

48 returndata49 defget_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 ininfo:56 admincodes=info[region]['sid'] #.split(',')

57 pid =info[region]['pid']58 else:59 print('wrong info') #stationId

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 returndata86

89 defget_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))

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 returndata107

108 defmain1():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 inlines: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 inenumerate(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 inall_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