Perl入門 ≫ LESSON23 データベースアクセス
データベースアクセス
Perlでは、DBMライブラリを使ってシンプルで使いやすいデータベースが利用できます。
データベース（DB）ってそもそも何？
DBは、簡単に言えば情報の集まりです。検索とかデータの出し入れとかが簡単にできるように整理されたデータの集まりです。
外部に作成したデータベースにアクセスする方法と、Perlのプログラムで作成したデータベースファイルにアクセスする方法の２つを紹介します。
データベースにアクセスする
Perlプログラムから、MySQLとかPostgreSQLなどのデータベースにアクセスしてみます。
注）データベースはすでにインストールされていることを前提にしています。
アクセスにはDBIモジュールを使うと便利です。
MySQLへアクセスするには、次のようにします。
$dbh = DBI->connect("dbi:mysql:dbname=データベース名;host=ホスト名;port=ポート名",
ユーザ名,パスワード)
$dbh->disconnect;
PostgreSQLの場合は「mysql」を「Pg」にすればＯＫです。 connectで接続して、disconnectで接続を切るまで、そのDBに対して処理ができます。 データベースにアクセスして、テーブルに登録してあるデータを全部とって表示してみます。
use DBI;
my $DB_NAME = "dbname";
my $DB_USER = "username";
my $DB_PASS = "userpass";
my $DB_HOST = "localhost";
my $DB_PORT = "";
my $dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME;host=$DB_HOST;port=$DB_PORT",
"$DB_USER","$DB_PASS")
or die "DB access Error!\n";
my $sth = $dbh->prepare("SELECT * FROM adminuser;");
$sth->execute();
while(my $arr = $sth->fetchrow_arrayref) {
my ($user_id, $password) = @$arr;
print $user_id.':'.$password."\n";
}
$sth->finish;
$dbh->disconnect;
user1:pass1
user2:pass2
:
こんなカンジになります。
fetchrow_arrayrefはfetchと書いても同じです。
fetchrow_arrayref
fetch
どちらも結果を１行ずつ取り出します。
prepareでSQL文を発行します。SQLとは、DBの操作を行うための言語です。大抵どのDBでも同じように使えます。prepareで準備したSQLを実際に実行するのにexecuteを使います。
prepare(SQL文)
execute()
do
このprepare&executeを一緒にして行うのに、doを使います。
doは、影響のあった行の数をかえします。insertやdelete、update文で使います。
do(SQL文)
以降、connectからdisconnectの間のみ書きます。
my $count = $dbh->do("insert into table tablename values('user4', 'pass4')");
user4のデータを挿入します。成功すれば1が返ります。
fetchall_arrayref
prepare executeとfetchall_arrayrefを一緒にして行うのに、fetchall_arrayrefを使います。 検索結果が一度に入ってくるので、あんまり結果が多い場合は使わないようにしましょう。
fetchall_arrayref(SQL文)
my $arrall = $dbh->selectall_arrayref("SELECT * FROM adminuser;");
for my $arr (@$arrall) {
my ($user_id, $password) = @$arr;
print $user_id.':'.$password."\n";
}
プレースホルダとバインド
次のようにSQLを複数実行する場合を考えます。
SELECT * FROM adminuser where user_id = 1
SELECT * FROM adminuser where user_id = 2
user_idで指定する値だけ変更できればいいので、先にprepareで変更する値の部分を「?」としておいて、実行時にその値を入れんで実行する書き方があります。
SELECT * FROM adminuser where user_id = ?
こんな具合です。こうやって後から値を入れることを前提にした?の場所のことをプレースホルダ、そこにあとから値をいれることをバインドと呼びます。
@idlist = (1, 2);
my $sth = $dbh->prepare("SELECT * FROM adminuser where user_id= ? ");
for my $id (@idlist) {
$sth->execute($id);
my $arr = $sth->fetchrow_arrayref;
my ($user_id, $password) = @$arr;
print $user_id.':'.$password."\n";
}
$sth->finish;
もし「user_id > ? and user_id < ?」なんて置き換える部分が２つのときは「execute(1, 100)」のように配列にします。
prepare,executeを使わない場合の書き方はつぎのようになります。
fetchall_arrayref(SQL文, 配列リファレンスの形式, 引数の配列)
「配列リファレンスの形式」とは気にしないでいいです。ただの「{}」としておきましょう。バインド値の配列なので、値が１つのときでも($id)としましょう。複数のときは(100, $id)などとします。
@idlist = (1, 2);
for my $id (@idlist) {
my $arrall = $dbh->selectall_arrayref("SELECT * FROM adminuser where user_id= ? ", {} , ($id));
for my $arr (@$arrall) {
my ($user_id, $password) = @$arr;
print $user_id.':'.$password."\n";
}
}
SQLで文字列を扱うときはシングルクォートで囲います。「where name='hanako'」という具合です。プレースホルダを使うと、その値が数値であろうと文字列であろうと、「where name=?」だけで済むので楽です。
あと、ユーザが入力した値で検索を行うときなど、妙な値が入ってくることがあります。
例えば$nameに「hana'ko」という値が入力された場合、
where name='$name'
これは
where name='hana'ko'
こうなってしまいます。SQLとしておかしいよ！と言われてしまいます。こうならないように、普通はSQLに入れる値はSQLのエスケープ処理をします。
hana'ko → hana''ko
プレースホルダを使うと、勝手にエスケープ処理をしてくれます。
commit
データベースハンドルを作ったあとに
print $dbh->{AutoCommit};
とやってみましょう。
おそらく1と表示されるはずです。これは、データベースで何か処理をしたときに、その都度自動で処理を確定してくれるかどうかの値です。
1のときは自動でやってくれます。1ではないと、
commitという処理をしないと処理が確定されません。
commit
rollback
commitは処理の確定、
rollbackは処理を戻すことです。
例えばテーブル１とテーブル２の更新を行う場合、両方成功しないと意味がないデータでは、自動確定しないようにして、両方の処理が成功しない場合は、先に行っていた処理をなかったことにします。これがrollbackです。両方成功したらcommitして確定します。これではじめて、両方のテーブルが更新されたことになります。
まず、大抵はAutoCommit値は1になっているので、0にします。ここから先の処理は、commitするかrollbackすることになります。
$dbh->{AutoCommit} = 0;
テーブル１を更新する。
if テーブル２の更新に成功した場合、両方の更新を確定します。
$dbh->commit;
else テーブル２の更新に失敗した場合、テーブル１の更新をなかったことにします。
$dbh->rollback;
こんな具合です。
DBIが動かない場合DBIを使ったプログラムを実行したとき、
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: C:/Perl/site/lib C:/Perl/lib .) at...
Perhaps the DBD::mysql perl module hasn't been fully installed,
こんなのが出るかもしれません。
この場合DBIモジュールが使ってるDBD::mysqlがないよーって言われています。
モジュールがない場合は、自分でいれましょう！
入れ方は次の「モジュールの入れ方」を参考にしてください。
データベースファイル
外部のデータベースを使わずに、ハッシュをそのままちょっと特殊なファイルに格納して、
そのファイルにアクセスすることでデータベースにアクセスするように使えるようにしたものもあります。
扱うデータはあるKEYに対して１対のデータを持つような簡単なものに限ります。
データベースファイルの作成
dbmopen ハッシュ, DBファイル名, モード
:
dbmclose ハッシュ
または
tie ハッシュ, クラス名, DBファイル名
:
untie ハッシュ
tieとdbmopen、どちらでも同じようです。が、今はtieを使ったほうが良い･･･らしいです。
最初のdbmopen（またはtie）でデータベースをオープンし、dbmclose(またはuntie）でクローズします。
閉じるまでの間に、データの追加・更新・削除を行います。
データへの各処理は、ハッシュへの操作と同様です。
#ファイルオープン
dbmopen(%pass, "password", 0666) || die "DB file open error!";
#データ追加
$pass{'user1'} = 'pass1';
$pass{'user2'} = 'pass2';
#データ表示
foreach(keys %pass) {
print "$_ : $pass{$_}\n";
}
print "----------\n";
$pass{'user2'} = 'pass2_1';
$pass{'user3'} = 'pass3';
#データ削除
delete $pass{'user1'};
foreach(keys %pass) {
print "$_ : $pass{$_}\n";
}
#データベースクローズ
dbmclose %pass;
user3 : pass3
user1 : pass1
user2 : pass2
----------
user2 : pass2_1
user3 : pass3
最初にデータベースファイルをオープンした時点で、データベースファイルが作られます。
上の例で行くとファイル名は”password”。しかし･･･どこを探してもこの名前のファイル名はありません。
実は、指定したデータベースファイル名に拡張子”dir”と”pag”を付けた二つのファイルが作られます。
これがデータベースファイルの正体です。
この二つを物理的に削除すればデータベース自体も削除することになります。
tie関数を使った方ですが個人的にあまり好きではないので省略します。
と言うのも、perlを動かす環境によってDBの種類(DBM,SDBM, etc..)が違ってしまうので、
他のマシンで動かそうとしても動かない場合が多いからです。
（ちなみに、大体DOS-VマシンだとSDBMが使えるみたいです）