rulururu

post Convert Xml to Sql

January 4th, 2008

Filed under: Database, Python — Kai @ 11:53 am

You need to convert an xml file to sql the simpliest way? I got a script for you that makes out of an xml file a clear sql dump for example to insert into a mysql database.

import sys
import xml.dom.minidom as minidom
 
#field list - same in SQL + XML
fields = "name, phone, adress"
tablename = "contacts" # table
recordset = "contact" 
 
def main(args):
    f = open(args[1])
    doc = minidom.parseString(f.read())
    f.close()
    for i in doc.getElementsByTagName(recordset):
        vars = []
        vals = []
        for j in fields.split(", "):
            for k in i.getElementsByTagName(j):
                if (k and k.firstChild):
                     vars.append(j)
                     vals.append(k.firstChild.nodeValue.replace("'", "\\'"))
        sqlstring = "INSERT INTO %s (%s) VALUES ('%s');" % (
                      tablename, ", ".join(vars), "', '".join(vals))
        print sqlstring.encode('utf-8')
 
if __name__ == "__main__":
    if len(sys.argv) < 1:
        print "Usage: %s <filename>.xml"
    else:
        main(sys.argv)

xml2sql.py (right-click Save as…)

Usage is:

xml2sql.py foo.xml

Of course you first have to change the rootnode name, subnodes and fields in the script.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

ruldrurd
Powered by WordPress, Content and Design by Kai Bellmann
Entries (RSS) and Comments (RSS)