Thursday, December 5, 2024
Google search engine
HomeLanguagesHow to store XML data into a MySQL database using Python?

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.

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:
  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.

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.

RELATED ARTICLES

24 COMMENTS

  1. 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!

  2. 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!!

  3. 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!

  4. 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!

  5. 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!

  6. 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!

  7. 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!

  8. 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

  9. 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!

  10. 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?

  11. 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

Most Popular

Recent Comments