Python 脚本将 Excel 数据导入数据库

这段代码使用 Python Pandas 库读取 Excel 表格中的数据，并生成相应的 SQL 插入语句，方便将数据导入数据库。具体使用方法如下：

1. 在 Excel 表格中填写好需要导入的数据，分别包括学生信息、教师信息、课程信息。

2. 将表格保存为 .xlsx 格式。

3. 将代码中的文件名修改为对应的 Excel 表格名，如 'student_import.xlsx'。

4. 将需要导入的数据按照表格中的格式填写好。

5. 将代码中的数据库表名和字段名修改为对应的表名和字段名。

6. 运行代码，将会输出对应的 SQL 语句。

7. 将输出的 SQL 语句复制到数据库中执行，即可将 Excel 表格中的数据导入到数据库中。

import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import math

# date = pd.read_excel('student_import.xlsx')
#
# id = date['学号']
# # print(len(id))
# for i in range(0, len(id)):
#     id1 = str(id[i])
#     # id1 = '201711010226'
#     sql = 'insert into user (userId, PASSWORD, roldId) values ('
#     sql= sql + ''' + id1 + ''' + ',' +  ''' + id1[-6 : ] + ''' + ',' + '1);'
#     print(sql)

# date = pd.read_excel('teacher_import.xlsx')
#
# id = date['教师工号']
# # print(len(id))
# for i in range(0, len(id)):
#     id1 = str(id[i])
#     # id1 = '201711010226'
#     sql = 'insert into user (userId, PASSWORD, roldId) values ('
#     sql= sql + ''' + id1 + ''' + ',' +  ''' + id1 + ''' + ',' + '2);'
#     print(sql)

# date = pd.read_excel('student_import.xlsx')
#
# id = date['学号']
# name = date['姓名']
# major = date['录取专业']
# clazz = date['拟分入班级']
# # print(len(id))
# for i in range(0, len(id)):
#     id1 = str(id[i])
#     name1 = str(name[i])
#     major1 = str(major[i])
#     clazz1 = str(clazz[i])
#     # id1 = '201711010226'
#     sql = 'insert into student (    studentId, name,major,clazz) values ('
#     sql= sql + ''' + id1 + ''' + ',' +  ''' + name1 + ''' + ',' + ''' +  major1[:8] + ''' + ',' +  ''计工本170' + clazz1 + ''' + ');'
#     print(sql)

# date = pd.read_excel('teacher_import.xlsx')
#
# id = date['教师工号']
# name = date['姓名']
# major = date['学院']
# clazz = date['所在系别']
# # print(len(id))
# for i in range(0, len(id)):
#     id1 = str(id[i])
#     name1 = str(name[i])
#     major1 = str(major[i])
#     clazz1 = str(clazz[i])
#     # id1 = '201711010226'
#     sql = 'insert into teacher (teacherId,name ,college,tie) values ('
#     sql= sql + ''' + id1 + ''' + ',' +  ''' + name1 + ''' + ',' + ''' +  major1[:8] + ''' + ',' +  ''计工本170' + clazz1 + ''' + ');'
#     print(sql)


date = pd.read_excel('course_import.xlsx')

id = date['课程号']
name = date['课程名称']
teacher = date['老师']
num = date['课容量']
major = date['开设教室']
clazz = date['教师教工号']
# print(len(id))
for i in range(0, len(id)):
    id1 = str(id[i])
    name1 = str(name[i])
    teacher1 = str(teacher[i])
    num1 = str(num[i])
    major1 = str(major[i])
    clazz1 = str(clazz[i])
    # id1 = '201711010226'
    sql = 'insert into course (courseId,name ,teachername,num,room,teacherId) values ('
    sql= sql + ''' + id1 + ''' + ',' +  ''' + name1 + ''' + ',' +  ''' + teacher1  + ''' + ',' + num1 + ',' + ''' +  major1[:8] + ''' + ',' +  ''' + clazz1 + ''' + ');'
    print(sql)