将excel数据导入数据库

需求:将文件夹低下的excel表格导入到数据库(大概70个文件夹,每个excel有30多个sheet)
excel格式如下


非主营业务.png

1.使用xlrd模块,读取excel文件

import xlrd
import pymysql
filename = r'F:\资料\非主营业务\非主营业务73.xlsx'
rbook = xlrd.open_workbook(filename)
names = rbook.sheet_names()

2.提取需要的字段(code,name),code的第一位为6,在后面加SH(上海);code的第一位为0或者3,在后面加SZ(深圳)

#股票编码
code = cod[0:6]
 if code[0] == '6':
      code += '.SH'
elif code[0] in ['3', '0']:
        code += '.SZ'
else:
        continue
#公司姓名
name = cod[6:]

3.读取每一个表,将报错的sheet名字,类型写入txt文件

sheet = rbook.sheet_by_name(cod)
unit = sheet.row_values(0)[0]
        if unit == 0:
            with open('error.txt', 'a')as A:
                A.write('第一个单元格为0:' + cod + '\n')
            continue
        if unit == '不存在' or unit == '不适用':
            print(cod, '不存在####################')
            continue
        if unit == '单位可疑':
            with open('error.txt', 'a')as A:
                A.write('单位可疑:' + cod + '\n')
            continue
        #但第三行第一单元格不为空 和 等于项目的时候跳出
        if sheet.row_values(2)[0] != '' and sheet.row_values(2)[0] != '项目':
            with open('error.txt', 'a')as A:
                A.write('错位:' + cod + '\n')
            continue

4.获取每个表格行数sheet.nrows,并对表格的内容进行清洗

for i in range(sheet.nrows):
     data = sheet.row_values(i)
     #因为存在空值,把第四行第二单元格为金额拿出来
     if i > 2 and sheet.row_values(3)[1] == '金额':
          with open('error.txt', 'a')as A:
                A.write('第四行异常:' + cod + '\n')
                continue
      for aa in range(len(data)):
                data[aa] = str(data[aa])
                data[aa] = data[aa].replace('\n', '')
                data[aa] = data[aa].replace(' ', '')
                data[aa] = data[aa].replace(',', '')
                data[aa] = data[aa].replace('--', '')
                data[aa] = data[aa].replace('否否', '否')
                data[aa] = data[aa].replace('——', '')
                data[aa] = data[aa].replace('—', '')
                data[aa] = data[aa].replace('"', '')
                data[aa] = data[aa].replace('不可持续不可持续', '不可持续')
                data[aa] = ''.join(data[aa].split())
                #data[aa] = data[aa][:data[aa].find('.', data[aa].find('.') + 1)]
        lst.append(data)

5.设置每个表格的表头,并于需要的内容关联起来,使用的是zip,以dict字典的形式。

keyTitle = ['Keyword', 'Amount', 'total_profit', 'reason', 'Persistent']
lsts = []
for da in lst[2::]:
       data_dic = zip(keyTitle, da)
        lsts.append(dict(data_dic))
print(keyTitle)

6.最后插入数据库

sql = 'insert into business (date,code,name,Keyword,Amount,total_profit,reason,Persistent,unit) values ("{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(date, code, name, Keyword, Amount, total_profit, reason, Persistent, unit)
cursor.execute(sql)
db.commit()

完整代码:


#excel表格导入数据库
import xlrd
import pymysql
import time
from xlrd import xldate_as_tuple
from datetime import datetime
from time import sleep
import os
import re


