郵便番号テーブル ken_all を 3つのテーブル zips, kens, shichosons に分解

【概要】

このページでは，日本郵便の郵便番号データダウンロードページで公開されている以下の2つの郵便番号データの CSV（カンマ区切り値）形式ファイルについて解説する．

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

これらのデータには，潜在的な冗長性が存在する（「第三正規形」を満たしていないため，データ更新時に不整合が発生する可能性がある）．この問題を解決するため，郵便番号データを3つのテーブルに分解し，冗長性を排除する手順を説明する．

【目次】

前準備
郵便番号テーブル ken_all を 3 つのテーブル zips, kens, shichosons に分解

前準備

使用するソフトウェア

Python （標準ライブラリの sqlite3 モジュールを使用）がインストール済みであること．

Pythonのインストールを行い、Pythonのプログラムを実行する環境を整える。扱う環境は、Windows搭載パソコンである。金子研究室では、Python 3.12.10を推奨する。

[Windows での Python 3.12 のインストール手順を見るには、ここをクリック]

Windows での Python 3.12 のインストール

以下のいずれかの方法でPython 3.12をインストールする。Pythonがインストール済みの場合、この手順は不要である。

方法 1：winget によるインストール

【インストールコマンドの実行方法】

管理者権限でコマンドプロンプトを起動する（手順：Windowsキーまたはスタートメニュー → cmd と入力 → 右クリック → 「管理者として実行」）。そして、コマンド全体をコマンドプロンプトにコピー＆ペーストする。

--scope machine を指定することで、システム全体（全ユーザー向け）にインストールされる。このオプションの実行には管理者権限が必要である。インストール完了後、コマンドプロンプトを再起動するとPATHが反映される。

REM Python 3.12 をシステム領域にインストール
winget install --id Python.Python.3.12 -e --scope machine --silent --accept-source-agreements --accept-package-agreements --override "/quiet InstallAllUsers=1 PrependPath=1 Include_test=0 Include_pip=1 Include_launcher=1 InstallLauncherAllUsers=1 TargetDir=\"C:\Program Files\Python312\""

REM Python と Scripts を PATH 先頭に追加
powershell -NoProfile -Command "$p='C:\Program Files\Python312'; $s=\"$p\Scripts\"; $c=[Environment]::GetEnvironmentVariable('Path','Machine'); if((Test-Path $p) -and (';'+$c+';' -notlike \"*;$p;*\") -and (';'+$c+';' -notlike \"*;$s;*\")){[Environment]::SetEnvironmentVariable('Path',\"$p;$s;$c\",'Machine')}"

方法 2：インストーラーによるインストール

Python公式サイト（https://www.python.org/downloads/）にアクセスし、「Download Python 3.x.x」ボタンからWindows用インストーラーをダウンロードする。
ダウンロードしたインストーラーを実行する。
初期画面の下部に表示される「Add python.exe to PATH」にチェックを入れてから「Customize installation」を選択する。このチェックを入れ忘れると、コマンドプロンプトから python コマンドを実行できない。
「Install Python 3.xx for all users」にチェックを入れ、「Install」をクリックする。

インストールの確認

コマンドプロンプトで以下を実行する。

python --version

バージョン番号（例：Python 3.12.x）が表示されればインストール成功である。「'python' は、内部コマンドまたは外部コマンドとして認識されていません。」と表示される場合は、インストールが正常に完了していない。

あらかじめ決めておく事項

このページでは，SQLite 3 データベースの生成を実施する．まず，SQLite 3 データベースのデータベース名を決定する必要がある．本解説では，以下のように設定する．

データベース名: zipdb
データベース名は任意に設定可能であるが，半角文字（英字および英記号）のみを使用し，スペースを含まないようにする．

テーブルの準備

「郵便番号 CSV データを SQLite 3 にインポート（SQLite 3 を使用）」の Web ページの手順に従って，郵便番号テーブル ken_all の作成を完了しておく．

郵便番号テーブル ken_all を 3 つのテーブル zips, kens, shichosons に分解

Pythonプログラムを使用して，郵便番号テーブル ken_allを以下の3つのテーブルに分解する．

郵便番号　・・・ zips テーブル
県　・・・ kens テーブル
市町村　・・・ shichosons テーブル

郵便番号辞書には「町域」という要素が存在するが，本設計では町域に対応するテーブルは作成しない．その理由は，「町域」テーブルを作成した場合，一意な「キー」が存在しない（より正確には，全属性を組み合わせなければキーとして機能しない）ためである．「町域」には同一漢字で読み方が異なるケース（例：「上川」の「かみがわ」，「かみかわ」）が存在するため，一意なキーを設定できない．

zips, kens, shichosons のテーブル定義

◆ Python プログラム

import sqlite3

con = sqlite3.connect(r"C:\SQLiteDB\zipdb")
cur = con.cursor()

