2012/03/09

csvをsqlにする(insert, update)

検索すればいくらでも出てくるようなものをあえて作りました。csvをinsert文、update文に変換するコマンド。

昨日のこれと合わせると結構使えるかも。
例えばこういうCSVを
$ cat sample.csv 
aaa,bbb,ccc,ddd,eee
1,2,3,4,5
10,20,30,40,50
100,200,300,400,500
csv2sqlに渡すと
$ csv2sql sample.csv 
INSERT INTO sample (aaa , bbb , ccc , ddd , eee) VALUES ('1' , '2' , '3' , '4' , '5')
INSERT INTO sample (aaa , bbb , ccc , ddd , eee) VALUES ('10' , '20' , '30' , '40' , '50')
INSERT INTO sample (aaa , bbb , ccc , ddd , eee) VALUES ('100' , '200' , '300' , '400' , '500')

$ csv2sql -k aaa sample.csv
UPDATE sample SET aaa='1', bbb='2', ccc='3', ddd='4', eee='5' WHERE aaa='1'
UPDATE sample SET aaa='10', bbb='20', ccc='30', ddd='40', eee='50' WHERE aaa='10'
UPDATE sample SET aaa='100', bbb='200', ccc='300', ddd='400', eee='500' WHERE aaa='100'

$ csv2sql -k "aaa,ccc" sample.csv
UPDATE sample SET aaa='1', bbb='2', ccc='3', ddd='4', eee='5' WHERE aaa='1' AND ccc='3'
UPDATE sample SET aaa='10', bbb='20', ccc='30', ddd='40', eee='50' WHERE aaa='10' AND ccc='30'
UPDATE sample SET aaa='100', bbb='200', ccc='300', ddd='400', eee='500' WHERE aaa='100' AND ccc='300'
こんな感じ。CSVファイル名がTable名に、CSVのheader行がカラム名に。update文でキーまで更新してることに今ごろ気づいたけど気にしない。

ソース

Gaucheで書きました。ソースはこちら。
#!/usr/local/bin/gosh
(use srfi-1)
(use text.csv)
(use file.util)
(use util.list)
(use gauche.parseopt)
(define (usage)
(print "Usage: csv2sql [options ...] <csv-file>")
(print " h|help - print this usage")
(print " k|keys - primary key column names (delimiter=,)")
(print " d|delimitter - (default=,)")
(print " n|tablename")
(print " t|tsv")
(print)
(print "SQL query")
(print " default => insert")
(print " -k|keys => update")
(print "Table name and Column name")
(print " Table name: <csv-file> name without extentions")
(print " Column name: header row column names")
(exit 1))
(define (csv->list file :optional (delim #\,))
(port->list (make-csv-reader delim)
(if file
(open-input-file file)
(current-input-port))))
(define (csv->sql csv table-name . key-col-names)
(if (null? csv)
'()
(let ((header (car csv))
(csv (cdr csv)))
(if (null? key-col-names)
(csv->insert-str csv table-name header)
(apply csv->update-str csv table-name header key-col-names)))))
(define (path->filename-without-ext path)
(receive (dir filename ext)
(decompose-path path)
filename))
(define (keys-str->list keys-str)
(string-split keys-str #\,))
(define (csv->insert-str csv table-name column-names)
(define (make-values row)
(intersperse "," (map (pa$ format "'~a'") row)))
(map (^[row]
(format "INSERT INTO ~a ~a VALUES ~a;"
table-name
(intersperse "," column-names)
(make-values row)))
csv))
(define (csv->update-str csv table-name column-names . key-col-names)
(define (make-set row)
(apply string-append
(intersperse
", "
(fold-right (^[colname val acc]
(if (member colname key-col-names)
acc
(cons (format "~a='~a'" colname val) acc)))
'() column-names row))))
(define (make-where row)
(apply string-append
(intersperse
" AND "
(map (^[key-col-name]
(let1 idx (list-index (pa$ equal? key-col-name)
column-names)
(format "~a='~a'" key-col-name (~ row idx))))
key-col-names))))
(map (^[row]
(format "UPDATE ~a SET ~a WHERE ~a;"
table-name
(make-set row)
(make-where row)))
csv))
(define (main args)
(let-args (cdr args)
((help "h|help" => usage)
(keys "k|keys=s")
(delim "d|delimitter=s" #\,)
(tsv? "t|tsv")
(tablename "n|tablename=s")
(else (opt . _)
(print "Unknown option : " opt)
(usage))
. rest)
(let ((csv (csv->list (and (not (null? rest))(car rest))
(if tsv? #\tab delim)))
(filename (cond ((and (null? rest) tablename) tablename)
((null? rest) "<TableName>")
(else (path->filename-without-ext (car rest)))))
(keys (if keys (keys-str->list keys) '())))
(for-each print (apply csv->sql csv filename keys)))))
view raw csv2sql.scm hosted with ❤ by GitHub


追記

update文でキーまで更新してることに今ごろ気づいたけど気にしない。 

対応した。


0 件のコメント:

コメントを投稿