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
XML Structure:
<root>
<child>
<subchild>…..</subchild>
</child>
</root>
We are going to use the XML module.
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 >
|
- 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.etree.ElementTree as ET
import mysql.connector
conn = mysql.connector.connect(user = 'root' ,
password = '',
host = 'localhost' ,
database = 'database' )
tree = ET.parse( 'vignan.xml' )
data2 = tree.findall( 'student' )
for i, j in zip (data2, range ( 1 , 6 )):
name = i.find( 'name' ).text
id = i.find( 'id' ).text
department = i.find( 'department' ).text
data =
c = conn.cursor()
c.execute(data, (name, id , department))
conn.commit()
print ( "vignan student No-" , j, " stored successfully" )
|
Output:
- Verify the content of the table in which the values were recently inserted.
Share your thoughts in the comments
Please Login to comment...