【コラム】

日本語で10行プログラミング

48 お手軽データベース「SQLite」を使う

クジラ飛行机  [2006/02/02]

48/90

お手軽データベースSQLiteを使って郵便番号の検索ソフトを作る

先日、なでしこのバージョンアップを行いました。不具合の修正に加えて、いくつか新しい機能を追加することができました。今回のバージョンアップの目玉は、お手軽データベースの「SQLite」のサポートです。そこで今回は、SQLiteを使って郵便番号検索プログラムを作ってみようと思います。

今まで、なでしこでデータベースを使うとなると、Accessなど有料のデータベースや、フリーでもSQLiteと違って別途サーバプロセスの起動が必要なMySQLなど、本格的なデータベースが必要でした。しかし、SQLiteを使えば手軽にデータベースを利用できるのです。個人でちょっとしたデータベースを使いたい場合に最適なのです。今回のなでしこのバージョンアップで、SQLiteのエンジンを配布ファイルに梱包しましたので、なでしこをインストールすれば、何の設定も行うことなく、SQLiteを使うことができます。

※今回のプログラムを実行するには、なでしこのバージョン1.399以降が必要です。

なでしこでSQLiteを使う方法

SQLiteでは、1つのファイルが1つのデータベースになっています。データベースを利用するためには、ファイル名を指定するだけでOKです。そして、データベースを操作するには、SQLという問い合わせ言語を使います。

日本語でプログラミングができるというのがウリのなでしこですが、SQLに関しては日本語化されていません。将来的には、日本語でデータベースの操作が全てできるようにしたいと思っているのですが、現在は、英語でSQLを記述する必要があります。ですが、SQLの構文はほとんど定型化された決まりきったものです。パターンとして覚えてしまいましょう。

はじめに、なでしこでSQLiteを使う方法を解説します。原稿執筆の時点で利用できる命令は(主に)以下の3つです。

  • (データファイル)をSQLITE開く……データベースを開きます。
  • (ハンドル)のSQLITE閉……データベースを閉じます。
  • (ハンドル)で(SQL文)をSQLITE実行……SQL文を実行して結果を得ます。

簡単な使い方の例は以下の通りです。

「test.db」をSQLITE開いて、ハンドルに代入。#1
ハンドルで「SELECT * FROM test」をSQLITE実行して表示。#2
ハンドルのSQLITE閉じる。#3

1行目では、ファイル名を指定してデータベースを開きます。その時、データベースを操作するためのハンドルを返しますので、変数「ハンドル」に代入しておきます。

2行目では、先ほど得たハンドルを指定して、「SELECT * FROM test」というSQL文を実行します。そして、その結果を表示します。SQLの実行結果は、CSV形式で得られます。

3行目では、ハンドルを指定してデータベースを閉じます。

郵便番号データベースを作ってみる

せっかくデータベースが使えるのですから、何かしら役に立つものを作ってみたいものです。そこで、SQLiteの性能を調査する意味もこめて、郵便番号データベースを作ってみようと思います。

全国の郵便番号の一覧は、ゆうびんホームページから自由にダウンロードすることができます。サイトマップから、「郵便番号等のダウンロード」を探してクリックすると、ダウンロードページに行き着くことができるでしょう。各都道府県別のデータに分けてのダウンロードもできますが、ここは、全国一括(ken_all.lzh)を選んでダウンロードします。

全国の郵便番号データ「KEN_ALL.CSV」は、解凍するとなんと11.4MBにもなります。これは普通のCSVファイルなので、Excelなどの表計算ソフトでも開くことができるのですが、サイズが大きいので、開くのにものすごく時間がかかることと思います。

テーブルの作成

先ほど、SQLiteは1ファイル1データベースと説明しましたが、このデータベースの中には、複数の異なる形式のデータを保存することができます。例えば、郵便番号の一覧データのほかに、電話番号の一覧データなども、同じ1つのデータベースに保存することができます。

郵便番号や電話番号データのような、列(カラム)と行(レコード)を持つ2次元の表をテーブルといいます。つまり、1つのデータベースの中に、複数のテーブルを保存することができるといえます。

なでしこの「SQLITE開く」命令でデータファイルを指定して開くと、自動的にデータベースが作成されるのですが、中身は空っぽです。まずは、データベースの中に、テーブルを定義する(作成する)必要があります。

テーブルを作成するには、SQLの「CREATE TABLE」を使います。これは以下の書式で使います。

 CREATE TABLE テーブル名 ( カラム名,カラム名... )

