xml 导入到mysql

<?xml version="1.0" encoding="UTF-8"?>
<data>
    <record id="1">
        <name>John Doe</name>
        <age>30</age>
    </record>
    <record id="2">
        <name>Jane Smith</name>
        <age>25</age>
    </record>
</data>

CREATE TABLE records (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

LOAD DATA INFILE 'data.xml'
INTO TABLE records
FIELDS TERMINATED BY '<record id="'
LINES TERMINATED BY '</record>'
IGNORE 1 LINES;

import xml.etree.ElementTree as ET
import mysql.connector
import json

# 解析XML文件
tree = ET.parse('data.xml')
root = tree.getroot()

# 将XML数据转换为JSON格式
data = []
for record in root.findall('record'):
    data.append({
        'id': record.get('id'),
        'name': record.find('name').text,
        'age': int(record.find('age').text)
    })

# 连接到MySQL数据库
conn = mysql.connector.connect(user='user', password='password', host='host', database='database')
cursor = conn.cursor()

# 插入数据到MySQL表中
for item in data:
    cursor.execute("INSERT INTO records (id, name, age) VALUES (%s, %s, %s)", (item['id'], item['name'], item['age']))

# 提交事务并关闭连接
conn.commit()
cursor.close()
conn.close()