郵便番号 CSV データを SQLite 3 にインポート(SQLite 3 を使用)
このページでは,日本郵政「ゆうびんホームページ」で公開されている 2種類の郵便番号データのCSV(カンマ区切り値)形式ファイルを活用する.
- 住所の郵便番号(CSV形式)(ken_all.csv)
- 事業所の個別郵便番号(CSV形式)(jigyosyo.csv)
これらのファイルを,リレーショナルデータベース管理システム SQLite 3 のテーブル(テーブル名は ken_all)へ効率的に格納する.
SQLite 3 の詳細情報: 別ページ »にまとめ
謝辞:
・郵便番号データの提供に関して,日本郵政に深く感謝申し上げます
1. 前準備
Python の準備(Windows,Ubuntu 上)
- Windows での Python 3.10,関連パッケージ,Python 開発環境のインストール(winget を使用しないインストール): 別ページ »で詳しく説明
- Ubuntu では,システム Pythonを効率的に活用できる.Python3 開発用ファイル,pip, setuptools のインストール手順: 別ページ »で詳しく解説
【サイト内の関連ページ】
- Python の総合的なまとめ: 別ページ »で解説
- Google Colaboratory の実践的な使い方: 別ページ »で詳しく説明
【関連する外部ページ】 Python の公式サイト: https://www.python.org/
csvkit のインストール
- Windows における csvkit のインストール手順
Windows で,コマンドプロンプトを管理者として起動する.
以下のコマンドを実行する.
python -m pip install csvkit
- Ubuntu における csvkit のインストール手順
端末で,次のコマンドを実行する.
sudo apt -y install csvkit python3-csvkit
SQLite 3 のインストール
SQLite 3は,高性能なリレーショナルデータベース管理システムである.
郵便番号 CSV データの準備
- 具体的なダウンロード手順は,別ページ »で詳しく説明.2つのCSV ファイルのダウンロードを完了させる必要がある
郵便番号 CSV データから,SQLite 3 データベースを生成 (csvsql, sqlite3 を使用)
- 処理対象の CSV ファイル名: ken_all.csv, jigyosyo.csv
- 生成するSQLite3 データベース名: zip.db
- 作成されるテーブル名: ken_all, jigyosyo
それぞれのテーブルスキーマは以下の通りである.
- KEN_ALL(a0, a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14)
- JIGYOSYO(a0, a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12)
- 最初に,2つのファイルの文字コードを,UTF-8 形式に変換する
- SQLite 3 データベースの生成手順
- Windows 環境での実行手順
del zip.db echo a0,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14 > k.csv type ken_all.csv >> k.csv csvsql --db sqlite:///zip.db --insert k.csv echo a0,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12 > j.csv type jigyosyo.csv >> j.csv csvsql --db sqlite:///zip.db --insert j.csv echo alter table k rename to ken_all; | sqlite3 zip.db echo alter table j rename to jigyosyo; | sqlite3 zip.db echo select * from ken_all limit 20; | sqlite3 zip.db echo select * from jigyosyo limit 20; | sqlite3 zip.db
正常に処理できたことを確認するため,データを表示する.
- Ubuntu 環境での実行手順
rm -f zip.db echo "a0,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14" > k.csv cat KEN_ALL.CSV >> k.csv csvsql --db sqlite:///zip.db --insert k.csv echo "a0,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12" > j.csv cat JIGYOSYO.CSV >> j.csv csvsql --db sqlite:///zip.db --insert j.csv echo "alter table k rename to ken_all;" | sqlite3 zip.db echo "alter table j rename to jigyosyo;" | sqlite3 zip.db echo "select * from ken_all limit 20;" | sqlite3 zip.db echo "select * from jigyosyo limit 20;" | sqlite3 zip.db
正常に処理できたことを確認するため,先頭部分のデータを表示する.
- Windows 環境での実行手順
データベースの整合性確認
- SQLite3 の起動方法
sqlite3 zip.db
- テーブル JIGYOSYO の属性 a0, a3, a4 の整合性確認
JIGYOSYO テーブルにおいて,a0 の値が一意に定まれば,a4 の値も一意に定まることを確認する.
以下の SQL クエリで検証する.結果が空の場合,整合性が保たれている.
create table T as select distinct a0, a3, a4 from JIGYOSYO; SELECT * FROM T WHERE a0 IN ( SELECT a0 FROM T group by a0 HAVING COUNT(*) > 1 );
- テーブル KEN_ALL の属性 a0, a6, a7 の整合性確認
KEN_ALL テーブルにおいて,a0 の値が一意に定まれば,a7 の値も一意に定まることを確認する.
以下の SQL クエリで検証する.結果が空の場合,整合性が保たれている.
drop table T; create table T as select distinct a0, a6, a7 from KEN_ALL; SELECT * FROM T WHERE a0 IN ( SELECT a0 FROM T group by a0 HAVING COUNT(*) > 1 );
- テーブル JIGYOSYO における読みがなデータの完全性確認
以下の SQL クエリで検証する.結果が空の場合,すべての読みがなが正しく格納されている.
select * from JIGYOSYO where a1 = '""';
- テーブル KEN_ALL における読みがなデータの完全性確認
以下の SQL クエリで検証する.結果が空の場合,すべての読みがなが正しく格納されている.
select * from JIGYOSYO where a2 = '""'; select * from JIGYOSYO where a3 = '""'; select * from JIGYOSYO where a4 = '""';