Untitled

#!/usr/bin/python

"""
This script, despite its name (sorry for a little confusion)
does not actually perform any imports. It just generates a set
of sql statements that, when fed to an appropriate sql shell
will perform the export.

Note, that it does not support bulk insert (yet)

It is meant to be used as an input for aforementioned shell command
"""

from optparse import OptionParser
from lxml import etree
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

parser = OptionParser()
parser.add_option('-i', '--input',
    dest = "input",
    help = "Name of the input XML file",
    metavar = "FILENAME",
        )

parser.add_option('-o', '--output',
    dest = "output",
    help = "Name of the output file to use. By default the output is written to stdout",
    metavar = "FILENAME",
        )

parser.add_option('-t', '--table',
    dest = "table",
    help = "Name of the table, on which to perform insert operations",
    metavar = "TABLENAME")


opts, args = parser.parse_args()
if not opts.input:
    raise "The name of the input file is a mandatory argument"


# Make the value SQL-type friendly :) This is a heuristic
def sqlize(val):
    # 0. None -- make it a NULL
    if val is None:
        return 'NULL'
    # 1. Number? -- Leave as it is
    try:
        float(val)
        return val
    except ValueError:
        # 2. String? -- quote
        return "'%s'" % unicode(val.replace("'", "\\'"))

resfile = sys.stdout
if opts.output is not None:
    resfile = file(opts.output, 'w')


root = etree.parse(opts.input).getroot()
for row in root.iter('row'):
    f_name_list = [f.attrib['name'] for f in row]
    f_val_list = [f.text for f in row]
    resfile.write("INSERT INTO %s(%s) values(%s);\n" % (opts.table, ', '.join(f_name_list), ', '.join([sqlize(v) for v in f_val_list])))