2. 现有存放学生照片的文件夹Images及某教务系统导出学生名单Excel表。请编写程序，根据Excel表的数据信息进行比对，将学生的照片以身份证号进行重命名，并按学院分类导出到以学院命名的文件夹中

照片涉及隐私，就不放了

名单如下：

 

代码如下：
如有错误希望各位指出

import xlrd
import os
import shutil
import sqlite3

# 获取Excel文件
data = xlrd.open_workbook("E:\Python实验报告6_素材\名单.xlsx")
sheet1 = data.sheet_by_name("Sheet1")
sheet2 = data.sheet_by_name("Sheet2")
path = "E:\Python实验报告6_素材\Images"
path2 = "E:\Python实验报告6_素材"
suffix = ".jpg"
# 学号初始化
Student_number = sheet1.col_values(2, start_rowx=1, end_rowx=None)
Student_number = [int(i) for i in Student_number]
Student_number = [str(i) for i in Student_number]
# 身份证号初始化
Student_id = sheet1.col_values(3, start_rowx=1, end_rowx=None)
Student_id = [str(i) for i in Student_id]
# 学院初始化
Student_faculty = sheet1.col_values(4, start_rowx=1, end_rowx=None)
Student_faculty = [str(i) for i in Student_faculty]
set_Student_faculty = set(Student_faculty)
# 创建学院文件夹,利用集合提纯
for faculty in set_Student_faculty:
    os.mkdir("E:\Python实验报告6_素材\{}".format(faculty))
# 将学号与身份证号一一对应，将其转换成元组
dictionary = dict(zip(Student_number, Student_id))
dictionary = sorted(dictionary.items(), key=lambda d: d[0], reverse=False)
dictionary = dict(dictionary)  # 再将其转换成字典类型
# 将身份证号与学院一一对应，将其转换成元组
dictionary2 = dict(zip(Student_id, Student_faculty))
dictionary2 = sorted(dictionary2.items(), key=lambda d: d[0], reverse=False)
dictionary2 = dict(dictionary2)

# 将学生的照片以身份证号进行重命名
for file in os.listdir(path):
    for key in dictionary:
        if file == key + suffix:
            os.renames(os.path.join(path, file), os.path.join(path, dictionary.get(key)) + suffix)
            # rename必须将文件路径下的文件和另一个文件路径下的文件进行重命名
# 将学生照片按学院分类导出到以学院命名的文件夹中
for file in os.listdir(path):
    for key in dictionary2:
        if file[:-4] == key:
            shutil.move(os.path.join(path, file), os.path.join(path2, dictionary2.get(key)))
# 与数据库建立连接
all_row = sheet1.nrows
conn = sqlite3.connect("student.db")
cur = conn.cursor()
# 建表
cur.execute("DROP TABLE IF EXISTS list")
cur.execute("CREATE TABLE list(cname TEXT,sex TEXT,sno INT,id TEXT,faculty TEXT,major TEXT,class TEXT)")
# 插入数据
for i in range(all_row):
    data1 = sheet1.row_values(i)
    cur.execute('''insert into list values('%s','%s','%s','%s','%s','%s','%s')''' % (data1[0],
                                                                                     data1[1], data1[2], data1[3],
                                                                                     data1[4], data1[5], data1[6]))
cur.close()
conn.commit()
conn.close()