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()