cur.executescript("""
    CREATE TABLE kens (
        id        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        ken_kanji TEXT UNIQUE NOT NULL,
        ken_kana  TEXT UNIQUE NOT NULL
    );

    CREATE TABLE shichosons (
        jiscode         INTEGER PRIMARY KEY NOT NULL CHECK (jiscode >= 1000 AND jiscode <= 50000),
        ken_kanji       TEXT NOT NULL,
        shichoson_kanji TEXT NOT NULL,
        shichoson_kana  TEXT
    );

    CREATE TABLE zips (
        id           INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        zipcode      INTEGER NOT NULL,
        zip_old      INTEGER NOT NULL,
        jiscode      INTEGER NOT NULL REFERENCES shichosons(jiscode),
        choiki_kanji TEXT,
        choiki_kana  TEXT,
        flag10       TEXT    NOT NULL,
        flag11       INTEGER NOT NULL CHECK (flag11 >= 0 AND flag11 <= 1),
        flag12       INTEGER NOT NULL CHECK (flag12 >= 0 AND flag12 <= 3),
        flag13       INTEGER NOT NULL CHECK (flag13 >= 0 AND flag13 <= 1),
        info14       INTEGER,
        info15       INTEGER
    );
""")

con.commit()
con.close()

zips, kens, shichosons テーブルの作成 (populate)

◆ Python プログラム

import sqlite3

# mydb01 から ken_all テーブルのデータを読み出し，zipdb にコピーする
src_con = sqlite3.connect(r"C:\SQLiteDB\mydb01")
dst_con = sqlite3.connect(r"C:\SQLiteDB\zipdb")

# mydb01 の ken_all テーブルを zipdb に複製する
dump_sql = "\n".join(src_con.iterdump())
dst_con.executescript(dump_sql)

src_con.close()

# kens, shichosons, zips テーブルにデータを投入する
cur = dst_con.cursor()

cur.execute("""
    INSERT INTO kens (ken_kanji, ken_kana)
    SELECT DISTINCT ken_kanji, ken_kana
    FROM ken_all
""")

cur.execute("""
    INSERT INTO shichosons (jiscode, ken_kanji, shichoson_kanji, shichoson_kana)
    SELECT DISTINCT jiscode, ken_kanji, shichoson_kanji, shichoson_kana
    FROM ken_all
""")

cur.execute("""
    INSERT INTO zips (zipcode, zip_old,
                jiscode, choiki_kanji, choiki_kana,
                flag10, flag11, flag12, flag13, info14, info15)
    SELECT zipcode, zip_old,
           jiscode, choiki_kanji, choiki_kana,
           flag10, flag11, flag12, flag13, info14, info15
    FROM ken_all
""")

dst_con.commit()
cur.execute("VACUUM")
dst_con.close()

【各テーブルの中身の先頭部分】

import sqlite3

con = sqlite3.connect(r"C:\SQLiteDB\zipdb")
cur = con.cursor()

print("--- kens ---")
for row in cur.execute("SELECT * FROM kens LIMIT 3"):
    print(row)

print("--- shichosons ---")
for row in cur.execute("SELECT * FROM shichosons LIMIT 3"):
    print(row)

print("--- zips ---")
for row in cur.execute("SELECT * FROM zips LIMIT 3"):
    print(row)

con.close()

(オプション) 再構成

◆ Python プログラム

import sqlite3
import os

# 各テーブルの SQL ダンプを取得する
con = sqlite3.connect(r"C:\SQLiteDB\zipdb")

# テーブルごとにダンプを取得する関数
def dump_table(connection, table_name):
    """指定テーブルに関連する SQL 文のみを抽出して返す"""
    lines = []
    for line in connection.iterdump():
        if table_name in line:
            lines.append(line)
    return "\n".join(lines)

kens_sql = dump_table(con, "kens")
shichosons_sql = dump_table(con, "shichosons")
zips_sql = dump_table(con, "zips")

# ダンプ結果をファイルに保存する
with open(r"C:\SQLiteDB\kens.sql", "w") as f:
    f.write(kens_sql)
with open(r"C:\SQLiteDB\shichosons.sql", "w") as f:
    f.write(shichosons_sql)
with open(r"C:\SQLiteDB\zips.sql", "w") as f:
    f.write(zips_sql)

con.close()

# 既存の zipdb を削除する
if os.path.exists(r"C:\SQLiteDB\zipdb"):
    os.remove(r"C:\SQLiteDB\zipdb")

# ダンプファイルから zipdb を再構成する
con = sqlite3.connect(r"C:\SQLiteDB\zipdb")

with open(r"C:\SQLiteDB\kens.sql", "r") as f:
    con.executescript(f.read())
with open(r"C:\SQLiteDB\shichosons.sql", "r") as f:
    con.executescript(f.read())
with open(r"C:\SQLiteDB\zips.sql", "r") as f:
    con.executescript(f.read())

con.execute("VACUUM")
con.close()