PDOで郵便番号辞書データベース構築の記録

萩原賢二のコーディングストック > プログラミング > PDOで郵便番号辞書データベース構築の記録

2019.07.17

データベースに接続、運用するときそれぞれのデータベース独自に用意された方法で、それぞれの作法で処理をします。
使用しているデータベースが何であるかにかかわらず、同じ関数を使用してクエリの発行やデータの取得が行えるます。
例えばにローカル開発環境で mysqlで開発していたシステムを本番環境はPostgreSQLを使用するといった場合があります。
Heroku: クラウド・アプリケーション・プラットフォームでは PostgreSQL が採用されています。

ここでは「mysql」にて郵便番号辞書をPDOを使って構築したサンプルを利用して構築したときの記録です。

PDO経由でデータベースに接続
データベースを作成
テーブルを作成
作成したテーブルをコマンドラインで確認
郵便番号データのインサート
都道府県、市町村コードのインサート
コマンドラインでデータを確認

PDO経由でデータベースに接続する。

// mysqlへの接続例
$dsn = ‘mysql:dbname=uriage;host=localhost’;
// PostgreSQLの接続例
$dsn = ‘pgsql:dbname=uriage host=localhost port=5432’;
// sqliteへの接続例
$dsn = ‘sqlite:[sqlite3のデータベースファイルのパス( c:\xxxx\yyyy\zzz.db )];

//PDOでデータベースに接続
define('DB_DATABASE', 'postcode');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'xxxxxxxxxx');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try {
     //データベースの接続　PDO インスタンスの生成
    $$dbh = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo $e->getMessage();
    exit;
}
　・
　・
// 接続を閉じる
$dbh = null;

//PDOでデータベースに接続

define('DB_DATABASE', 'postcode');

define('DB_USERNAME', 'dbuser');

define('DB_PASSWORD', 'xxxxxxxxxx');

