How to store XML data into a MySQL database using Python?

XML (eXtensible Markup Language) is a widely used format for storing structured data. When working with large XML datasets, storing this data in a MySQL database provides better performance and query capabilities. Python offers excellent libraries to parse XML and interact with MySQL databases.

Prerequisites

Before starting, ensure you have the required libraries installed ?

pip install mysql-connector-python

Step 1: Import Required Libraries

We need two essential libraries for this task ?

• xml.etree.ElementTree: For parsing and manipulating XML documents

• mysql.connector: For connecting Python to MySQL database

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

Step 2: Sample XML Data

Let's create a sample XML file to demonstrate the process. Save this as employees.xml ?

<employees>
    <employee>
        <id>1</id>
        <name>John Doe</name>
        <department>IT</department>
        <salary>75000</salary>
    </employee>
    <employee>
        <id>2</id>
        <name>Jane Smith</name>
        <department>HR</department>
        <salary>65000</salary>
    </employee>
    <employee>
        <id>3</id>
        <name>Bob Johnson</name>
        <department>Finance</department>
        <salary>70000</salary>
    </employee>
</employees>

Step 3: Parse XML Data

Parse the XML file and extract the root element ?

import xml.etree.ElementTree as ET

# Parse the XML file
tree = ET.parse('employees.xml')
root = tree.getroot()

print(f"Root element: {root.tag}")
print(f"Number of employees: {len(root)}")

Root element: employees
Number of employees: 3

Step 4: Database Connection and Table Creation

Connect to MySQL and create a table to store the XML data ?

import mysql.connector

# Database connection
db = mysql.connector.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)

cursor = db.cursor()

# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS employees (
        id INT PRIMARY KEY,
        name VARCHAR(100),
        department VARCHAR(50),
        salary DECIMAL(10,2)
    )
''')

Step 5: Complete Example - Store XML in MySQL

Here's the complete working example that parses XML and stores data in MySQL ?

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

# Sample XML data as string (for demo purposes)
xml_data = """
<employees>
    <employee>
        <id>1</id>
        <name>John Doe</name>
        <department>IT</department>
        <salary>75000</salary>
    </employee>
    <employee>
        <id>2</id>
        <name>Jane Smith</name>
        <department>HR</department>
        <salary>65000</salary>
    </employee>
</employees>
"""

# Parse XML data
root = ET.fromstring(xml_data)

# Simulate database operations (for demo)
print("Parsing XML data...")
employees_data = []

for employee in root.findall('employee'):
    emp_id = int(employee.find('id').text)
    name = employee.find('name').text
    department = employee.find('department').text
    salary = float(employee.find('salary').text)
    
    employees_data.append((emp_id, name, department, salary))
    print(f"Processed: ID={emp_id}, Name={name}, Dept={department}, Salary=${salary}")

print(f"\nTotal employees processed: {len(employees_data)}")
print("Data ready for MySQL insertion!")

Parsing XML data...
Processed: ID=1, Name=John Doe, Dept=IT, Salary=$75000.0
Processed: ID=2, Name=Jane Smith, Dept=HR, Salary=$65000.0

Total employees processed: 2
Data ready for MySQL insertion!

Step 6: Insert Data with Error Handling

Here's a robust version with error handling for production use ?

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

def store_xml_to_mysql():
    try:
        # Database connection
        db = mysql.connector.connect(
            host='localhost',
            user='your_username',
            password='your_password',
            database='your_database'
        )
        
        cursor = db.cursor()
        
        # Parse XML
        tree = ET.parse('employees.xml')
        root = tree.getroot()
        
        # Insert data
        for employee in root.findall('employee'):
            emp_id = int(employee.find('id').text)
            name = employee.find('name').text
            department = employee.find('department').text
            salary = float(employee.find('salary').text)
            
            query = "INSERT INTO employees (id, name, department, salary) VALUES (%s, %s, %s, %s)"
            values = (emp_id, name, department, salary)
            cursor.execute(query, values)
        
        # Commit changes
        db.commit()
        print(f"Successfully inserted {cursor.rowcount} records")
        
    except Error as e:
        print(f"MySQL Error: {e}")
        if db:
            db.rollback()
    except ET.ParseError as e:
        print(f"XML Parse Error: {e}")
    finally:
        if cursor:
            cursor.close()
        if db:
            db.close()

# Call the function
store_xml_to_mysql()

Key Benefits

Conclusion

Storing XML data in MySQL using Python provides better performance, data integrity, and query capabilities. The combination of xml.etree.ElementTree for parsing and mysql.connector for database operations creates an efficient pipeline for XML data management.