どんなCSVも楽々DBに取り込めるようにしてみた

たむけん（エンジニア）

どんなCSVも楽々DBに取り込めるようにしてみた

たむけん（エンジニア）

2022.03.17

IT技術

Python

ものすごく簡単に

どんなCSVも楽々DBに取り込めるようにしてみました。

ものすごく簡単にです。

たぶんですけど。

とりあえず動作確認してみたい方はこちらから諸々参照して試してみてください。

ちなみにPythonで実装、DBはMySQL限定となってます。

あしからず。

ざっくり説明すると、

取り込みたいCSVファイルの情報を定義して、
CSVファイルを所定のディレクトリに置いて、
pythonを実行する

となります。

create文とかinsert文とかの作成は要りません。

あらやだ、簡単ですね。

まずは動作検証結果からご紹介（検証はMacでしてます）。

検証1: 郵便番号CSVをぱぱっと取り込んでみる

日本郵便のサイトからCSVを取得。

折角なので（？）全国一括を取り込んでみます。

DLしたCSVの情報を調べて定義を追加します（詳細は GithubのReadme を参照）。

で、DLしたCSVを↑で定義したディレクトリに置いて実行。

1> pipenv run python 01_import.py postcd recreate
2Loading .env environment variables...
3[2022-03-11 17:06:58,495][INFO] 01_import.py:22 - # CSV Import Start.
4[2022-03-11 17:06:58,495][INFO] 01_import.py:62 - ## KEN_ALL.CSV -> t_postcd Start.
5[2022-03-11 17:06:58,937][INFO] db_service.py:78 - ### table recreate has done.  [t_postcd]

実行開始！

しかし全然終わらない。

うーん、12.3MB。

1[2022-03-11 17:06:58,937][INFO] db_service.py:78 - ### table recreate has done.  [t_postcd]
2[2022-03-11 17:14:43,482][INFO] db_service.py:103 - ### table import has done.  [124,531 records]
3[2022-03-11 17:14:43,484][INFO] 01_import.py:96 - ## KEN_ALL.CSV -> t_postcd End.
4[2022-03-11 17:14:43,484][INFO] 01_import.py:102 - # CSV Import End. [result: add 124,531 records to t_postcd]

おわたー。約8分。

ではMySQL確認。

1mysql> show tables;
2+--------------+
3| Tables_in_db |
4+--------------+
5| t_postcd     |
6+--------------+
71 rows in set (0.00 sec)
8
9mysql> show full columns from t_postcd;
10+--------+------+-------------+------+-----+---------+-------+---------------------------------+---------+
11| Field  | Type | Collation   | Null | Key | Default | Extra | Privileges                      | Comment |
12+--------+------+-------------+------+-----+---------+-------+---------------------------------+---------+
13| pc_001 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references |         |
14| pc_002 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references |         |
15| pc_003 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references |         |
16〜〜〜
17| pc_014 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references |         |
18| pc_015 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references |         |
19+--------+------+-------------+------+-----+---------+-------+---------------------------------+---------+
2015 rows in set (0.00 sec)

テーブル作成されてる。

ちなみに、項目は設定したプレフィックス＋連番、型は`TEXT`（ええ、全部TEXT型です）、CSVファイル1行目がヘッダ項目ならそれを項目コメントに設定（郵便番号CSVはヘッダ項目無しなので無し）。

1mysql> select count(*) from t_postcd;
2+----------+
3| count(*) |
4+----------+
5|   124531 |
6+----------+
71 row in set (0.01 sec)

約12万件。

本社（福岡）の郵便番号で検索してみる。

1mysql> select pc_003, pc_008, pc_009 from t_postcd where pc_003 = '8120038';
2+---------+--------------------+-----------+
3| pc_003  | pc_008             | pc_009    |
4+---------+--------------------+-----------+
5| 8120038 | 福岡市博多区       | 祇園町    |
6+---------+--------------------+-----------+
71 row in set (0.25 sec)

いいですね（いいんですよね？）。

なんでだろう、世田谷にあこがれる。

1mysql> select pc_003, pc_008, pc_009 from t_postcd where pc_008 = '世田谷区';
2+---------+--------------+--------------------------------+
3| pc_003  | pc_008       | pc_009                         |
4+---------+--------------+--------------------------------+
5| 1540000 | 世田谷区     | 以下に掲載がない場合           |
6| 1560044 | 世田谷区     | 赤堤                           |
7| 1540001 | 世田谷区     | 池尻                           |
8〜〜〜
9| 1580097 | 世田谷区     | 用賀                           |
10| 1540023 | 世田谷区     | 若林                           |
11+---------+--------------+--------------------------------+
1262 rows in set (0.25 sec)

