読者です 読者をやめる 読者になる 読者になる

Windows上でRubyを使ってMySQLにアクセスしてみよう

Ruby

現在Windows Server上でIIS+.Net(VisualBasic)で作ったWebサイト上での住所検索機能をFreeBSD(またはLinux)+Apache+MySQL+Rubyで実現するためのテスト。なぜならVisualBasicはともかくWindows Server 2003 R2 Standardは開発ライセンスのものなので実運用にはできないから。会社でWindows Server買ってくれればいいんだけど以下略。
ちなみに、住所を入力するとその住所の推定緯度経度を返してくる検索機能です。住所と緯度経度の相関関係は国土交通省国土計画局参事官室から位置参照情報ダウンロードサービス→以下略とたどれば取得できます。都市部は結構データがそろっているようです。

そして今回の内容はかなり長いです。自分用メモですね。

UNIX系OSで動かすつもりだけど、とりあえずよく使うのはWindowsなのでWindows上でMySQL+Rubyの検索機能部分を作ってみる前段階でMySQLをインストールしてみた。
先日来Rubyは使っているのでRuby自体は問題ないけどMySQLを扱うための拡張ライブラリをインストールする。

# gem install mysql

次にWindows用のMySQLMySQLのサイトにあるダウンロードのCurrent Release (Recommended)にあったMySQL 5.1を選択。WindowsWindows Essentials (x86)をダウンロードする。
現在のデータはAccessデータベースに登録されているのでエクスポートをするためにMySQL Connector/ODBC 5.1もダウンロードしました。
MySQLMySQL Connecterをインストールして管理ツールにあるODBCの設定。システムDSNにMySQLを登録。「Test」ボタンをクリックしてテスト接続もOK。後はAccessのファイルを開いてテーブルをエクスポートしてやる。
次にRubyスクリプトを書いてエクスポートしたデータの確認。の前にMySQLのインストールフォルダ内のbinフォルダにあるlibmySQL.dllをRubyのbinフォルダにコピーしてやらないとだめらしい。とりあえずこんな感じのスクリプトを書いてテスト。

#!/usr/local/bin/ruby -Ku
$KCODE="UTF8"
require 'rubygems'
require 'mysql'

my = Mysql::new("localhost", "%user%", "%password%","%database%")
test = my.query("select * from %table%")
test.each {|fields|
  puts "#{fields[0]}#{fields[1]}#{fields[2]}"
}
my.close

データは取り出せるのですが、文字が化けてました。
Accessで使ってる文字コードMySQLで指定したUTF-8と違ってるのだろうか、とりあえず一回CSVにエクスポートしてからCSVMySQLにインポートすることに。

何も考えずAccessのテーブルをCSVにエクスポートしたがテーブル内で小数第6位まである数値が小数第2位までしかエクスポートされません。ググるWindowsの設定が影響しているらしくコントロールパネルにある「地域と言語のオプション」から「地域オプション」タブの「地域のオプションのカスタマイズ」にある「小数点以下の桁数」を変更すると変更した範囲でエクスポートされるらしい。ついでにエクスポートする際の文字コードUTF-8にしておきました。

CSVから取り込む時はまずテーブルを作成しておかないといけないらしいのでまずはテーブルを作成。

# mysql -u %user% -p
create database %database%;
use %database%;

続いてテーブルを作成。必要なのは次のフィールド

ID 整数
都道府県 テキスト
市区町村 テキスト
大字・町丁目 テキスト
街区符号・地番 テキスト
緯度 整数+小数値
経度 整数+小数値

これに従ってテーブルを作成する

create table %table%(
  ID int not null,
  pref text,
  city text,
  section text,
  num text,
  breite float,
  lange float
  );
show fields from %table%;

で確認すると

+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| ID      | int(11) | NO   |     | NULL    |       |
| pref    | text    | YES  |     | NULL    |       |
| city    | text    | YES  |     | NULL    |       |
| section | text    | YES  |     | NULL    |       |
| num     | text    | YES  |     | NULL    |       |
| breite  | float   | YES  |     | NULL    |       |
| lange   | float   | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

無事にテーブルが作成されたらしい。
次にCSVファイルをインポートする。

load data local infile "%CSVFile%" into table %table% fields terminated by ',';

