mysql数据库操作以及excel修改

# -*- coding: utf-8 -*-
import pymysql
from datetime import datetime
from datetime import timedelta
import time,yaml
import pdb,json

def get_key_by_elenment(element):
    
    key=''
    if element=='rain_20':
        key='PRE_DAY_20_20'
    elif element=='tem_ave':
        key='TEM_DAY_AVG'
    elif element=='tem_max':
        key='TEM_DAY_MAX'
    elif element=='tem_min':
        key='TEM_DAY_MIN'

    return key
def get_history_surf(start_time,end_time,element):

    key=get_key_by_elenment(element)
    #获取全国历史站点数据 目前能查 2001到2014年的历史数据 2739个地面站
    time1=datetime.strptime(start_time+'0000',"%Y%m%d%H%M%S")
    time2=datetime.strptime(end_time+'0000',"%Y%m%d%H%M%S")

    st_time=time1.strftime("%Y-%m-%d %H:%M:%S")
    ed_time=time2.strftime("%Y-%m-%d %H:%M:%S")
    
    # 打开数据库连接
    db = pymysql.connect("*****","user","pwd","表名", port=19100 )
    # 使用 cursor() 方法创建一个游标对象 cursor 1990-01-03 00:00:00
    cursor = db.cursor()
    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%'"
    # 使用 execute()  方法执行 SQL 查询 
    # pdb.set_trace()
    # print(st_time,ed_time)
    print(sql)
    cursor.execute(sql)
    # 使用 fetchone() 方法获取单条数据.
    data = cursor.fetchall()
    # for da in data[:] :
    #     print(da)

    db.close()

    return data
def get_stn_by_region(level,region):

    #判断区域 分别运行 不同的sql
    if level== 'area':
        info=get_yaml_data(r'E:\HUAXIN\huaxin-qifu\gww\yushuai\src\insure\region.yaml')
        
        if region in info:
            admincodes=info[region]['sid'] # .split(',')
            pid =info[region]['pid']
        else:
            print('wrong info') #stationId

        sql="select * from taiping_sta_info_surf_chn1 WHERE adminCode in (%s)"%(admincodes)
        print(sql)
        # pdb.set_trace()
    elif level=='pro':
        sql="select * from taiping_sta_info_surf_chn1 WHERE provinceCode in (%s)"%(region)
    elif level=='county':
        sql="select * from taiping_sta_info_surf_chn1 WHERE adminCode in (%s)"%(region)
    else:
        print('wrong input')

    db = pymysql.connect("*","*","*","*", port=19100 )
    # 使用 cursor() 方法创建一个游标对象 cursor 1990-01-03 00:00:00
    cursor = db.cursor()
    # 使用 execute()  方法执行 SQL 查询 
    # pdb.set_trace()
    cursor.execute(sql)
    # 使用 fetchone() 方法获取单条数据.
    data = cursor.fetchall()
    # for da in data[:] :
    #     print(da)
    # print ("Database version : %s " % data)
    # 关闭数据库连接
    db.close()

    return data



def get_yaml_data(yaml_file):
    # 打开yaml文件
    # print("***获取yaml文件数据***")
    file = open(yaml_file, 'r', encoding="utf-8")
    file_data = file.read()
    file.close()
    
    # print(file_data)
    # print("类型：", type(file_data))

    # 将字符串转化为字典或列表
    # print("***转化yaml数据为字典或列表***")
    # pdb.set_trace()
    data = yaml.load(file_data)
    # print(data)
    # print("类型：", type(data))
    # pdb.set_trace()
    return data

def main1():
    all_city={}
    with open(r'E:\HUAXIN\huaxin-qifu\gww\yushuai\src\insure\DATA\county1.txt','r', encoding="utf-8") as f:
        lines=f.readlines()
        for line in lines:          
            # pdb.set_trace()
            # print(line.split('\t')[1][1:] )
            all_city.update({line.split('\t')[1][1:]:line.split('\t')[0]})


    workbook=openpyxl.load_workbook(r'C:\Users\YS\Desktop\111.xlsx')
    
    worksheet=workbook.worksheets[0]

    for index,row in enumerate(worksheet.rows):
        
        if index==0:
            print(row[8].value)  #每一行的一个row[0]就是第一列
        else:
            # print(row[8].value)
            # pdb.set_trace()
            if row[8].value in all_city:
                row[5].value=all_city[row[8].value]
                print(row[8].value,all_city[row[8].value])
    
    workbook.save(filename=r'C:\Users\YS\Desktop\111.xlsx')

if __name__ == '__main__':

    # data=get_history_surf('2010101010','2010101110','tem_ave') # 接收时间段 要素 
    # data=get_stn_by_region('area','DongBei') # 传入区域返回对应区域的站点信息 参数  [area 区域名称]  [pro  admincode] [county  admincode]
    # data=get_stn_by_region('pro','110000') # 传入区域返回对应区域的站点信息 参数  area 区域  pro 省  county 县
    data=get_stn_by_region('county','110107') # 传入区域返回对应区域的站点信息 参数  area 区域  pro 省  county 县
    print(len(data))