XML文件数据无法读取并写入本地MySQL数据库的问题求助

核心问题分析 #

代码无法读取XML数据的原因有两个：

1. 命名空间配置错误：XML中声明的命名空间是xmlns:icom="a1234"，但代码里写的是{'icom': 'something'}，完全不匹配；且XML内的<Item>、<Label>等子元素没有icom:前缀，不属于该命名空间，使用icom:Item查找必然失败。
2. 数据类型未转换：XML属性读取到的是字符串，但数据库表中id、value_type等字段是数值类型，直接插入会导致类型不匹配错误。

修正后的完整代码 #

from lxml import etree
import mysql.connector

def parse_and_insert_to_mysql(file_path, db_config):
    conn = None
    cursor = None
    data_found = False
    try:
        # Parse the XML file using lxml
        print("Parsing XML file using lxml...")
        tree = etree.parse(file_path)
        root = tree.getroot()
        print("XML file parsed successfully.")

        # Print the root tag to verify successful parsing
        print(f"Root tag: {root.tag}")

        # 修正命名空间映射（与XML中的声明一致）
        namespace = {'icom': 'a1234'}

        # Connect to the MySQL database
        print("Connecting to MySQL database...")
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()
        print("Connected to MySQL database.")

        # 修正XPath：直接查找不带命名空间前缀的Item元素
        for item in root.findall('Item'):
            data_found = True
            # 转换数据类型以匹配数据库字段
            item_id = int(item.attrib['id'])
            name = item.attrib['name']
            label = item.find('Label').text
            value_element = item.find('Value')
            value_type = int(value_element.attrib['valueType'])
            offset = float(value_element.attrib['offset'])
            gain = float(value_element.attrib['gain'])
            precision_level = int(value_element.attrib['precision'])
            value = value_element.text
            # 处理Unit元素为空的情况
            unit_element = item.find('Unit')
            unit = unit_element.text if unit_element is not None and unit_element.text is not None else ''
            sensor_date = '2024-10-05 00:00:00'

            # Debugging: Print each value to verify correctness
            print(f"Preparing to insert: id={item_id}, name={name}, label={label}, value_type={value_type}, offset={offset}, gain={gain}, precision_level={precision_level}, value={value}, unit={unit}, sensor_date={sensor_date}")

            insert_query = (
                "INSERT INTO ClimateMonitoring (id, name, label, value_type, offset, gain, precision_level, value, unit, sensor_date) "
                "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            )
            try:
                cursor.execute(insert_query, (item_id, name, label, value_type, offset, gain, precision_level, value, unit, sensor_date))
                print(f"Inserted row with id={item_id}")
            except mysql.connector.Error as e:
                print(f"Failed to insert row with id={item_id}: {e}")
            except Exception as e:
                print(f"Unexpected error when inserting row with id={item_id}: {e}")

        # Check if no data was found
        if not data_found:
            raise ValueError("No insertable data found in the XML file.")

        # Commit the transaction
        print("Committing transaction...")
        conn.commit()
        print("Transaction committed successfully.")

    except etree.XMLSyntaxError as e:
        print(f"Error parsing the XML file: {e}")
    except mysql.connector.Error as e:
        print(f"Error with MySQL database: {e}")
    except ValueError as e:
        print(e)
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        # Close the database connection
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        print("Database connection closed.")

# Path to your XML file
file_path = r"path/to/your/xml/file.xml"  # 替换为实际文件路径

# MySQL database configuration
db_config = {
    'user': 'root',
    'password': 'your_password',  # 替换为你的数据库密码
    'host': 'localhost',
    'database': 'climate'
}

parse_and_insert_to_mysql(file_path, db_config)

关键修正点说明 #

1. 命名空间与XPath调整：
   • 将命名空间字典改为{'icom': 'a1234'}，与XML中的声明一致；
   • 查找<Item>及子元素时，直接使用元素名（如root.findall('Item')、item.find('Label')），因为这些元素未带icom:前缀，不属于该命名空间。
2. 数据类型转换：
   • 将id、value_type、precision_level转换为int类型；
   • 将offset、gain转换为float类型，确保与数据库字段类型匹配。
3. 空值处理优化：
   • 增加对<Unit>元素文本为空的判断，避免None值插入数据库。

内容的提问来源于stack exchange，提问作者Trickfinger