Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Jan 21st, 2011  |  syntax: Python  |  size: 1.83 KB  |  views: 168  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. #!/usr/bin/python
  2.  
  3. """
  4. This script, despite its name (sorry for a little confusion)
  5. does not actually perform any imports. It just generates a set
  6. of sql statements that, when fed to an appropriate sql shell
  7. will perform the export.
  8.  
  9. Note, that it does not support bulk insert (yet)
  10.  
  11. It is meant to be used as an input for aforementioned shell command
  12. """
  13.  
  14. from optparse import OptionParser
  15. from lxml import etree
  16. import sys
  17. reload(sys)
  18. sys.setdefaultencoding('utf-8')
  19.  
  20. parser = OptionParser()
  21. parser.add_option('-i', '--input',
  22.     dest = "input",
  23.     help = "Name of the input XML file",
  24.     metavar = "FILENAME",
  25.         )
  26.  
  27. parser.add_option('-o', '--output',
  28.     dest = "output",
  29.     help = "Name of the output file to use. By default the output is written to stdout",
  30.     metavar = "FILENAME",
  31.         )
  32.  
  33. parser.add_option('-t', '--table',
  34.     dest = "table",
  35.     help = "Name of the table, on which to perform insert operations",
  36.     metavar = "TABLENAME")
  37.  
  38.  
  39. opts, args = parser.parse_args()
  40. if not opts.input:
  41.     raise "The name of the input file is a mandatory argument"
  42.  
  43.  
  44. # Make the value SQL-type friendly :) This is a heuristic
  45. def sqlize(val):
  46.     # 0. None -- make it a NULL
  47.     if val is None:
  48.         return 'NULL'
  49.     # 1. Number? -- Leave as it is
  50.     try:
  51.         float(val)
  52.         return val
  53.     except ValueError:
  54.         # 2. String? -- quote
  55.         return "'%s'" % unicode(val.replace("'", "\\'"))
  56.  
  57. resfile = sys.stdout
  58. if opts.output is not None:
  59.     resfile = file(opts.output, 'w')
  60.  
  61.  
  62. root = etree.parse(opts.input).getroot()
  63. for row in root.iter('row'):
  64.     f_name_list = [f.attrib['name'] for f in row]
  65.     f_val_list = [f.text for f in row]
  66.     resfile.write("INSERT INTO %s(%s) values(%s);\n" % (opts.table, ', '.join(f_name_list), ', '.join([sqlize(v) for v in f_val_list])))
clone this paste RAW Paste Data