您好,欢迎访问代理记账网站
  • 价格透明
  • 信息保密
  • 进度掌控
  • 售后无忧

python3批量处理文件夹下 exel文档并插入MySQL数据库

文件夹:

exel文档格式:

 

python用到的模块:

import pymysql

import xlrd

import os

代码:

def readAll(self):
        folderFile = r'D:/院校排名/院校排名/'
        for root,dirs,files in os.walk(folderFile):
            for f in files:
                exelFile = folderFile+f
                table = self.openExel(exelFile,0)
                for i in range(table.nrows):
                    rowData = table.row_values(i)
                    ranking = i + 1
                    majorName = f.strip().replace(' ', '').replace('.xlsx', '').replace('(', '(').replace(')', ')')
                    schoolCode = ''
                    schoolName = rowData[0].strip().replace('推荐阅读', '').replace('风景园林', '').replace('录取分数线', '').replace('l', '').replace('参考书目', '').replace('专业分析', '').replace('()', '').replace('()', '').replace('(', '(').replace(')', ')').replace(' ', '')
                    grade = rowData[1].strip().replace(' ', '')
                    if grade == None:
                        grade = ''
                    sqlQu = 'SELECT id FROM a_major_university_top WHERE major_name = "%s" AND university_code = "%s" AND university_name = "%s" AND ranking = "%s" AND grade = "%s" ' % (majorName,schoolCode,schoolName,ranking,grade)
                    self.cursor.execute(sqlQu)
                    result = self.cursor.fetchone()
                    if result == None:
                        sqlIn = 'INSERT a_major_university_top SET major_name = "%s" , university_code = "%s" , university_name = "%s" , ranking = "%s" , grade = "%s" ' % (majorName,schoolCode,schoolName,ranking,grade)
                        print(sqlIn)
                        self.cursor.execute(sqlIn)
                        self.cursor.connection.commit()
                    else:
                        print('已经有同样数据了!')

我用的python的版本是3.7.9

下边这个带表结构和注释说明。

https://download.csdn.net/download/daotianmi/19143880


分享:

低价透明

统一报价,无隐形消费

金牌服务

一对一专属顾问7*24小时金牌服务

信息保密

个人信息安全有保障

售后无忧

服务出问题客服经理全程跟进