import pandas as pd
import pymysql

# 读取 Excel 文件
df = pd.read_excel('students_info.xlsx')

# 数据库连接信息
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'root',
    'db': 'unicom',
}

# 创建数据库连接
connection = pymysql.connect(**db_config)

try:
    with connection.cursor() as cursor:
        for index, row in df.iterrows():
            # 构建插入 SQL 语句
            sql = """
            INSERT INTO students (id, name, points, has_shield, consecutive_calls)
            VALUES (%s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (row['ID'], row['Name'], row['Points'], row['Has_Shield'], row['Consecutive_Calls']))

    # 提交更改
    connection.commit()
    print("数据已成功导入到数据库。")

finally:
    connection.close()