#!/usr/bin/env python
"""
Load a CSV file, find a cell with a SQL statement in it, like this::

    sql: insert into person (id, name) values (ID, NAME);
    
Then iterate through the csv file again, assuming we have
columns with a header of ID and NAME, replacing the values in
the SQL statement for every row. Print the replacements on
stdout.
"""
__author__ = 'Martin Blais <blais@furius.ca>'

import sys, os, re, csv

def find_sql(csvfn):
    for irow, row in enumerate(csv.reader(open(csvfn))):
        for icol, value in enumerate(row):
            mo = re.match('sql:(.*)', value)
            if mo:
                return mo.group(1).strip(), irow
    return None, None

def main():
    import optparse
    parser = optparse.OptionParser(__doc__.strip())
    opts, args = parser.parse_args()
    if len(args) != 1:
        parser.error("Usage: FILE.csv")
    fn = args[0]

    statement, irow = find_sql(fn)
    if statement is None:
        raise SystemExit("Error: Could not find SQL statement embedded in spreadsheet.")

    it = enumerate(csv.reader(open(fn)))
    _, head = it.next()
    modrows = [x for x in enumerate(head) if x[1]]
    for i, row in it:
        if i == irow:
            continue
        sql = statement
        for idx, column in modrows:
            newvalue = row[idx]
            if not newvalue:
                break
            sql = sql.replace(column, newvalue)
        else:
            print sql



if __name__ == '__main__':
    main()