グッド。

次。

検証2: 駅データのCSVをさささっと取り込んでみる

駅データ.jp からCSVをダウンロード（無料ですが要会員登録）。

郵便番号と同じ要領でDLしたCSVの情報を調べて定義を追加して実行。

1> pipenv run python 01_import.py station recreate
2Loading .env environment variables...
3[2022-03-11 18:42:54,673][INFO] 01_import.py:22 - # CSV Import Start.
4[2022-03-11 18:42:54,673][INFO] 01_import.py:62 - ## station20220310free.csv -> t_station Start.
5[2022-03-11 18:42:54,752][INFO] db_service.py:78 - ### table recreate has done.  [t_station]
6[2022-03-11 18:43:35,333][INFO] db_service.py:103 - ### table import has done.  [10,883 records]
7[2022-03-11 18:43:35,335][INFO] 01_import.py:96 - ## station20220310free.csv -> t_station End.
8[2022-03-11 18:43:35,335][INFO] 01_import.py:102 - # CSV Import End. [result: add 10,883 records to t_station]

1万件は1分弱。

MySQL検証。

1mysql> show tables;
2+--------------+
3| Tables_in_db |
4+--------------+
5| t_postcd     |
6| t_station    |
7+--------------+
82 rows in set (0.00 sec)
9
10mysql> show full columns from t_station;
11+--------+------+-------------+------+-----+---------+-------+---------------------------------+----------------+
12| Field  | Type | Collation   | Null | Key | Default | Extra | Privileges                      | Comment        |
13+--------+------+-------------+------+-----+---------+-------+---------------------------------+----------------+
14| st_001 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references | station_cd     |
15| st_002 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references | station_g_cd   |
16| st_003 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references | station_name   |
17〜〜〜
18| st_014 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references | e_status       |
19| st_015 | text | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references | e_sort         |
20+--------+------+-------------+------+-----+---------+-------+---------------------------------+----------------+
2115 rows in set (0.00 sec)

テーブル作成OK。

駅CSVはヘッダ行有りなので項目コメントにその値がセットされてる。

1mysql> select count(*) from t_station;
2+----------+
3| count(*) |
4+----------+
5|    10883 |
6+----------+
71 row in set (0.00 sec)

駅って日本にそんなにあるですねー？

とか思ったですが、どうやら駅と沿線でユニークとなるデータの模様。

ので、駅名と都道府県CDでグルーピングして件数カウントしてみる。

1mysql> select count(*) as st_uniq_cnt from (select 1 from t_station group by st_003, st_007) tmp;
2+-------------+
3| st_uniq_cnt |
4+-------------+
5|        9265 |
6+-------------+
71 row in set (0.03 sec)

それでも9265駅ってゆー。

コトコトことでん！

1mysql> select st_003, st_006, st_009 from t_station where st_006 = '99802';
2+--------------------------------------+--------+--------------------------------------------------+
3| st_003                               | st_006 | st_009                                           |
4+--------------------------------------+--------+--------------------------------------------------+
5| 高松築港                             | 99802  | 高松市多玉藻町９７-２                            |
6| 片原町                               | 99802  | 高松市鶴屋町９-１                                |
7| 瓦町                                 | 99802  | 高松市常磐町１-３-１                             |
8〜〜〜
9| 榎井                                 | 99802  | 仲多度郡琴平町榎井４００-３                      |
10| 琴電琴平                             | 99802  | 仲多度郡琴平町３６０-２２                        |
11+--------------------------------------+--------+--------------------------------------------------+
1223 rows in set (0.03 sec)

沿線数が多い駅Top10！

1mysql> select st_003, count(*) as cnt from t_station group by st_003, st_007 order by cnt desc limit 10;
2+--------+-----+
3| st_003 | cnt |
4+--------+-----+
5| 新宿   |  13 |
6| 東京   |  11 |
7| 横浜   |  11 |
8| 渋谷   |  11 |
9| 大宮   |   9 |
10| 池袋   |   9 |
11| 上野   |   8 |
12| 新橋   |   7 |
13| 京都   |   7 |
14| 岡山   |   7 |
15+--------+-----+
1610 rows in set (0.04 sec)