define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try {

//データベースの接続　PDO インスタンスの生成

$$dbh = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {

echo $e->getMessage();

exit;

}

　・

// 接続を閉じる

$dbh = null;

PDO::setAttribute — 属性を設定する
PDO::ATTR_ERRMODE: エラーレポート
PDO::ERRMODE_SILENT: エラーコードのみ設定する
PDO::ERRMODE_WARNING: E_WARNING を発生させる
PDO::ERRMODE_EXCEPTION: 例外を投げる
PDO 定義済み定数

メニューに戻る

データベースを作成する。

phpMyAdminやMySQL Workbenchでデータベースを管理する場合が多いと思いますが、今回はMacのターミナルでコマンド操作での管理といたします。
これらのツール類はまた、別の機会でご紹介することとさせていただきます

//root権限でログインしてデータベースを作成します。
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
　・
　・
mysql> 

// データベース作成
mysql> create database postaldictionary;
Query OK, 1 row affected (0.66 sec)

//一般ユーザーである dbuserに作成さたデータベースの権限を付与します。
mysql> grant all on postcode.* to dbuser@localhost;
Query OK, 0 rows affected (0.48 sec)
// 同時にユーザーを新規に作成しパスワードも作成することもできます。
mysql> grant all on postcode.* to dbuser@localhost identified by 'xxxxxxxxx'

// dbuserでログインし直します。
mysql> quit
Bye
[root@lesson csvdata]# mysql -p -u dbuser
Enter password: 

// dbuserが所有するデーターベースを確認します。
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| postcode           |
+--------------------+

//root権限でログインしてデータベースを作成します。

$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

　・

mysql>

// データベース作成

mysql> create database postaldictionary;

Query OK, 1 row affected (0.66 sec)

//一般ユーザーである dbuserに作成さたデータベースの権限を付与します。

mysql> grant all on postcode.* to dbuser@localhost;

Query OK, 0 rows affected (0.48 sec)

// 同時にユーザーを新規に作成しパスワードも作成することもできます。

mysql> grant all on postcode.* to dbuser@localhost identified by 'xxxxxxxxx'

// dbuserでログインし直します。

mysql> quit

Bye

[root@lesson csvdata]# mysql -p -u dbuser

Enter password:

// dbuserが所有するデーターベースを確認します。

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| postcode |

+--------------------+

メニューに戻る

テーブルを作成する

テーブルを作成するのにPDOを利用したPHPスクリプトを作成してみようと思います。
PDOクラス概要のメソッドで実行する際の主なメソッド
(1) exec(): SQL ステートメントを実行し、作用した行数を返す
(2) query(): SQL ステートメントを実行し、結果セットを PDOStatement オブジェクトとして返す
(3) prepare(): 文を実行する準備を行います。複数回実行されるSQL
=> 結果　ステートメントオブジェクトを返す

// create文の作成
// 都道府県テーブルの作成
$create_prefecture = <<< EOM
create table prefecture (
pref_no int(11) not null  PRIMARY KEY,
pref_name varchar(30) not null,
pref_kana varchar(30) not null);
EOM;
// PDOのコンストラクターで生成したインスタンスにより作成します。
$res = $dbh->query($create_prefecture);

// 市町村テーブルの作成
$create_city = <<< EOM
create table city (
city_code int(11) not null PRIMARY KEY,
pref_no int(11) not null,
city_name varchar(30) not null,
city_kana varchar(30) not null);
EOM;
$res = $dbh->query($create_city);

// 町域テーブルの作成
$create_post = <<< EOM
create table zipcode (
pno int(11) NOT NULL PRIMARY KEY,
post_code int(11) not null,
city_code int(11) not null,
old_code int(11),
town_kana text,
town_name text);
EOM; 
$res = $dbh->query($create_post);

// create文の作成

// 都道府県テーブルの作成

$create_prefecture = <<< EOM

create table prefecture (

pref_no int(11) not null PRIMARY KEY,

pref_name varchar(30) not null,

pref_kana varchar(30) not null);

EOM;

// PDOのコンストラクターで生成したインスタンスにより作成します。

$res = $dbh->query($create_prefecture);

// 市町村テーブルの作成

$create_city = <<< EOM

create table city (

city_code int(11) not null PRIMARY KEY,

pref_no int(11) not null,

city_name varchar(30) not null,

city_kana varchar(30) not null);

EOM;

$res = $dbh->query($create_city);

// 町域テーブルの作成

$create_post = <<< EOM

create table zipcode (

pno int(11) NOT NULL PRIMARY KEY,

post_code int(11) not null,

city_code int(11) not null,

old_code int(11),

town_kana text,

town_name text);

EOM;

$res = $dbh->query($create_post);

メニューに戻る

作成したテーブルをコマンドラインで確認します。

// 郵便番号データベースの確認
mysql> show fields from prefecture;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| pref_no   | int(11)     | NO   | PRI | NULL    |       |
| pref_name | varchar(30) | NO   |     | NULL    |       |
| pref_kana | varchar(30) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show fields from city;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| city_code | int(11)     | NO   | PRI | NULL    |       |
| pref_no   | int(11)     | NO   |     | NULL    |       |
| city_name | varchar(30) | NO   |     | NULL    |       |
| city_kana | varchar(30) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show fields from post;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| pno       | int(11) | NO   | PRI | NULL    |       |
| post_code | int(11) | NO   |     | NULL    |       |
| city_code | int(11) | NO   |     | NULL    |       |
| old_code  | int(11) | YES  |     | NULL    |       |
| town_kana | text    | YES  |     | NULL    |       |
| town_name | text    | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
6 rows in set (0.00 sec)

// 郵便番号データベースの確認

mysql> show fields from prefecture;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

| pref_no | int(11) | NO | PRI | NULL | |

+-----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> show fields from city;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

| city_code | int(11) | NO | PRI | NULL | |

| pref_no | int(11) | NO | | NULL | |

+-----------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> show fields from post;

+-----------+---------+------+-----+---------+-------+

+-----------+---------+------+-----+---------+-------+

| pno | int(11) | NO | PRI | NULL | |

| post_code | int(11) | NO | | NULL | |

| city_code | int(11) | NO | | NULL | |

| old_code | int(11) | YES | | NULL | |

+-----------+---------+------+-----+---------+-------+

6 rows in set (0.00 sec)

メニューに戻る

郵便番号データのインサート

テーブルが作成できましたので次は実際のデータをインサートしていきます。
今回参照した郵便番号データダウンロードより CSV形式のデータを利用しました。
郵便番号データの説明全国地方公共団体コード（JIS X0401、X0402）も使用しています。全国市町村コードから全国市町村コードをダウンロードしました。

function convertZip( $pno,$file,&$pdo ){
// ---------------------------
// 引数: $pno 都道府県コード
//     $file 都道府県ごとのCSVファイルのパス
//     &$pdo PDOインスタンスの参照
//
// PDO::prepare ,PDOStatement::bindParamでデータをインサート
// ---------------------------	
    $stmt_post 
        = $pdo->prepare("insert into zipcode (pno,post_code,city_code,old_code,town_kana,town_name) ".
                  "values (:pno, :post_code, :city_code, :old_code, :town_kana, :town_name)");

    $data = explode("\r\n",mb_convert_encoding(file_get_contents($file), 'UTF-8', 'sjis-win'));
    $city_code_back=0;
    $pno = $pno*100000;
    echo "pno=$pno<br>";
    $stmt_post->bindParam(':pno', $pno, PDO::PARAM_INT);
    $stmt_post->bindParam(':post_code', $post_code, PDO::PARAM_INT);
    $stmt_post->bindParam(':city_code', $city_code,	 ::PARAM_INT);
    $stmt_post->bindParam(':old_code', $old_code, PDO::PARAM_INT);
    $stmt_post->bindParam(':town_kana', $town_kana, PDO::PARAM_STR);
    $stmt_post->bindParam(':town_name', $town_name, PDO::PARAM_STR);
    foreach( $data as $line ){
        $pcode = explode(",", $line);
        if( !$city_code_back || $pcode[0] != $city_code ){
          $city_code_back =  $pcode[0];
          $city_code = $city_code_back;
        }
        ++$pno;
        $post_code = str_replace("\"","",$pcode[2]);
        $old_code  = str_replace("\"","",$pcode[1]);
        $town_kana = str_replace("\"","",mb_convert_kana( $pcode[5], "RCKV"));
        $town_name = str_replace("\"","",$pcode[8]);
            $stmt3->execute();
    }
}

function convertZip( $pno,$file,&$pdo ){

// ---------------------------

// 引数: $pno 都道府県コード

// $file 都道府県ごとのCSVファイルのパス

// &$pdo PDOインスタンスの参照

// PDO::prepare ,PDOStatement::bindParamでデータをインサート

// ---------------------------

$stmt_post

= $pdo->prepare("insert into zipcode (pno,post_code,city_code,old_code,town_kana,town_name) ".

"values (:pno, :post_code, :city_code, :old_code, :town_kana, :town_name)");

$data = explode("\r\n",mb_convert_encoding(file_get_contents($file), 'UTF-8', 'sjis-win'));

$city_code_back=0;

$pno = $pno*100000;

echo "pno=$pno<br>";

$stmt_post->bindParam(':pno', $pno, PDO::PARAM_INT);

$stmt_post->bindParam(':post_code', $post_code, PDO::PARAM_INT);

$stmt_post->bindParam(':city_code', $city_code, ::PARAM_INT);

$stmt_post->bindParam(':old_code', $old_code, PDO::PARAM_INT);

$stmt_post->bindParam(':town_kana', $town_kana, PDO::PARAM_STR);

$stmt_post->bindParam(':town_name', $town_name, PDO::PARAM_STR);

foreach( $data as $line ){

$pcode = explode(",", $line);

if( !$city_code_back || $pcode[0] != $city_code ){

$city_code_back = $pcode[0];

$city_code = $city_code_back;

}

++$pno;

$post_code = str_replace("\"","",$pcode[2]);

$old_code = str_replace("\"","",$pcode[1]);

$town_kana = str_replace("\"","",mb_convert_kana( $pcode[5], "RCKV"));

$town_name = str_replace("\"","",$pcode[8]);

$stmt3->execute();

}

参考：　
PDO::prepare
PDOStatement::bindParam

メニューに戻る

都道府県、市町村コードのインサート

function convertPrefCity(&$pdo){	
//--------------------------------------------------------------
// 引数: &$pno PDOインスタンス の参照
// 全国地方公共団体コード（JIS X0401、X0402）のダウンロードした全国版のファイルより、
// 都道府県、市町村コードのデータベースにインサートします。
//----------------------------------------------------------------
    $stmt_pref = $pdo->prepare("insert into prefecture (pref_no, pref_name, pref_kana) values (?, ?, ?)");
    $stmt_city = $pdo->prepare("insert into city (city_code, pref_no, city_name, city_kana) values (:city_code,:pref_no,:city_name,:city_kana )");
    $jis_x0402 = "data/JIS_X0402.csv";
    $data = explode("\r\n",mb_convert_encoding(file_get_contents($jis_x0402), 'UTF-8', 'sjis-win'));
    foreach( $data as $city ){
        $pref=explode(",",$city);
        pref_no = (int)($pref[0]/1000);
        if( !$prefs[$pref_no]){
            $prefs[$pref_no] = array( $pref_no,$pref[1],$pref[3]);
            $stmt_pref->execute( [$pref_no,$pref[1],$pref[3] ] );			
        }
        $cities[$pref[0]] = array($pref[0],$pref_no,$pref[2],$pref[4]);
        $stmt_city->execute(
			[':city_code' => $pref[0],
			 ':pref_no' => $pref_no,
			 ':city_name' => $pref[2],
			 ':city_kana' => $pref[4]]);
    }
}

function convertPrefCity(&$pdo){

//--------------------------------------------------------------

// 引数: &$pno PDOインスタンスの参照

// 全国地方公共団体コード（JIS X0401、X0402）のダウンロードした全国版のファイルより、

// 都道府県、市町村コードのデータベースにインサートします。

//----------------------------------------------------------------

$stmt_pref = $pdo->prepare("insert into prefecture (pref_no, pref_name, pref_kana) values (?, ?, ?)");

$stmt_city = $pdo->prepare("insert into city (city_code, pref_no, city_name, city_kana) values (:city_code,:pref_no,:city_name,:city_kana )");

$jis_x0402 = "data/JIS_X0402.csv";

$data = explode("\r\n",mb_convert_encoding(file_get_contents($jis_x0402), 'UTF-8', 'sjis-win'));

foreach( $data as $city ){

$pref=explode(",",$city);

pref_no = (int)($pref[0]/1000);

if( !$prefs[$pref_no]){

$prefs[$pref_no] = array( $pref_no,$pref[1],$pref[3]);

$stmt_pref->execute( [$pref_no,$pref[1],$pref[3] ] );

}

$cities[$pref[0]] = array($pref[0],$pref_no,$pref[2],$pref[4]);

$stmt_city->execute(

[':city_code' => $pref[0],

':pref_no' => $pref_no,

':city_name' => $pref[2],

':city_kana' => $pref[4]]);

}

メニューに戻る

コマンドラインでデータを確認してみる

// データの参照
mysql> select
    -> zipcode.post_code as 郵便番号,
    -> prefecture.pref_name as 都道府県名,
    -> city.city_name as 市区町村名,
    -> zipcode.town_name as 町域名,
    -> prefecture.pref_kana as 都道府県カナ名,
    -> city.city_kana as 市区町村カナ名,
    -> zipcode.town_kana as 町域カナ名
    -> from
    -> zipcode 
    -> inner join city on zipcode.city_code = city.city_code
    -> inner join prefecture on city.pref_no = prefecture.pref_no
    -> where zipcode.post_code = 1000001\G
*************************** 1. row ***************************
  郵便番号: 1000001
都道府県名: 東京都
市区町村名: 千代田区
   町域名: 千代田
都道府県カナ名: トウキョウト
市区町村カナ名: チヨダク
   町域カナ名: チヨダ
1 row in set (0.15 sec)