str_dic = {
    "Keyword": '关键字',
    "Amount": '金额',
    "total_profit": '占利润总额比例',
    'reason': '形成原因说明',
    'Persistent': '是否具有可持续性',
}
keyTitle = ['Keyword', 'Amount', 'total_profit', 'reason', 'Persistent']
# 总净资产利润营业收入
if __name__ == '__main__':
    date = '2019-12-31'
    db = pymysql.connect(host='192.168.0.147', port=3306, user='root', password='123', db='test')
    cursor = db.cursor()
    filename = r'F:\资料\非主营业务\非主营业务73.xlsx'
    rbook = xlrd.open_workbook(filename)
    names = rbook.sheet_names()
    titles = []
    for cod in rbook.sheet_names():
        #在编号名称后面加SH,SZ
        code = cod[0:6]
        if code[0] == '6':
            code += '.SH'
        elif code[0] in ['3', '0']:
            code += '.SZ'
        else:
            continue
        #公司姓名
        name = cod[6:]

        sheet = rbook.sheet_by_name(cod)
        unit = sheet.row_values(0)[0]
        if unit == 0:
            with open('error.txt', 'a')as A:
                A.write('第一个单元格为0:' + cod + '\n')
            continue
        if unit == '不存在' or unit == '不适用':
            print(cod, '不存在####################')
            continue
        if unit == '单位可疑':
            with open('error.txt', 'a')as A:
                A.write('单位可疑:' + cod + '\n')
            continue
        #但第三行第一单元格不为空 和 等于项目的时候跳出
        if sheet.row_values(2)[0] != '' and sheet.row_values(2)[0] != '项目':
            with open('error.txt', 'a')as A:
                A.write('错位:' + cod + '\n')
            continue

        lst = []
        for i in range(sheet.nrows):
            data = sheet.row_values(i)
            #因为存在空值,把第四行第二单元格为金额拿出来
            if i > 2 and sheet.row_values(3)[1] == '金额':
                with open('error.txt', 'a')as A:
                    A.write('第四行异常:' + cod + '\n')
                continue
            for aa in range(len(data)):
                data[aa] = str(data[aa])
                data[aa] = data[aa].replace('\n', '')
                data[aa] = data[aa].replace(' ', '')
                data[aa] = data[aa].replace(',', '')
                data[aa] = data[aa].replace('--', '')
                data[aa] = data[aa].replace('否否', '否')
                data[aa] = data[aa].replace('——', '')
                data[aa] = data[aa].replace('—', '')
                data[aa] = data[aa].replace('"', '')
                data[aa] = data[aa].replace('不可持续不可持续', '不可持续')
                data[aa] = ''.join(data[aa].split())
                #data[aa] = data[aa][:data[aa].find('.', data[aa].find('.') + 1)]


            lst.append(data)


        lsts = []
        for da in lst[2::]:
            data_dic = zip(keyTitle, da)
            lsts.append(dict(data_dic))
        print(keyTitle)

        for i in lsts[1::]:
            # print(i)
            Keyword = i.get('Keyword')
            if not Keyword:
                Keyword = ''
            Amount = i.get('Amount')
            if Keyword[-11:] == '(损失以“-”号填列)':
                Keyword = Keyword[:len(Keyword) - 11]
            if Keyword[-5:] == '益资产减值':
                Keyword = Keyword[:len(Keyword) - 4]
            if Keyword[-3:] == '(注)':
                Keyword = Keyword[:len(Keyword) - 3]
            if not Amount:
                Amount = ''
            if Amount.count('.') > 1:
                with open('error.txt', 'a')as A:
                    A.write('多个小数点:' + cod + '\n')
                continue
            if  Amount == '-':
                Amount = ''
            #才用正则,把数字后面文字去掉
            #Amount=''.join(re.findall(r"\d+\.?\d*",Amount))
            total_profit = i.get('total_profit')
            if not total_profit:
                total_profit = ''
            if total_profit.count('%') > 1:
                with open('error.txt', 'a')as A:
                    A.write('多个百分号:' + cod + '\n')
                continue
            if  total_profit == '-':
                total_profit = ''
            # 才用正则,把数字后面文字去掉,再加上%,把等于%的符号替换为空
            #total_profit = ''.join(re.findall(r"\d+\.?\d*", total_profit))+'%'
            #if total_profit == '%':
            #    total_profit = ''
            reason = i.get('reason')
            if not reason:
                reason = ''
            if  reason == '-':
                reason = ''
            Persistent = i.get('Persistent')
            if not Persistent:
                Persistent = ''
            if  Persistent == '-':
                Persistent = ''
            print(Keyword, Amount, total_profit, reason, Persistent)
            # db.ping()
            sql = 'insert into business (date,code,name,Keyword,Amount,total_profit,reason,Persistent,unit) values ("{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(
                date, code, name, Keyword, Amount, total_profit, reason, Persistent, unit
            )
            cursor.execute(sql)
            db.commit()
    db.close()
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 203,098评论 5 476
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,213评论 2 380
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 149,960评论 0 336
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,519评论 1 273
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,512评论 5 364
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,533评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,914评论 3 395
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,574评论 0 256
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,804评论 1 296
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,563评论 2 319
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,644评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,350评论 4 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,933评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,908评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,146评论 1 259
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,847评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,361评论 2 342