郵便番号の一覧「KEN_ALL.CSV」にはいろいろな情報が記載されていますが、とりあえず今回は、郵便番号と住所が分かれば良いので、「郵便番号(code)、都道府県名(ken)、市区名(shi)、町名(cho)」の4列(カラム)を作ることにします。この場合、テーブルの作成は以下のように書きます。

 CREATE TABLE zip (code, ken, shi, cho)

そして、以下が、データベースとテーブルを作成するなでしこのプログラムです。

SQL文は「CREATE TABLE zip (code, ken, shi, cho)」
「郵便番号.db」をSQLITE開いてハンドルに代入。
ハンドルでSQL文をSQLITE実行。
ハンドルのSQLITE閉じる。
終わり。

プログラムを実行すると、「郵便番号.db」という名前のデータベースファイルが作成されます。

データの挿入

さて、次に先ほどダウンロードした「KEN_ALL.CSV」の郵便番号データをデータベースに挿入してみたいと思います。データを挿入するSQLは「INSERT」です。これは以下の書式で使います。

 INSERT INTO テーブル名 (カラム名,カラム名...) VALUES (データ,データ...)

例えば、テーブルzipのカラムcodeに0640941というデータを挿入したい場合は、以下のように書きます。

 INSERT INTO zip (code) VALUES ("0640941")

このとき、カラムとデータを複数指定することもできます。複数指定するときは、カラム名の順番とそれに対応するデータの順番を以下のように対応させて書きます。

 INSERT INTO zip (code, ken, shi) VALUES ("4418085","愛知県","豊橋市")

なでしこで郵便番号のデータ挿入を行うプログラムは以下のようになりました。

「郵便番号.db」をSQLITE開いてハンドルに代入。#1
「KEN_ALL.CSV」を毎行読む。#2
それを反復 #3
  「"」を「」に置換。#4
  「,」で区切る。#5
  郵便番号はそれ[2] #6
  県はそれ[6] #7
  市はそれ[7] #8
  町はそれ[8] #9
  SQL文は「INSERT INTO zip (code, ken, shi, cho) VALUES ("{郵便番号}","{県}","{市}","{町}")」#10
  ハンドルでSQL文をSQLITE実行。#11
  母艦は県&市 #12
ハンドルのSQLITE閉じる。#13
終わり。#14

