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") |
Output:
- Save both files
- Verify the content of the table in which the values were recently inserted.
I am really impressed with your writing skills as well as with the layout on your blog.
Is this a paid theme or did you modify it yourself? Either way keep
up the excellent quality writing, it’s rare to see a
nice blog like this one these days.
Aw, this was an exceptionally good post. Spending some
time and actual effort to make a good article… but what can I say… I hesitate
a lot and never seem to get nearly anything done.
Thanks for sharing your thoughts about 로미로미.
Regards
Howdy, i read your blog from time to time and i own a similar one and i
was just curious if you get a lot of spam remarks?
If so how do you prevent it, any plugin or anything you can recommend?
I get so much lately it’s driving me insane so any help is very much
appreciated.
I don’t even know how I finished up right here, however I believed this submit was good.
I don’t understand who you’re however definitely you are going
to a well-known blogger in the event you aren’t already. Cheers!