博客

文件夹：

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