MENU

Catalog

    python秒存数据excel,mysql

    June 16, 2019 • python阅读设置

    import json
    import pymysql
    import datetime
    import json
    import os
    from openpyxl import Workbook
    import time
    <!--more-->
    def count_time(func):
        def int_time(*args, **kwargs):
            start_time = datetime.datetime.now()  # 程序开始时间
            func()
            over_time = datetime.datetime.now()   # 程序结束时间
            total_time = (over_time-start_time).total_seconds()
            print('程序共计%s秒' % total_time)
        return int_time
    
    @count_time
    def to_mysql():
        conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db="jhshop")
        #创建游标
        cursor = conn.cursor()
        with open('jhshop1.json','r',encoding='utf-8')as f:
            list1 = (i for i in f.readlines())
            count =0
            for k in list1:
                k1 = json.loads(k)
                price1 = k1["price"].replace("¥","")
                name1 = k1["name"]
                shop_name1 = k1["shop_name"]
                zxl1 = int(k1["zxl"])
                pingjia1 = int(k1["pingjia"])
                cursor.execute("insert into jjshop (name, price, shopname, zxl, pingjia) values (%s, %s, %s, %s, %s)",
                           (name1,price1,shop_name1,zxl1,pingjia1))
                # cursor.execute("INSERT INTO jjshop (name,price,shopname,zxl,pingjia) VALUES (%s,%s,%s.%s.%s)",
                #                (name1,price1,shop_name1,zxl1,pingjia1))
                conn.commit()
                count +=1
                print("执行成功{}".format(count))
    
        conn.close()
        cursor.close()
    
    
    
    
    
    
    
    wb = Workbook()
    ws = wb.active
    
    cols = []
    
    def json2excel(jsfile, excfile):
        # 读取json数据
        a = 1
        if os.path.exists(jsfile):
            # 先用key值写表头
            with open(jsfile, 'r', encoding='utf8') as fp:
                # 先用key值写表头
                line = fp.readline()
                if not line:
                    print("没有内容")
                else:
                    # 每一行转换成字典类型
                    jsdata = json.loads(line)
                    # 用key值做标题
                    for k in jsdata.keys():
                        if k not in cols:
                            cols.append(k)
                    ws.append(cols)  # 标题
            # 写值
            with open(jsfile, 'r', encoding='utf8') as fp:
                # 循环写值
                while True:
                    print('正在写入的行数%s:' % a)
                    line = fp.readline()
                    if not line:
                        break
                    # 转换为python对象
                    jsdata = json.loads(line)
                    rowdata = []
                    for col in cols:
                        # 获取每一行key值对应的value值
                        rowdata.append(jsdata.get(col))
                    a += 1
                    ws.append(rowdata)  # 写行
            # ws.append(cols) # 标题
        print('保存中')
        wb.save(excfile)  # 保存
    
    
    if __name__ == '__main__':
        s_time = time.time()
        jsfile = "jhshop.json"
        excfile = "jhshop.xlsx"
        json2excel(jsfile, excfile)
        e_time = time.time()
        print("耗时%s秒"%(e_time-s_time))
    
    Leave a Comment