import json
import pymysql
import codecs

# 连接数据库
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='HelloWorld123',
    db='water',  # 确保数据库名与实际一致
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        # 读取 JSON 文件内容
        with codecs.open("moveplan.json", encoding="utf-8") as lud:
            content = lud.read()
        
        # 解析 JSON 数据
        data = json.loads(content)
        
        # 插入数据到数据库
        for item in data:
            cursor.execute("""
                INSERT INTO Move_Plans (isAfter, isOut, `desc`, `from`, `to`, manNum, homeNum, completeNum, dateTime,villageCount,materialPrize) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s)
            """, (
                int(item['isAfter']),
                int(item['isOut']),
                item['desc'],
                item['from'],
                item['to'],
                int(item['manNum']),
                int(item['homeNum']),
                int(item['completeNum']),
                item['dateTime'],
                int(item['villageCount']),
                int(item['materialPrize']),
            ))
        
        # 提交事务
        conn.commit()
        print("数据插入成功")
finally:
    conn.close()