%CSVFile%はディレクトリの区切りを¥ではなく/で書かないと怒られる。試しにselect * from %table%でテーブルの中身を全部表示させてみた。
…。漢字が化けてる上に緯度が小数第4位、経度が小数第3位までしか表示されない。
テーブルのfloatの指定の時にfloat(%全体桁数%,%小数点以下の桁数%)として全体の桁数と小数点以下の桁数を指定すればいいらしい。
一度テーブルを破棄して再作成。

drop table %table%;
create table %table%(
  ID int not null,
  pref text,
  city text,
  section text,
  num text,
  breite float(10,6),
  lange float(10,6)
  );
show fields from %table%;

これで出てきたのが

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ID      | int(11)     | NO   |     | NULL    |       |
| pref    | text        | YES  |     | NULL    |       |
| city    | text        | YES  |     | NULL    |       |
| section | text        | YES  |     | NULL    |       |
| num     | text        | YES  |     | NULL    |       |
| breite  | float(10,6) | YES  |     | NULL    |       |
| lange   | float(10,6) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

で、もっかいCSVを取り込む。

load data local infile "%ファイル名%" into table %table% fields terminated by ',';

今度こそとテーブルを表示させるがやっぱり文字化けしてる。緯度と経度は問題なく表示された。
これはもしかしてMySQLAccess文字コードの違いのせいじゃなくてコマンドプロンプト文字コードがShift-JISだからなんじゃないなか…。

そんなわけでコマンドプロンプト文字コードを変更する。cmd.exeを起動してchcpを実行すると文字コードをみられる。

# chcp
現在のコード ページ: 932

やっぱりShift−JISのようで。
これをUTF-8に変更すればいいわけだ。がまずはコマンドプロンプトのプロパティで表示フォントをMSゴシックにしておいてからchcpを実行する。chcpではいくつか文字コードが指定できるらしい。今回見た中だと。

20932 EUC
65001 UTF-8
932 Shift−JIS

って感じ、他は滅多に使わないのであっても気にしない。とりあえずchcp 65001を実行してMySQLに入ってから再度チャレンジ。…もっと化けた。

実はMySQLUTF-8に指定されていないのではないかと思いProgram FilesにあるMySQLフォルダ内のmy.iniを編集してみた。[mysqld]セクションの下に

[client]
default-character-set = utf8
[mysqld]
default-character-set = utf8
[mysqldump]
default-character-set = utf8

を追記。MySQLサービスを再起動させてMySQL内から確認してみる。

show variables like "char%";
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| character_set_client     | utf8     |
| character_set_connection | utf8     |
| character_set_database   | utf8     |
| character_set_filesystem | binary   |
| character_set_results    | utf8     |
| character_set_server     | utf8     |
| character_set_system     | utf8     |
| character_sets_dir       | 略       |
+--------------------------+----------+

ちゃんとUTF-8が指定されているらしい。念のためと思ってRubyスクリプトから確認してみた。

#!/usr/bin/ruby -Ku
require 'rubygems'
require 'mysql'

my = Mysql.init()
my.real_connect("localhost", "%user%", "%password%", "%database%")
p my.character_set_name()

実行すると"latin1"が返ってくる。なんでやねん。

ダメもとでテスト用Rubyスクリプトに「set character set utf8」を追記。さらにコマンドプロンプトの漢字も信用ならないのでRubyスクリプトでデータを取得してファイルに出力するように変更。

#!/usr/local/bin/ruby -Ku
$KCODE="UTF8"
require 'rubygems'
require 'mysql'

my = Mysql::new("localhost", "%user%", "%%password","database")
my.query("set character set utf8")
links = my.query("select * from %table%")
my.close
foo = File.open("test.txt",'w')
links.each {|fields|
  data = "#{fields[1]},#{fields[2]},#{fields[3]}"
  foo.puts data
}
foo.close

出力されたtext.txtを表示してみるとちゃんと住所が表示されている。なんでやねん。いや、できてよかったんだけどさ。
Rubyから文字コードを問い合わせた時にUTF-8が返ってこない謎が残った。

…というか、最初にAccessからテーブルをエクスポートしたときにも問題なくエクスポートされていて、ただ単に表示がおかしかっただけなのでは。
試しに最初の手順でデータをエクスポートして最後に書いたRubyをエクスポートしたテーブルに合わせてファイルに書き込んだらちゃんと住所が漢字として返ってきた。ああ、CSVにしなくてよかったのか…。

そんなわけでWindowsコマンドプロンプト上でUTF-8の文字列を出力はしないで、一回テキストファイルに出力してやった方がいいようですね。今回試してるのはXPなのでVistaとかWindows 7だと事情が違うかもしれません。