【Good By エクセル】 csvをテーブルに変換するためのsqlを生成するpython script #Python

さよならexecel

僕はエクセルというアプリケーションが嫌いです。

1万行とかのcsvをエクセルに読みこんで、フィルターかけたり、関数をドラッグしてコピーしたりめんどくさいことみんな結構やるじゃないですか。
ロード時間なんか、日がくれんじゃねぇかってくらい時間かかる時あるし、macだとヌルヌル動かないし、そんなんでフリーズしてくれた日には、もうやる気なくして早退も辞さないわけですね。

あんなん発狂しますよ！！！

エンジニアなら、csv importからのデーター加工・抽出からのexportからのftpサーバーアップロードまで全部コマンドだけで終わらせたい！
エクセルなんて邪道 of 邪道！
信頼すべきデーターベースゥー！！！

要は、csvからcreate table文を作成して、load dataまで一括までするスクリプトさえあればもう、エクセルなんて、お別れwithout even saying goodbyですよ。
そんなpython scriptが本日のテーマどす。

解説

pythonにはcsv_to_tableというcsvから型を推測し、一行目のヘッダー部分をカラム名としてcreate table文作ってくれるpipがあるので、この機能に、load data文までの作成機能を加えたscriptを今回作りました。

使い方はgithubにまんま書いたのでそのまんま実行してくださいw

gitclone

git clone https://github.com/ShusukeOtani/table_stmt_generator_from_csv

pipとpyenvをinstallして、python2.7.1で実行してください。

依存pipインストール

pip install -r requirements.txt

オプション引数には -dと-tと-pを用意してます。
-dはデータベースのタイプを判定するためのオプションです。
-d mならmysql, -d pならpostgreです。デフォルト値はmysqlです。
-tはTempテーブルにするか否かのboolです。カットした場合はpermamentテーブルにします。

-pでは主キーを指定できます。
-p idならカラムidがPKとなります。

例を書いておきます。
まずは主キーなし、permamentテーブル、mysqlならoptionはいらないので、単純にcsvのfile pathを指定してください。

exmaple1

python execute.py ./example/test.csv

result1

DROP TABLE IF EXISTS table_stmt_generator_from; CREATE TABLE table_stmt_generator_from (
    id smallint,
    pref text
);LOAD DATA LOCAL INFILE '/Users/200302/table_stmt_generator_from_csv/example/test.csv' INTO TABLE table_stmt_generator_from FIELDS TERMINATED BY ','  IGNORE 1 LINES;

postgre、tempテーブルかつ pkにidというカラムを使いたいなら、以下のoptionを加えてください。

example2

`python execute.py -d p -t -p id ./example/test.csv

result2

DROP TABLE IF EXISTS test; CREATE TEMPORARY TABLE test (
    id smallint,
    pref text
, PRIMARY KEY(id));COPY test FROM '/Users/200302/table_stmt_generator_from_csv/example/test.csv' CSV HEADER;

後日談

広告の仕事の一つにフィード作成というものがあります。
CriteoやIndeedといったベンダーに対してそれぞれ最適化された案件リストのフィードを作成するわけですが、
複数のDBを見に行く場合JOINできないんですよね。
この課題解決のためにこのscriptを書きました。
実際の運用では、このscript使って下記の一連を行うapplicationにしています。

1.それぞれのDBでselect文を発行する。
2.結果をcsvとしてexportする。
3.このscriptを使ってローカルのDBにcsvをimportして、テーブルを生成する。
4.ローカルDBでJOIN して最終的なフィードデータを生成する。
5.フィードデータをftpサーバーにアップロードする。