Welcome, guest | Sign In | My Account | Store | Cart

Recipe 577601 revision 1 

##
## XMLPush
##
## A small utility to move XML as exported from SQL Server or MS Access to a
## mySQL table. 
##
## Not too fancy, but gets the job done. As with all recipes, season to taste
## depending on your needs.
##
##  Albert L. Perrien II
##  08 March 2011
##  aperrien@razodisk.com
##

import re
from lxml import etree
import MySQLdb


def XMLPush(datafile,server,dbuser,password,dbname,table)


    def quote(text):
        return "'" + text + "'"

    def doublequote(text):
        return '"' + text + '"'

    connection = MySQLdb.connectionect (host = server,
                           user = dbuser,
                           passwd = password,
                           db = dbname)

    cursor = connection.cursor()

    tree = etree.parse(datafile)

    root = tree.getroot()


    # Parse out data from XML
    data = []
    for child in root:
        datarow = {}
        for leaf in child:
            datarow[leaf.tag] = leaf.text
        data.append(datarow)

    # Push data to DB
    statements = []
    for row in data:
        columns = []
        values = []
        for item in row:
            # Reformatting data to mySQL formats
            columns.append(item.replace(" ",""))
            temp = row[item]
            values.append(quote(temp.replace("'","")))

        # Push data to table
        statement = "INSERT INTO " + table + " (" + ",".join(columns) + ") VALUES (" + \
                    ",".join(values) + ")"

        statements.append(statement)

    for statement in statements:
        cursor.execute(statement)
        connection.commit()
        
    
    connection.close()



XMLPush("MainTable.xml","mySQL-Server","mySQL-User","mySQL-Password","DB_Name","Table")

History