Return to Snippet

Revision: 48375
at June 30, 2011 01:06 by eristoddle

Initial Code
#!/usr/bin/env python

# Run with no args for usage instructions
# Notes:
#  - will probably insert duplicate records if you load the same file twice
#  - assumes that the number of fields in the header row is the same
#    as the number of columns in the rest of the file and in the database
#  - assumes the column order is the same in the file and in the database
# Speed: ~ 1s/MB

import sys
import MySQLdb
import csv

def main(user, db, table, csvfile):

        conn = getconn(user, db,)
    except MySQLdb.Error, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit (1)
    cursor = conn.cursor()

    loadcsv(cursor, table, csvfile)


def getconn(user, db, passwd=""):
    conn = MySQLdb.connect(host = "localhost",
                           user = user,
                           passwd = "",
                           db = db)
    return conn

def nullify(L):
    """Convert empty strings in the given list to None."""

    # helper function
    def f(x):
        if(x == ""):
            return None
            return x
    return [f(x) for x in L]

def loadcsv(cursor, table, filename):

    Open a csv file and load it into a sql table.
     - the first line in the file is a header

    f = csv.reader(open(filename))
    header =
    numfields = len(header)

    query = buildInsertCmd(table, numfields)

    for line in f:
        vals = nullify(line)
        cursor.execute(query, vals)


def buildInsertCmd(table, numfields):

    Create a query string with the given table name and the right
    number of format placeholders.

    >>> buildInsertCmd("foo", 3)
    'insert into foo values (%s, %s, %s)' 
    assert(numfields > 0)
    placeholders = (numfields-1) * "%s, " + "%s"
    query = ("insert into %s" % table) + (" values (%s)" % placeholders)
    return query

if __name__ == '__main__':
    # commandline execution

    args = sys.argv[1:]
    if(len(args) < 4):
        print "error: arguments: user db table csvfile"


Initial URL

Initial Description

Initial Title
Python CSV to MySQL

Initial Tags

Initial Language