Windows上でRubyを使ってMySQLにアクセスしてみよう
現在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用のMySQLをMySQLのサイトにあるダウンロードのCurrent Release (Recommended)にあったMySQL 5.1を選択。WindowsのWindows Essentials (x86)をダウンロードする。
現在のデータはAccessデータベースに登録されているのでエクスポートをするためにMySQL Connector/ODBC 5.1もダウンロードしました。
MySQLとMySQL 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にエクスポートしてからCSVをMySQLにインポートすることに。
何も考えず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 ',';
今度こそとテーブルを表示させるがやっぱり文字化けしてる。緯度と経度は問題なく表示された。
これはもしかしてMySQLとAccessの文字コードの違いのせいじゃなくてコマンドプロンプトの文字コードが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に入ってから再度チャレンジ。…もっと化けた。
実はMySQLがUTF-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だと事情が違うかもしれません。
