Minireceta : Programa que convierte un fichero .csv en un conjunto de sentencias SQL para inyectar en una base de datos (por James Mills):

#!/usr/bin/env python
# Module:   csv2sql
# Date:     14th September 2008
# Author:   James Mills, prologic at shortcircuit dot net dot au
Tool to convert CSV data files into SQL statements that
can be used to create SQL tables. Each line of text in
the file is read, parsed and converted to SQL and output
to stdout (which can be piped).
__desc__ = "CSV to SQL Tool"
__version__ = "0.2"
__author__ = "James Mills"
__email__ = "%s, prologic at shortcircuit dot net dot au" % __author__
__url__ = ""
__copyright__ = "CopyRight (C) 2008 by %s" % __author__
__license__ = "GPL"
import os
import csv
import optparse
from cStringIO import StringIO
USAGE = "%prog [options] <file>"
VERSION = "%prog v" + __version__
def parse_options():
 """parse_options() -> opts, args
 Parse any command-line options given returning both
 the parsed options and arguments.
 parser = optparse.OptionParser(usage=USAGE, version=VERSION)
 parser.add_option("-t", "--table",
       action="store", default=None, dest="table",
       help="Specify table name")
 parser.add_option("-f", "--fields",
       action="store", default=None, dest="fields",
       help="Specify a list of fields")
 opts, args = parser.parse_args()
 if len(args) < 1:
    raise SystemExit, 1
 return opts, args
def mkBuffer(fd):
 buffer = StringIO()
 return buffer
def readCSV(file):
 if type(file) == str:
    fd = open(file, "rU")
    fd = file
 fd = mkBuffer(fd)
 sniffer = csv.Sniffer()
 dialect = sniffer.sniff(fd.readline())
 reader = csv.reader(fd, dialect)
 for line in reader:
    yield line
def main():
 opts, args = parse_options()
 file = args[0]
 if file == "-":
    fd = sys.stdin
    if opts.table is None:
       print "ERROR: No table specified and stdin used."
       raise SystemExit(1)
    fd = open(file, "rU")
    if opts.table is None:
       table = os.path.splitext(file)[0]
       table = opts.table
 for line in readCSV(fd):
    if opts.fields:
       fields = [x.strip() for x in opts.fields.split(",")]
       fields = "(%s)" % ",".join(fields)
       fields = ""
    values = ",".join(["\"%s\"" % x for x in line])
    print "INSERT INTO %s %s VALUES (%s);" % (table, fields, values)
if __name__ == "__main__":

<Volver a la sección de Tutoriales Python>

  • programacion/tutoriales/python/csv2sql.txt
  • Última modificación: 30-04-2012 18:05
  • por sromero