よちよちpython

独習 python/Qpython/Pydroid3/termux/Linux

【TermuxとsqliteとVim】全国の郵便番号CSVをsqliteにインポートする(メモ

SQLの練習がてら、全国郵便番号のCSVファイルをsqliteにインポートする際に手こずったのでメモ。



※ Termuxとvimを触ったことがある方対象記事です。





実行環境



  • Termuxへのsqliteのインストールは
$ pkg install sqlite



  • sqliteの対話モードの起動方法

f:id:chayarokurokuro:20220122222516j:plain

sqliteのデータファイル指定の場合。ファイルが無ければ新規作成されます(テーブル作成後に)

$ sqlite3 データファイル名

プロンプトがsqlite >に変わります。あとはSQLを打つのみ。



今回おこった問題と対処

何をしようとしていたかですが、

  1. 郵便局のサイトからDLした全国郵便番号のCSVを、
  2. AndroidのTermuxアプリにインストールしたsqliteの対話式シェルでインポートしようとしたら、
  3. 異常に時間が掛かる上に、途中でエラーを起こしてストップした。
  4. そこで、vimCSVを開いたら文字化けしていた。
  5. 従って、vimCSVutf-8に変換し上書き保存し直して、
  6. sqliteの対話式シェルからコマンドでCSVをインポートした。
  7. めでたし。



郵便番号のCSV(全国一括 ken_all.zip)入手

こちらでDLしました

郵便番号データダウンロード - 日本郵便



  • zipファイルなのでLinuxコマンドで解凍します。
$ unzip ken_all.zip

カレントディレクトリにKEN_ALL.CSVで解凍されます。



sqliteCSVをインポートします

データベースファイル名「sample.db」(新規作成)、テーブル名「Code」(新規作成)


失敗編

# データベースファイルに接続
$ sqlite3 sample.db

# sqliteが起動しプロンプトが変わる

/* csvモードに変更 */
sqlite > .mode csv

/* ファイル名とテーブル名を指定してインポート */
sqlite > .import KEN_ALL.CSV Code

/* ズラズラっとインポートしている風な文字が流れ、やがて止まる。*/

/* テーブル確認 */
sqlite > .tables
sqlite >

テーブルが作られていない。



VimCSVutf-8に変換、保存作業

参考 : VimでShift_JISのファイルをUTF-8に変換 | ハックノート



  • vimCSVを確認のため開く
$ vim KEN_ALL.CSV

文字化けしていた。



vimCSVファイルを開く。

$ vim KEN_ALL.CSV



文字コードの確認。

:set fenc?

/* Shift_JISの場合はfileencoding=latin1 と表示される。された。*/



shift-jisで開き直す。vimは閉じず、そのまま以下のコマンドを入力する。

:e ++enc=shift-jis

これで文字化けの表示が直った。



文字コードutf-8に変換する。

:set fenc=utf-8



最後に上書き保存する。utf-8で保存される。

:wq

CSVutf-8で上書き保存完了。



sqliteCSVのインポート再チャレンジ

shift-jisからutf-8に変換し直したCSVをインポートします。上手くいくかな?

# sqliteファイルに接続
$ sqlite3 sample.db


/* モードをcsvに変更 */
sqlite > .mode csv

/* 対話sqliteでコマンド入力しCSVインポート */
sqlite > .import KEN_ALL.CSV Code
sqlite >

1秒も掛からずプロンプトが表示された。もう完了?



データがインポートされたか確認する。

/* テーブルの存在確認 */
sqlite > .tables
Code

/* 全データ表示 */
sqlite > select * from Code;

/* ズラズラっとデータが表示 */

一番最後の沖縄県の与那国のデータまで表示し終えるのに15秒ほど掛かった。エラーなし、全てインポートされたようです。



sqlite対話モードの終了方法

sqlite > .exit

/* または */

sqlite > .quit

/* または */
sqlite > .q

/* または */
Ctrl + d キー のショートカット



補足

CSVをテーブル無しの状態でインポートすると、先頭行が見出し行になる。型は全てTEXT型。
今回いれた郵便番号のCSVファイルではヘッダー行が入っていなかったので変更が必要になる。

f:id:chayarokurokuro:20220122222616j:plain

sqliteのDBビューワーアプリで見てみたら、レコードが1つのカラムに入っていた…全然ダメじゃん…



上手く行くやり方

郵便番号 CSV データを SQLite 3 にインポート(SQLite 3 を使用)

こちらのリンクは福山大学の金子研究室ページ。同じテーマの記事がある。


方法として、

  1. CSVファイルをutf-8で保存する。
  2. sqlite3の対話モードを起動。
  3. テーブルを作成。15カラムでそれぞれ型指定する。
  4. モードをcsvに変更。
  5. utf-8で保存したCSVファイルをテーブルにインポート。


12万行ありますが、データのインポートは一瞬で終わる。
テーブルは先に作っておいた方が良さそうです。たとえばPandasのpd.read_sql()sqliteを読み込む場合、テーブルを作らずインポートしたものは型が全てTEXT型で入れられる為にobject型になる。いちいちastype(int)みたいな変換をしないといけない。



以上です。