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

このページでは,日本郵政「ゆうびんホームページ」で公開されている 2種類の郵便番号データのCSV(カンマ区切り値)形式ファイルを活用する.

  1. 住所の郵便番号(CSV形式)(ken_all.csv)
  2. 事業所の個別郵便番号(CSV形式)(jigyosyo.csv)

これらのファイルを,リレーショナルデータベース管理システム SQLite 3 のテーブル(テーブル名は ken_all)へ効率的に格納する.

SQLite 3 の詳細情報: 別ページ »にまとめ

謝辞:

・郵便番号データの提供に関して,日本郵政に深く感謝申し上げます

1. 前準備

Python の準備(Windows,Ubuntu 上)

サイト内の関連ページ

関連する外部ページPython の公式サイト: https://www.python.org/

csvkit のインストール

SQLite 3 のインストール

SQLite 3は,高性能なリレーショナルデータベース管理システムである.

郵便番号 CSV データの準備

郵便番号 CSV データから,SQLite 3 データベースを生成 (csvsql, sqlite3 を使用)

  1. 最初に,2つのファイルの文字コードを,UTF-8 形式に変換する
  2. 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
      

      正常に処理できたことを確認するため,先頭部分のデータを表示する.

データベースの整合性確認

  1. SQLite3 の起動方法
    sqlite3 zip.db
    
  2. テーブル 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 );
    
  3. テーブル 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 );
    
  4. テーブル JIGYOSYO における読みがなデータの完全性確認

    以下の SQL クエリで検証する.結果が空の場合,すべての読みがなが正しく格納されている.

    select * from JIGYOSYO where a1 = '""';
    
  5. テーブル KEN_ALL における読みがなデータの完全性確認

    以下の SQL クエリで検証する.結果が空の場合,すべての読みがなが正しく格納されている.

    select * from JIGYOSYO where a2 = '""';
    select * from JIGYOSYO where a3 = '""';
    select * from JIGYOSYO where a4 = '""';