Convert XML to Json format and store in Database using Python

Date posted:23 /11 / 2018

Introduction:

In this article, it is applicable to convert only a very small Xml file format  in to Json  format. The converted Json format will be inserted in to a  existing database in Mysql.  This example is explained based on Linux environment in Centos 7.

Prerequisites:

  • For centOS and RHEL  based environment: 
    # sudo yum install python
  • Pip installation 
    # curl -O https://bootstrap.pypa.io/get-pip.py

    And  then execute,

    #python get-pip.py

    confirm the installed PIP version,

    # pip –version

Importing Modules and their Pip Installation:

While Importing, if the packaging Modules of  any one among the above missed, will arise  Import error. To eradicate the error follow these step by step modules to import  using pip Installation.

1. Importing Mysqldb:

Yum install MYSQL-python

2. Importing mysql.connector:

pip install mysql-connector-python

3.Importing  xmltodict:

pip install xmltodict

4.Importing Json:

pip install xmljson

Executing  script:

NOTE: This script is executed only for small XML format convert in to JSON  format and  to  store  in  a table row of  MySQL DB.

The Key  role  must be followed before  proceeding  this  script are as follows.

  • Enter   your  appropriate mysql username and password  in the script .Please replace with the below script  instead of “usernamexxx” and “passxxx“.
  • create a database Name  of your choice  and replace in the below script instead of  “DBname created“

  NOTE:  Database must be already created and appropriate DB name should given in script.

  •  BY the execution of  script Table will automatically generated in Mysql . The table name called Jsonobject  and having two column named id and Json.
  • All converted Json format  will store in first row of Json.
  • If xml file is Big, it won’t fix in the first Json row of DB and it will result in error.
  • Create a  file name called  xmltoJson.py and execute the script with your convenience.
import MySQLdb
import mysql.connector
import xmltodict
import pprint
import json
import sys

try:
my_xml= sys.argv[1]
except:
print("Help : please enter your valid xml file")
if len(sys.argv) < 2:
print 'You failed to provide your valid xml file'
sys.exit(1)

with open(my_xml) as fd:
doc = xmltodict.parse(fd.read())

pp = pprint.PrettyPrinter(indent=1000)
pp.pprint(json.dumps(doc))

mydb = MySQLdb.connect("localhost","usernamexxx","Passxxx","DBname created" )

print(mydb)
mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")
for x in mycursor:

mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
try:
mycursor.execute("CREATE TABLE Jsonobject (id VARCHAR(255), Json VARCHAR(255))")
except:
print("Table created successfully. Inserting jason objects ...")
insert = "INSERT INTO Jsonobject (id) VALUES (%d)"
insert = "INSERT INTO Jsonobject (Json) VALUES (%s)"
mycursor.execute(insert, (json.dumps(doc),))
print(mycursor.rowcount, "record inserted.")
mydb.commit()

Output Execution and result:

Sample xml file is taken and saved as  examples.xml. I have attached my xml file below.

<mec>
<student>
<firstname>James</firstname>
<lastname>Smith</lastname>
</student>
</mec>

Let us see how the xml to json  occurs.

The code is scripted in a manner, one xml file executed at once.During execution follow this below example.

Python  programfile.py  xmlfile

Python  xmltojson.py examples.xml

1.Converted Json format:

'{"mec": {"student": {"firstname": "James", "lastname": "Smith"}}}'

2.Inserting conformation:

3.Output in Mysql DB:

Use DBname;
select * from Jsonobject;

4.The expected output is acheived.

That’s it! Hope it’s useful. Please leave us the comment for any queries.

 

 

 

 

 

Post Views: 575

Leave a Reply