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

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.

• For Python code:

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

Output:

• Save both files

• Verify the content of the table in which the values were recently inserted.