定时的实现方式之一，excel入库

2019独角兽企业重金招聘Python工程师标准>>>

# -*- coding: utf-8 -*-
import pymysql
import xlrd
import uuid
import os
import re
import time
from xlrd import XLRDError

# 数据库配置
MYSQL_HOST = "mysql对应的云服务器的地址"
MYSQL_DATABASE = "数据库名称"
MYSQL_USER = "用户名"
MYSQL_PASSWORD = "密码"
MYSQL_PORT = 8029#端口


area_map={
"鲍山分局":"历城区",
"高新技术产业开发区分局":"历城区",
"公共交通分局":"市中区",
"槐荫区分局":"槐荫区",
"历城区分局":"历城区",
"历下区分局":"历下区",
"南部山区分局 ":"历城区",
"山东省济阳县公安局":"济阳区",
"山东省平阴县公安局":"平阴县",
"山东省商河县公安局":"商河县",
"山东省章丘市公安局":"章丘区",
"市中区分局":"市中区",
"天桥区分局":"天桥区",
"长清区分局":"长清区",
"山东省济南市公安局历下区分局":"历下区",
"山东省济南市公安局市中区分局":"市中区",
"山东省济南市公安局槐荫区分局":"槐荫区",
"山东省济南市公安局天桥区分局":"天桥区",
"山东省济南市公安局历城区分局":"历城区",
"山东省济南市公安局长清区分局":"长清区",
"山东省济南市公安局南部山区分局":"历城区",
"山东省济南市公安局高新技术产业开发区分局":"历城区",
"山东省济南市公安局鲍山分局":"历城区",
}

class MysqlHelper(object):
    def __init__(self):
        self.__host = MYSQL_HOST
        self.__user = MYSQL_USER
        self.__database = MYSQL_DATABASE
        self.__port = MYSQL_PORT
        self.__password = MYSQL_PASSWORD
        self.__conn = pymysql.connect(host=self.__host, user=self.__user, password=self.__password,
                                      database=self.__database, charset='utf8', port=self.__port)
        self.__cursor = self.__conn.cursor()

    def mysql_insert(self, sql, data):
        self.__cursor.execute(sql, data)
        self.__conn.commit()

    def mysql_query(self, sql, str=''):
        if (str != ''):
             self.__cursor.execute(sql, str)
             return self.__cursor.fetchall()
        else:
             self.__cursor.execute(sql)
             return self.__cursor.fetchall()

    def mysql_update(self, sql, str=''):
        try:
            result = self.__cursor.execute(sql, str)
            self.__cursor.commit()
        except Exception:
            self.__conn.rollback()
            print("发生异常")
        return result

    def close(self):
        self.__cursor.close()
        self.__conn.close()

    def prepare_insertsql(self, tablename, dict_data={}):
        # 数据库中表对应的键值
        keys = ",".join(dict_data.keys())
        # 数据库中对应的键值个数
        values = ",".join(['%s'] * len(dict_data))

        sql = r"insert into %s (%s) VALUES (%s)" % (tablename, keys, values)
        return sql

    def prepare_data(self, dict_data={}):
        data = tuple([pymysql.escape_string(one) for one in dict_data.values()])
        return data


class ExcelHelper(object):
    def __init__(self, filepath):
        try:
            self.work_book = xlrd.open_workbook(filepath,
                                                encoding_override='utf-8')
        except XLRDError as e:
            print(e)

    def get_content(self, sheet_index, row_num, kwargs={}):
        '''
        读取excel的内容
        :param sheet_index:sheet页位于第几个
        :param row_num :从第几行开始
        :param kwargs: 传递对应的数据库中的字段的字典表，
        键表示对应的数据库中的字段的名称，值表示对应的excel中的列号，从0开始,值必须是int类型
        :param length: 传递excel每行的个数
        :return:
        '''
        for i in range(row_num, self.work_book.sheet_by_index(sheet_index).nrows):
            dict_map = {}
            data_excel = self.work_book.sheet_by_index(sheet_index).row_values(i, 0, self.work_book.sheet_by_index(
                sheet_index).row(i).__len__())
            for key in kwargs.keys():
                if isinstance(kwargs.get(key), int):
                    dict_map[key] = str(data_excel[kwargs.get(key)])
                else:
                    dict_map[key] = eval(kwargs.get(key))
            yield dict_map


def getFileDataTime(filename):
    '''
    文件名称如：入住人数及来源国_20180618.xls
    :param filename:
    :return:
    '''
    result=re.match(".*?_(\d*).xls",filename)
    time_num=result.group(1) if result is not None and len(result.group(1)) != 0 else None

    if not time_num:
        return None
    else:
        reslt = time.strptime(time_num, "%Y%m%d")
        return time.strftime("%Y-%m-%d", reslt)



if __name__ == '__main__':
    excel_dir = ""
    while True:
        if not os.path.isdir(excel_dir):
            excel_dir= input("请您输入文件存放的文件夹:")
        else:
            break

    excel_helper=None
    mysql_helper=None

    while True:

        while True:
            #获取文件的具体位置
            dirs=os.listdir(excel_dir)
            if len(dirs)==0:
                print("文件还未进行上传，请尽快上传文件，程序将要执行下一个周期!")
                break
            dirs=sorted(dirs,reverse=True)#降序排序
            excel_path=os.path.join(excel_dir,dirs[0])#文件名称合并

            if not excel_helper:
                excel_helper = ExcelHelper(excel_path)
            if not mysql_helper:
                mysql_helper = MysqlHelper()

            #获取文件对应的数据日期
            timeData = getFileDataTime(dirs[0])
            if timeData:
                sql="SELECT MAX(t_date) FROM t_po_hotelportrait"
                #如果表中没有数据将会获得None
                max_date=mysql_helper.mysql_query(sql)

                #如果数据是最新的数据，则插入数据
                if max_date[0][0] and max_date[0][0]<timeData:
                    map_dict = {'t_id': "uuid.uuid1().hex", 't_policeName': 0, 't_hotelName': 1, "t_sex": 2,
                                "t_cardNo": 3, "t_num": 4}
                    # 第一个sheet也的第一行开始
                    for dict_data in excel_helper.get_content(0, 1, map_dict):

                        # 设置酒店所处的城市名称
                        dict_data['t_cityName'] = "济南"

                        # 设置酒店所处的区县名称
                        hotel_district=""
                        for key in area_map.keys():
                            if key in  dict_data.get("t_policeName"):
                                hotel_district=area_map.get(key)
                                break
                        dict_data['t_districtName'] = hotel_district

                        # 为数据赋值时间
                        dict_data['t_date'] = timeData

                        sql = mysql_helper.prepare_insertsql("t_po_hotelportrait", dict_data)
                        data = mysql_helper.prepare_data(dict_data)
                        mysql_helper.mysql_insert(sql, data)
                        print("正在%s导入%s 的数据 数据为：%s"%(timeData,dict_data.get("t_hotelName"),dict_data))
                    print("%s 数据导入完成"%(timeData,))
                    break
                else:
                    print("%s 今天没有新数据导入"%(timeData,))
                    print("程序将要等待下一个周期执行，程序窗口不能关闭！")
                    break
            else:
                print("%s excel文件时间命名不对，请按照格式命名,程序将在5秒后关闭" % (timeData,))
                time.sleep(5)
                exit()

        time.sleep(60*24*60)

转载于:https://my.oschina.net/jiansin/blog/2243699