Insert XML Data to MySQL Table using Python

In this article, you will learn how to insert XML data into the MySQL table using the Python programming language.

Python has a set of helpful libraries and packages that minimise the use of code in our everyday lives. This quality of Python makes it a top pick among a large portion of the development. A major advantage of this is that it makes it so easy to work with huge datasets.

In web applications, XML (Extensible Markup Language) is used for many aspects. The data stored in XML can be rendered easily in many applications. That's why it is the best choice for web services and data transport. It stores data in a formatted way that is easy to search and understand. It makes it easier for informative applications to store, retrieve, and display data. It is widely used in e-commerce applications, business-to-business transactions, generating metadata, and so on. As the information in XML format is not readable by general customers, we may need to transform it so that we can easily display it in web applications.

MySQL is a very lightweight, popular, and widely used open source relational database management system. It has been successfully tested on a broad range of different compilers.

Python provides the ElementTree module to parse the XML. Here is a simple application that parses some given XML data and extracts the data elements from the XML.

Suppose we have the following XML file-

<?xml version="1.0"?>
<company>
	<employee>
		<name>Alaya</name>
		<phone>3290349906</phone>
		<email>This email address is being protected from spambots. You need JavaScript enabled to view it.</email>
		<date>This email address is being protected from spambots. You need JavaScript enabled to view it.</date>
	</employee>
	<employee>
		<name>Carle</name>
		<phone>9059098968</phone>
		<email>This email address is being protected from spambots. You need JavaScript enabled to view it.</email>
		<date>This email address is being protected from spambots. You need JavaScript enabled to view it.</date>
	</employee>
	<employee>
		<name>Amma</name>
		<phone>6750390948</phone>
		<email>This email address is being protected from spambots. You need JavaScript enabled to view it.</email>
		<date>This email address is being protected from spambots. You need JavaScript enabled to view it.</date>
	</employee>
</company>

Here, we have imported the XML data by reading from an XML file. The parse() method parses an XML section into an element tree, and the findall() method traverses the immediate children of the referenced element. We have traversed all the immediate children of the 'employee' element and stored them in a variable called 'emp'.

import xml.etree.ElementTree as ET

tree = ET.parse('employee.xml')
emp = tree.findall('employee')

Next, we loop over the emp variable. The find() method finds the first child with a particular tag, and Element.text accesses the element's text content.

for ep in emp:
    name = ep.find('name').text
    email = ep.find('email').text
    phone = ep.find('phone').text

Next, we need MySQL connector module to connect the database. If you do not have this installed, you can install MySQL Connector using the pip command.

pip install mysql-connector

Once the MySQL Connector is installed, import this at the top.

import MySQL.connector

Make sure to replace the 'hostname', 'database_username', 'database_password' and 'database_name' with your database credentials and name.

conn = MySQL.connector.connect(user='database_username', password='database_password', 
                    host='hostname', database='database_name')
conn.close()

Complete Code

Above, we have explained the code in chunks. Here is the complete code to import the XML data to the MySQL table using the Python programming language.

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

conn = mysql.connector.connect(user='root', password='',
                              host='localhost',database='company')

if conn:
    print ("Connected Successfully")
else:
    print ("Connection Not Established")

tree = ET.parse('employee.xml')

emp = tree.findall('employee')


for ep in emp:
    eid = ep.find('id').text
    name = ep.find('name').text
    email = ep.find('email').text
    phone = ep.find('phone').text
    date = ep.find('date').text
    
    employee = """INSERT INTO employee (emp_id,emp_name,email,phone,created_date)
                        VALUES(%s,%s,%s,%s,%s)"""

    cursor = conn.cursor()
    cursor.execute(employee,(eid,name,email,phone,date))
    conn.commit()
    print("Data inserted successfully.")

When, we will execute the above code, it will insert the XML data into MySQL 'employee' table.

Related Articles