Excelで全国の郵便番号「KEN_ALL.CSV」を開くと非常に時間がかかるように、なでしこでも、CSVファイルの内容を一気で読み込むと時間がかかってしまいます。そこで、「毎行読む」命令(#2行目)を使って、ファイルの内容を1行ずつ読むようにして、1行ずつデータベースに挿入していきます。

上のプログラムの動きを箇条書きにすると以下のようになります。

  1. データベースを開く(#1)
  2. 全国郵便番号ファイルを1行ずつ読むように指定(#2)
  3. 以下4から6を一行ずつ実行する(#3)
  4.   取り出した1行の不要な記号を取り去りカンマで区切る(#4-5)
  5.   必要なカラムを得てSQL文を作る(#6-10)
  6.   SQL文を実行する(#11)。経過を表示する(#12)
  7. データベースを閉じてプログラムを終了する(#13-14)

さて、プログラムを実行すると、ザーッとデータが登録されていくのですが、私のPCでは登録が終わるまでに5分以上の時間がかかりました。これでは余りにも遅すぎます。しょうがないのでしょうか?

SQLiteでは、何も指定せずに挿入すると、挿入のたびに毎回データの整合性をチェックします。これには時間がかかってしまいます。そこで、挿入の開始と最後を明示することで、データの挿入を高速にすることができます。

以下が、挿入処理を高速にしたプログラムです。(*1)と(*2)の行を追加しただけですが、今度は1分もかからずデータを登録することができました。

「郵便番号.db」をSQLITE開いてハンドルに代入。
ハンドルで「begin」をSQLITE実行。#------------------(*1)
「KEN_ALL.CSV」を毎行読む
それを反復
  「"」を「」に置換。
  「,」で区切る
  郵便番号はそれ[2]
  県はそれ[6]
  市はそれ[7]
  町はそれ[8]
  SQL文は「INSERT INTO zip (code, ken, shi, cho) VALUES ("{郵便番号}","{県}","{市}","{町}")」
  ハンドルでSQL文をSQLITE実行。
  もし、回数%100=0ならば、母艦は県&市&町
ハンドルで「end」をSQLITE実行。#----------------(*2)
ハンドルのSQLITE閉じる。
終わり。

データベースを検索する

さて、これでデータベースが完成しましたので、今度は、データを検索してみたいと思います。データを検索するには、SQLの「SELECT」文を使います。SELECT文の簡単な使い方は以下の書式の通りです。

  SELECT * FROM テーブル名 WHERE 条件

例えば、郵便番号の「0600035」を調べたい場合は以下のSQL文を書きます。

  SELECT * FROM zip WHERE code = "0600035"

では、実際になでしこを使って調べてみましょう。

SQL文は「SELECT * FROM zip WHERE code = "0600035"」 #1
「郵便番号.db」をSQLITE開いてハンドルに代入。#2
ハンドルでSQL文をSQLITE実行して表示。#3
ハンドルのSQLITE閉じる。#4

1行目にSQL文を書き、2行目でデータベースを開き、3行目でSQL文を実行し結果を画面に表示します。4行目はデータベースを閉じています。

郵便番号を検索したところ

参考までに、検索にどれだけの時間がかかるのか、いくつか調べて測ってみました。すると、平均して240ミリ秒かかりました(CPU 1.40GHz、RAMが512MBの環境です)。全国の郵便番号12万件以上からのデータ検索ですから、十分な結果ではないでしょうか。

10行プログラム~郵便番号から住所の検索

なでしこの「SQLITE実行」命令の戻り値は、CSV形式で返されます。そこで、グリッド部品を使うことでグリッド内にデータを表示することができます。

今回の10行プログラムとして、郵便番号を入力すると、住所を表示するという検索プログラムを作ってみます。

「郵便番号を入力」と表示。#1
番号エディタとはエディタ。#2
表示用とはグリッド。#3
検索ボタンとはボタン。その位置は番号エディタの右側。#4
そのクリックした時は #5
  「郵便番号.db」をSQLITE開いてハンドルに代入。#6
  SQL文は「SELECT * FROM zip WHERE code = "{番号エディタ}"」#7
  ハンドルでSQL文をSQLITE実行。#8
  表示用のアイテムはそれ。#9
  ハンドルのSQLITE閉じる。#10

郵便番号を入力して検索ボタンを押すとグリッドに結果を表示します

プログラムの1~4行目ではエディタやボタン、グリッドを配置しています。5行目以降ではボタンをクリックした時の動作を定義します。6~10行目ではSQL文を作って、SQLiteのデータベースを検索し、結果をグリッドに設定します。

応用~住所から郵便番号

ところで、郵便番号が分かっていて住所を引くということは、あまりないかもしれません。普通は住所から郵便番号を検索するというのが一般的だと思います。そこで、上のプログラムを改良して、住所から郵便番号を調べるプログラムを作ってみました。

「都道府県は?」と表示。#1
県エディタとはエディタ。#2
「市区は?」と表示。#3
市エディタとはエディタ。#4
検索ボタンとはボタン。#5
そのクリックした時は #6
  「郵便番号.db」をSQLITE開いてハンドルに代入。#7
  SQL文は「SELECT * FROM zip WHERE ken = "{県エディタ}" and shi = "{市エディタ}"」#8
  ハンドルでSQL文をSQLITE実行。#9
  表示用のアイテムはそれ。#10
  ハンドルのSQLITE閉じる。#11
表示用とはグリッド。#12

都道府県と市区を入力して検索することができます

プログラムを実行し、都道府県と、市区を入力すると、その地域の郵便番号の一覧をグリッドに表示します。

まとめ

以上、簡単ではありますが、なでしこでのSQLiteの使い方を書いてみました。データベースの問い合わせ言語であるSQLに詳しい人なら、手軽に利用することができると思います。ただ、SQL文に初めて触れる人は、まず、SQL文から覚えないといけないので、ちょっと大変かもしれません。

しかし、ここで見てきたように、全国の郵便番号一覧のような大量のデータの中から任意のデータを取り出したい場合などは、SQLiteは高速に動作しますので、大変重宝すると思います。ぜひ、なでしこから使ってみてください。

48/90

インデックス

連載目次
第90回 10行プログラミング最終回~10行プログラミングを振り返って
第89回 フォルダ一括圧縮
第88回 テキストエディタに貼り付けたExcelの表を見やすく整形する
第87回 一発加算電卓
第86回 ブログにHTMLソースを貼れるよう変換する
第85回 カード式メモ帳
第84回 時間電卓を作る
第83回 アスキーアート地図エディタ
第82回 縦書き風テキスト作成ツール
第81回 Excelシートの比較を行う
第80回 ノルマ計算機
第79回 高速バックアップ
第78回 単語帳に読み上げ機能をつける
第77回 ホームページのバックアップツール
第76回 英単語帳を作成する(その2)
第75回 英単語帳を作成する(その1)
第74回 メモリカード挿入でデータをコピーするツール
第73回 モールス信号ソフトの作成
第72回 フォトCDランチャーを作る
第71回 残暑見舞い作成ツールを作る
第70回 画面キャプチャプログラムを作る
第69回 様々なルールに対応するビンゴシートのプログラムを作る
第68回 ビンゴカードを作る
第67回 ビンゴマシンを作る
第66回 パスワードマネージャの作成
第65回 アンドゥ機能をつける
第64回 テキストの比較ツール~FCフロントエンド
第63回 システム情報クリップ
第62回 飲み会電卓を作る
第61回 バイナリエディタの作成
第60回 メールフッタ作成ツール(その2)
第59回 メールフッタ作成ツール(その1)
第58回 プロセス監視ツールを作成する
第57回 TODOメモを作る
第56回 携帯電話をテキストリーダーに使う(2)
第55回 携帯電話をテキストリーダーに使う(1)
第54回 「オープンソースカンファレンス2006 Tokyo/Spring」に参加しました
第53回 10行でチャットソフトを作る(2)
第52回 10行でチャットソフトを作る(1)
第51回 プレゼンタイマーを作る
第50回 カラーピッカーを作る
第49回 簡単な暗号作成プログラム
第48回 お手軽データベース「SQLite」を使う
第47回 CUI(コマンドライン)環境を拡張する
第46回 デスクトップ画像を記録するプログラム
第45回 市外局番で住所を調べる検索プログラム
第44回 コマンドライン型ランチャーを作る
第43回 文字数カウンタを作る
第42回 日本語でバッチ処理
第41回 イベントドリブンと電光掲示板
第40回 なでしこでWindowsの「送る」メニューを使いやすくする
第39回 同人音楽即売会M3に参加して思ったこと
第38回 画像のサムネイル作成
第37回 なでしことExcelで行事予定カレンダーを作ろう
第36回 なでしことExcelで月間カレンダーを作ろう!
第35回 なでしことExcelで年間カレンダーを作ろう
第34回 急な電話にも安心、意外と便利な「手書きメモ」
第33回 携帯電話のアドレスデータを取り出す
第32回 記念日カウンターを作る
第31回 顔文字入力支援ソフト
第30回 アスキーアートのためのエディタ
第29回 HTTPサーバーを作る
第28回 手紙のあいさつ文を作成するプログラム
第27回 ギターコードの構成音を表示
第26回 音楽の再生スピードを変える
第25回 ミニスライドショーを作る
第24回 撮影日で写真を振り分け
第23回 PSP用テキスト画像の作成
第22回 金種計算
第21回 パスワードを自動生成
第20回 携帯メールでWEBを更新する
第19回 時間の使い方を反省するプログラム
第18回 ウェブを画像でキャプチャ
第17回 ラーメンタイマーを作る
第16回 画像のトリミングツール
第15回 ごみの分別カレンダー
第14回 デスクトップ検索ツール
第13回 フォント見本帳
第12回 セピア調の画像ビューワー
第11回 健康チェック
第10回 10行で壁紙にらくがきする
第9回 WEBサイトの更新チェッカー
第8回 WEBページのリンク切れをチェック その2
第7回 WEBページのリンク切れをチェック
第6回 音楽ファイルの整理 - メディアタグから自動フォルダ分類
第5回 請求書の自動作成・印刷~Word連携技
第4回 重要データのバックアップ術
第3回 10行ニュースリーダーを作ろう!
第2回 なでしことは その2
第1回 なでしことは? その1

もっと見る

特別企画 PR

人気記事

一覧

イチオシ記事

新着記事

【レポート】「だが、それがいい! その箸がいい!」 - コトブキヤ秋葉原館にて『花の慶次』と侍箸のコラボイベント開催中
[15:00 5/28] ホビー
魔女と巫女が親友になる、内藤隆「黒髪巫女とマリアウィッチ」キャラット新連載
[14:36 5/28] ホビー
「ガンダム THE ORIGIN」で池田秀一「やっとシャアになれました」
[14:32 5/28] ホビー
[池田秀一]シャアに“戻り”安堵 「いい酒が飲めた」
[13:57 5/28] ホビー
[マンガ質問状]「おそ松さん」 トト子ちゃん主役の構想も
[13:30 5/28] ホビー

特別企画 PR