昨日のこれと合わせると結構使えるかも。
例えばこういうCSVを
$ cat sample.csv aaa,bbb,ccc,ddd,eee 1,2,3,4,5 10,20,30,40,50 100,200,300,400,500csv2sqlに渡すと
$ 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で書きました。ソースはこちら。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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))))) |
追記
update文でキーまで更新してることに今ごろ気づいたけど気にしない。
対応した。
0 件のコメント:
コメントを投稿