Python Pandas 批量生成 SQL 插入语句：学生、教师、课程数据导入

本文提供 Python 代码，使用 Pandas 库从 Excel 文件读取学生、教师、课程数据，并自动生成 SQL 插入语句，方便快速将数据导入数据库。

import pandas as pd

# 读取学生数据
date = pd.read_excel("student_import.xlsx")

id = date["学号"]
name = date["姓名"]
major = date["录取专业"]
clazz = date["拟分入班级"]

for i in range(0, len(id)):
    id1 = str(id[i])
    name1 = str(name[i])
    major1 = str(major[i])
    clazz1 = str(clazz[i])
    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["所在系别"]

for i in range(0, len(id)):
    id1 = str(id[i])
    name1 = str(name[i])
    major1 = str(major[i])
    clazz1 = str(clazz[i])
    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["教师教工号"]

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])
    sql = "insert into course (courseId, name, teachername, num, room, teacherId) values ("
    sql = sql + "'"+ id1 + "'"+," + "'"+ name1 + "'"+," + "'"+ teacher1  + "'"+,"+ num1 + "," + "'"+ major1[:8] + "'"+," + "'"+ clazz1 + "'");"
    print(sql)

建表语句:

CREATE TABLE user (
  id INT PRIMARY KEY AUTO_INCREMENT,
  userId VARCHAR(20) NOT NULL UNIQUE,
  PASSWORD VARCHAR(20) NOT NULL,
  roldId INT NOT NULL
);

CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  studentId VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(20) NOT NULL,
  major VARCHAR(20) NOT NULL,
  clazz VARCHAR(20) NOT NULL
);

CREATE TABLE teacher (
  id INT PRIMARY KEY AUTO_INCREMENT,
  teacherId VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(20) NOT NULL,
  college VARCHAR(20) NOT NULL,
  tie VARCHAR(20) NOT NULL
);

CREATE TABLE course (
  id INT PRIMARY KEY AUTO_INCREMENT,
  courseId VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(20) NOT NULL,
  teachername VARCHAR(20) NOT NULL,
  num INT NOT NULL,
  room VARCHAR(20) NOT NULL,
  teacherId VARCHAR(20) NOT NULL
);

说明:

• user 表用于存储用户信息，包括学生和教师，roldId 为角色 ID，1 表示学生，2 表示教师。
• student 表用于存储学生信息，包括学号、姓名、专业和班级。
• teacher 表用于存储教师信息，包括教师工号、姓名、学院和所在系别。
• course 表用于存储课程信息，包括课程号、课程名称、教师姓名、课容量、开设教室和教师教工号。
• 每个表都有一个自增主键 id，用于唯一标识每个记录。

使用方法:

1. 将 student_import.xlsx、teacher_import.xlsx、course_import.xlsx 文件分别替换为你的 Excel 文件名。
2. 将代码复制到 Python 环境中运行，程序会自动输出 SQL 插入语句。
3. 将输出的 SQL 插入语句复制到数据库管理工具中执行，即可将数据导入到数据库。