import pandas as pd
import pymysql

# 连接到 MySQL 数据库
cnx = pymysql.connect(
    host='localhost',
    user='root',
    password='123123',
    database='your_database'
)

# 创建数据库游标
cursor = cnx.cursor()

# 创建一个示例的 DataFrame
df = pd.read_csv('igs_stations.csv')

# 定义插入数据的 SQL 语句
insert_stmt = "INSERT INTO student (Site Name, Country/Region, Receiver,Antenna,Radome,Satellite System,Latitude," \
              "Longitude,Height (m),X (m),Y (m),Z (m),Calibration,Networks,Data Center,IERS DOMES,Clock,Agencies," \
              "Last Data) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

try:
    # 遍历 DataFrame 中的行并插入到数据库
    for _, row in df.iterrows():
        # 每一行的数据按照需要提取到变量中
        name = row['name']
        age = row['age']
        grade = row['grade']

        # 执行插入操作
        cursor.execute(insert_stmt, (name, age, grade))

    # 提交事务
    cnx.commit()
    print("数据插入成功！")
except Exception as e:
    # 发生错误时回滚事务
    cnx.rollback()
    print("数据插入失败:", str(e))

# 关闭数据库连接
cursor.close()
cnx.close()