pythonでtsvからsqlを作る

タブ区切りのデーターファイルからsqlを生成する。
id¥tname¥tmsg
から
insert into table ( id,name,msg ) values (%s,%s,%s),(%s,%s,%s),(%s,%s,%s)

psycopg2のプレースホルダーの形式でバルクinsert文へ

引数にファイル名を渡す
python hoge.py data.tsv

import sys
cols = ['id', 'name', 'msg']

def main():
    try:
        for d in parse_line():
            print make_str(d)
    except IOError as e:
        print 'IOError!!!'
        print e
    except Exception as e:
        print 'Exeption'
        print "Unexpected error:", sys.exc_info()[0]
        print e

def make_str(data):
    f = []
    for x in cols:
        f.append('%s')
    fs = "(" + ",".join(f) + ")"

    fss = []
    cond = []
    for td in data:
        fss.append(fs)
        for d in td:
            cond.append(d)
    
    sql = "insert into table ( {0} ) values  {1} ".format(",".join(cols), ",".join(fss))
    print cond
    
    return sql

def parse_line():
    nums = 0
    nmax = 3
    ds = []
    for line in open(sys.argv[-1]):
        d = line[:-1]
        if nums == nmax:
            yield ds
            ds = []
            nums = 0
        ds.append(d.split("\t"))
        nums += 1
    yield ds

if __name__ == '__main__':
    main()