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

Last Updated : 27 Mar, 2021

In this article, we are going to store XML data into the MySQL database using python through XAMPP server. So we are taking student XML data and storing the values into the database.

Requirements

XAMPP server: It is a cross-platform web server used to develop and test programs on a local server. It is developed and managed by Apache Friends and is open-source. It has an Apache HTTP Server, MariaDB, and interpreter for 11 different programming languages like Perl and PHP. XAMPP Stands for cross-platform, Apache, MySQL, PHP, and Perl. It can be easily installed from here.
MySQL connector: MySQL Connector module of Python is used to connect MySQL databases with the Python programs, it does that using the Python Database API Specification v2.0 (PEP 249). It uses the Python standard library and has no dependencies. It can be installed using the below command:

pip install mysql.connector

Approach

Start XAMPP server

Create XML file.

XML Structure:

<root>

<child>

<subchild>.....</subchild>

</child>

</root>

We are going to use the XML module.

xml.etree.ElementTree

This is an Element Tree XML API that is used to implement a simple and efficient API for parsing and creating XML data. So we need to import this module.

Syntax:

import xml.etree.ElementTree

The XML file to be created is names vignan.xml.

XML

<?xml version="1.0"?>
<studentdata>
    <student>
        <name>Sravan Kumar</name>
        <id>7058</id>
        <department>IT</department>
    </student>
    <student>
        <name>Meghana</name>
        <id>7034</id>
        <department>IT</department>
    </student>
    <student>
        <name>Pranathi</name>
        <id>7046</id>
        <department>EEE</department>
    </student>
    <student>
        <name>Durga</name>
        <id>7078</id>
        <department>Mech</department>
    </student>
    <student>
        <name>Ishitha</name>
        <id>7093</id>
        <department>MBA</department>
    </student>
</studentdata>

For Python code:

Create a python file names a.py.
Import required module.
Establish connection.
Read XML file.
Retrieve data from the XML and insert it into a table in the database.
Display message on successful insertion of data.

Python

# import xml element tree
import xml.etree.ElementTree as ET

# import mysql connector
import mysql.connector

# give the connection parameters
# user name is root
# password is empty
# server is localhost
# database name is database
conn = mysql.connector.connect(user='root', 
                               password='', 
                               host='localhost', 
                               database='database')

# reading xml file , file name is vignan.xml
tree = ET.parse('vignan.xml')

# in our xml file student is the root for all 
# student data.
data2 = tree.findall('student')

# retrieving the data and insert into table
# i value for xml data #j value printing number of 
# values that are stored
for i, j in zip(data2, range(1, 6)):
    name = i.find('name').text
    id = i.find('id').text
    department = i.find('department').text
    
    # sql query to insert data into database
    data = """INSERT INTO vignan(name,id,department) VALUES(%s,%s,%s)"""

    # creating the cursor object
    c = conn.cursor()
    
    # executing cursor object
    c.execute(data, (name, id, department))
    conn.commit()
    print("vignan student No-", j, " stored successfully")