Python
MySQL
PostgreSQL

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

More than 1 year has passed since last update.


さよなら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

https://github.com/ShusukeOtani/table_stmt_generator_from_csv


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サーバーにアップロードする。