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