使用带有列映射的python将XML文件加载到MySQL数据库

pip install mysql-connector-python

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

tree = ET.parse('example.xml')  # 替换为实际的XML文件路径
root = tree.getroot()

data = []
for item in root.findall('item'):
    column1 = item.find('column1').text
    column2 = item.find('column2').text
    # 获取其他需要的列数据

    data.append((column1, column2, ...))  # 将数据添加到列表中

cnx = mysql.connector.connect(user='username', password='password', host='localhost', database='database_name')  # 替换为实际的数据库连接信息
cursor = cnx.cursor()

create_table_query = '''
CREATE TABLE IF NOT EXISTS table_name (
    column1 VARCHAR(255),
    column2 VARCHAR(255),
    ...
)
'''
cursor.execute(create_table_query)

insert_query = '''
INSERT INTO table_name (column1, column2, ...)
VALUES (%s, %s, ...)
'''
cursor.executemany(insert_query, data)

cnx.commit()  # 提交事务

cursor.close()
cnx.close()