Loading xml file data to oracle table using python

Sample xml file (test.xml):

<?xml version="1.0"?>

<data>

    <customer name="Ravi" >

        <email>lravikumarvsp@gmail.com</email>

        <phone>9999999999</phone>

    </customer>

    <customer name="kumar" >

        <email>kumar@gmail.com</email>

    </customer>

    <customer name="lanke" >

        <email>lanke@gmail.com</email>

        <phone>8888888888</phone>

    </customer>

    <customer name="ravikumar" >

        <phone>7777777777</phone>

        <address>

            <street>kukatpally</street>

        </address>

    </customer>

</data>

Python script for parsing xml file data to data frame:

import pandas as pd

import xml.etree.cElementTree as et

#Reading Data from the xml file

parsedXML = et.parse( "test.xml" )                                                                         

dfcols = ['name','email','phone','street']                                                                                

df = pd.DataFrame(columns=dfcols)

def getvalueofnode( node ):

                return node.text if node is not None else None

for node in parsedXML.getroot():                                                                              

                name = node.attrib.get('name')

                email = node.find('email')

                phone = node.find('phone')

                street = node.find('address/street')

                df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)

print (df)                                                                                                                                  

Run the program as below:

Now create a table with column names in the data frame.

CREATE TABLE TEST 

   (                          "NAME" VARCHAR2(200 BYTE),

                               "EMAIL" VARCHAR2(200 BYTE),

                               "PHONE" VARCHAR2(200 BYTE),

                               "STREET" VARCHAR2(200 BYTE)

   )

Python script for loading xml file to oracle table.

import pandas as pd

import cx_Oracle

import xml.etree.cElementTree as et

#Reading Data from the xml file

parsedXML = et.parse( "test.xml" )                                                                         

dfcols = ['name','email','phone','street']                                

df = pd.DataFrame(columns=dfcols)

def getvalueofnode( node ):

                               return node.text if node is not None else None

for node in parsedXML.getroot():                                                                               

                               name = node.attrib.get('name')

                               email = node.find('email')

                               phone = node.find('phone')

                               street = node.find('address/street')

                               df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)

print (df)                                                                                                                             

#Loading Data into the Database Table

host1 = '172.16.11.32'

port1 = '1521'

sid1 = 'srcdb'

schema1 = 'system'

pwd1 = 'Admin123'

sdsn = cx_Oracle.makedsn(host1, port1, service_name=sid1)    

connect=cx_Oracle.connect(schema1, pwd1, sdsn)                                                        

cursor=connect.cursor()

dataframe_list=df.values.tolist()                                                                                                              # convert the dataframe to list

sql='INSERT INTO TEST values (:1,:2,:3,:4)'

for index,elem in enumerate(dataframe_list):                                                   

                               cursor.execute(sql,dataframe_list[index]) 

                               connect.commit()                                                             

Run the script to load the data to oracle table now

Check the table for data