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.
It’s going to be finish of mine day, but before ending I am reading this impressive post to increase my experience.
Woah! I’m really enjoying the template/theme of this site.
It’s simple, yet effective. A lot of times it’s very hard to
get that “perfect balance” between usability and visual appeal.
I must say you have done a excellent job with this. Also, the
blog loads super fast for me on Internet explorer.
Superb Blog!
Thanks for sharing your thoughts. I really appreciate your efforts and I will be waiting for your next post thank you once again.
If some one desires expert view about blogging and site-building then i propose him/her to pay a quick visit this weblog,
Keep up the good job.
This is a topic that’s close to my heart… Cheers!
Where are your contact details though?
Hello, i feel that i noticed you visited my site so i got here to
return the favor?.I’m attempting to find things to enhance my site!I suppose its ok to make use of some
of your ideas!!
I do not even know how I stopped up right here, but I thought this submit used
to be great. I do not recognise who you might be however definitely you’re going to a well-known blogger for those who are not already.
Cheers!
This design is incredible! You most certainly know how to keep a reader amused.
Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Excellent job.
I really enjoyed what you had to say, and more than that, how you presented it.
Too cool!
Good response in return of this question with genuine arguments and telling all concerning that.
Greetings! This is my 1st comment here so
I just wanted to give a quick shout out and say I truly enjoy reading through
your articles. Can you suggest any other blogs/websites/forums that deal with the same topics?
Thank you!
Since the admin of this web page is working, no uncertainty very soon it will be famous, due
to its quality contents.
Have you ever considered about including a little bit more than just your articles?
I mean, what you say is valuable and everything.
But imagine if you added some great pictures or video clips
to give your posts more, “pop”! Your content is excellent but with images and clips, this website could definitely be one of the greatest in its niche.
Fantastic blog!
I was recommended this web site by means of my
cousin. I am not certain whether or not this publish is written by means of
him as nobody else recognize such specific about my difficulty.
You’re amazing! Thank you!
What’s up it’s me, I am also visiting this web site on a regular basis,
this web site is genuinely good and the people are in fact sharing
nice thoughts.
I know this website gives quality depending posts and
additional material, is there any other web page which gives these kinds of information in quality?
Awesome! Its truly remarkable article, I have got much clear idea about from this article.
Right now it seems like Movable Type is the best blogging platform out there right now.
(from what I’ve read) Is that what you are using on your blog?
I don’t know whether it’s just me or if everybody else encountering
problems with your website. It looks like some of the text on your posts are running off
the screen. Can somebody else please provide feedback and let me
know if this is happening to them as well?
This could be a problem with my browser because I’ve had this
happen previously. Thank you
Hello! Quick question that’s entirely off topic.
Do you know how to make your site mobile friendly? My
weblog looks weird when viewing from my iphone4. I’m trying
to find a template or plugin that might be able to resolve
this issue. If you have any suggestions, please share.
Many thanks!
I was curious if you ever thought of changing the structure of your blog?
Its very well written; I love what youve got to say.
But maybe you could a little more in the way of content
so people could connect with it better. Youve got an awful lot of text for only having 1 or 2 images.
Maybe you could space it out better?
I am regular reader, how are you everybody? This piece of writing posted at this
website is truly fastidious.
Great information. Lucky me I ran across your website by chance
(stumbleupon). I’ve saved it for later!
I love your blog.. very nice colors & theme.
Did you create this website yourself or did you hire someone to do it for
you? Plz answer back as I’m looking to design my own blog and would
like to know where u got this from. thanks
Appreciate the recommendation. Let me try it out.