Perlでデータベース接続を行う際に使うモジュール、DBI.pm の覚え書きとサンプル。何回やっても忘れるので自分用メモ。Postgresとは書いているけど、MySQLなどでもだいたい同じなはず
ググる前にまず perldoc DBI するかCPANのDBI.pmのページを見て、公式ドキュメントを読むべき。（自戒を込めて）
#!/usr/bin/perl
use strict;
use DBI;
my $DB_NAME = "testdb";
my $DB_HOST = "localhost";
my $DB_USER = "ozuma";
my $DB_PASSWD = "7PX-uwVX";
my $dbh = DBI->connect("dbi:Pg:dbname=$DB_NAME;host=$DB_HOST", $DB_USER, $DB_PASSWD)
or die "$!\n Error: failed to connect to DB.\n";
my $sth = $dbh->prepare("SELECT userid,settime,status FROM tableA WHERE name = ? AND param = ?");
$sth->execute("Yasunaga", 201);
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($userid, $settime, $status) = @$ary_ref;
....
....
}
$dbh->disconnect;
#!/usr/bin/perl
use strict;
use DBI;
my $DB_NAME = "testdb";
my $DB_HOST = "localhost";
my $DB_USER = "ozuma";
my $DB_PASSWD = "7PX-uwVX";
my $dbh = DBI->connect("dbi:Pg:dbname=$DB_NAME;host=$DB_HOST", $DB_USER, $DB_PASSWD)
or die "$!\n Error: failed to connect to DB.\n";
my $sth = $dbh->prepare("INSERT INTO customer_tbl(userid, status) VALUES(?,?)");
my $retVal = $sth->execute("ozuma5119", 4);
$dbh->disconnect;
$dbh = DBI->connect($data_source, $username, $password, \%attr)
最初にconnectメソッドでデータベースハンドル($dbh)を取得する。$data_sourceは以下のように書く：
dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port
例1）$dbh = DBI->connect("dbi:Pg:dbname=testdb;host=localhost", "ozuma")
or die "$!\n Error: failed to connect DB.\n";
例2）$dbh = DBI->connect("dbi:Pg:dbname=testdb;host=localhost", "ozuma", "7PX-uwVX")
or die "$!\n Error: failed to connect DB.\n";
パスワードが空っぽの場合は例1のように引数を省けば良い。connectは負荷が高いので、一つのプログラムの最初にconnectして最後にdisconnectする、というのが定跡である（SELECTしようとするたびにconnect/disconnectなんてしないこと）。
\%attr は様々なattribute(属性)をハッシュリファレンスの形で渡すことで設定できるが、デフォルトのままならば省いて良い。具体的に属性として設定できる値一覧が整理されていなくて見にくいのだが、perldoc DBI の "ATTRIBUTES COMMON TO ALL HANDLES" と、"Database Handle Attributes" の2つが記述できる属性である。実際によく使われるのはAutoCommit, PrintError, RaiseErrorの3つくらいであろう。ドキュメントではAutoCommitとPrintErrorが両方ともデフォルトでONであることが明記されているが、これを信頼せずに明示的に指定すべきであるとも書かれている。
以下の例では、AutoCommitをゼロ(偽)に設定している。こうすると、トランザクションはもちろん自分で管理しないといけない（自分で明示的にCOMMIT/ROLLBACKする)。またPrintErrorも無効にし、エラーメッセージを抑制している。明示的にONを指定する時は、非ゼロ(1が普通)を設定する。
$dbh = DBI->connect($data_source, $username, $password,
{
AutoCommit => 0 # 自動コミットを無効
PrintError => 0, # エラーメッセージを抑止
}
);
DBIではfetchするために実に様々なメソッドがあるのだが、基本的にこのfetchrow_arrayrefだけ使っていれば良い。他のメソッドは忘れよう。
fetchrow_arrayrefを使った流れは、(1)SQL文でprepareして、(2)executeして(この際、必要ならばプレースホルダの変数をbindして)、(3)fetchrow_arrayrefで一行ずつ取り出す、という流れになる。
my $sth = $dbh->prepare("SELECT userid,settime,status FROM tableA WHERE name = ? AND param = ?");
$sth->execute("Yasunaga", 201);
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($userid, $settime, $status) = @$ary_ref;
....
....
}
最初に引数にSQL文を使ってprepareする。この際、変数がある場合はプレースホルダ "?" として入れておこう。
次に、executeメソッドで実際にSQL文を発行する。引数には、プレースホルダに入れるバインド値を順番に指定する。なお、プレースホルダが無いSQL文ならば、executeは引数無しで $sth->execute; と実行することになる。
最後に、fetchrow_arrayrefメソッドで1行ずつ取り出して処理する。配列のリファレンスの形でレコードの内容が返るので、"@"でデリファレンスしてやれば値が取り出せる。「1行ずつ取り出すなんて面倒くさい……」と、fetchall_arrayrefという全行を返してくれるメソッドを使いたくなるかもしれないが、巨大なテーブルを相手にした時に大変なことになるのでこれは使わないようにする。
先ほど、SELECT系は基本的にfetchrow_arrayrefを用いること、と書いた。しかし、主キーのuseridを指定して1行取り出すとか、"SELECT count(*) FROM tableA" など、「結果レコードが1行しかない」ということが明らかな場合は、このselectrow_arrayrefメソッドを使う方が良い。このメソッドはprepareとexecuteを自前でやってくれるため、自分でステートメントハンドルを作る必要が無く、SQL文だけを渡せばいいから楽チンだ。
selectrow_arrayrefメソッドは、バインド値の有無などで3種類の書き方がある。
$ary_ref = $dbh->selectrow_arrayref($statement); $ary_ref = $dbh->selectrow_arrayref($statement, \%attr); $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
\%attrの扱いにとまどう人が多いが、ここには特殊な値を渡すことで返される配列リファレンスの形式を指定できる。しかし、凝ったことをしてもややこしいだけなので、ここは空っぽのハッシュリファレンス {} を決め打ちで渡しておけば良い。つまり、プレースホルダが無いときは1番目、ある時は3番目の形式を用いる。
@bind_valuesには、バインド値を配列の形で渡す。$statementに登場するプレースホルダ "?" の順番に、値を配列にしてセットすれば良い。
引数が無いときの例：
my $ary_ref = $dbh->selectrow_arrayref("SELECT count(*) FROM txtinfo");
my $count = $ary_ref->[0];
....
引数があるときの例：
my $ary_ref = $dbh->selectrow_arrayref("SELECT id, username FROM txtinfo WHERE id=?, username=?", {}, (21, "yamada"));
my ($id, $username) = @$ary_ref;
....
富豪的パワフルなselectall_arrayref先生の登場。とりあえずこれだけあれば何でもできるが、SELECTした内容を無造作に全行メモリに展開するため、巨大テーブルを扱うと大変なことになる。そのため、基本的にこのメソッドは使わず、fetchrow_arrayrefを使うこと。引数の種類により以下の3パターンがある。
$ary_ref = $dbh->selectall_arrayref($statement); $ary_ref = $dbh->selectall_arrayref($statement, \%attr); $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
selectall_arrayrefメソッドは内部的には、"prepare","execute","fetchall_arrayref"メソッドをいっぺんにやってくれている。SQL文だけ与えれば良いので非常に楽チンだ。データベースのテーブル全体が「配列のリファレンスの配列のリファレンス」で返るので、1要素ずつ取り出してデリファレンスすればそのまま1レコードの配列が得られる。すなわち、foreachして->[$i]で値が取り出せる。
例）
こういうテーブルがある時……
userid | settime | status
----------+---------------------+--------
76306018 | 2006-07-12 03:37:58 | 0
51209602 | 2006-07-12 03:38:53 | 2
24495022 | 2006-07-12 03:41:41 | 0
43212393 | 2006-07-12 03:42:22 | 2
42478293 | 2006-07-12 03:43:43 | 0
$ary_ref = $dbh->selectall_arrayref("SELECT userid, settime, status FROM tableA");
foreach my $record (@$ary_ref) {
print $record->[0]; # userid
print $record->[1]; # settime
print $record->[2]; # status
print "\n";
}
\%attrというよく分からない値の使い方でとまどう人が多い。fetchall_arrayref メソッドで使われる $slice パラメタをselectall_arrayrefでも使用することができるのだ。この場合、\%attrに'Slice'もしくは'Columns'で指定する。具体的な使い方は以下のようになる。
# Slice={} とするとレコード部分はハッシュになって返る
$ary_ref = $dbh->selectall_arrayref("SELECT userid, settime, status FROM tableA", {Slice=>{}});
[
{
'settime' => '2006-07-12 03:43:43',
'status' => '0',
'userid' => '43212393'
},
....(略)....
]
# Columns=>[1,3] とすると、1カラムめと3カラムめだけ取得する
$ary_ref = $dbh->selectall_arrayref("SELECT userid, settime, status FROM tableA", {Columns=>[1,3]});
[
[
'43212393',
'3'
],
[
'30548923',
'1'
],
....(略)....
]
# 何も指定しない時は、空のハッシュを渡しておけばいい。デフォルトの「配列のリファレンスの配列のリファレンス」が返る。
$ary_ref = $dbh->selectall_arrayref("SELECT userid, settime, status FROM tableA", {});
ただ、Columns指定する例などは、特定カラムだけ欲しければ最初からそこだけSELECTすればいいのだから、わざわざ使う必要は無いだろう。私の場合、返り値の$ary_refの中身フォーマットがコロコロ変わるとむしろ混乱するのが良くないと考えており、この\%attrを指定することは無い。
さて、3つめの引数@bind_valuesは、バインド値を指定する。プレースホルダを利用する際は、ここに書けば良い。
# 例）IDの下1桁がゼロでステータスが2の人を取得
$ary_ref = $dbh->selectall_arrayref("SELECT userid, settime, status FROM tableA WHERE userid LIKE ? AND status = ?", {}, ('%0', 2);
このメソッドは、selectall_arrayrefと似ているが、各レコードをハッシュとしてテーブル全体をハッシュのハッシュとして返してくれる。とてもクセがあるし、selectall_arrayrefと同様にテーブルの行数が膨大だと大変なので、基本的には使わなくて良い。引数の種類により以下の3パターンがあるが、下2つはselectall_arrayrefと同様の動作なので説明を省略する。
$hash_ref = $dbh->selectall_hashref($statement, $key_field); $hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr); $hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr, @bind_values);
selectall_hashrefでは必ず$key_fieldを指定しなくてはならず、これが返されるハッシュのキーを指定することになる。$key_fieldは数値もしくは無名配列で指定する。以下に例を書こう。
例）
こういうテーブルがある時……
userid | settime | status
----------+---------------------+--------
76306018 | 2006-07-12 03:37:58 | 0
51209602 | 2006-07-12 03:38:53 | 2
24495022 | 2006-07-12 03:41:41 | 0
43212393 | 2006-07-12 03:42:22 | 2
42478293 | 2006-07-12 03:43:43 | 0
# 1番目のフィールド、すなわちここではuseridをキーに指定
$ary_ref = $dbh->selectall_hashref("SELECT userid,settime,status FROM tableA", 1);
# 取り出しの例
foreach my $id (keys %$ary_ref) {
print "$id,$ary_ref->{$id}{settime},$ary_ref->{$id}{status}\n";
}
# $ary_refの中身はこうなっている
{
'76306018' => {
'settime' => '2006-07-12 03:37:58',
'status' => '0',
'userid' => '76306018'
},
'51209602' => {
'settime' => '2006-07-12 03:38:53',
'status' => '2',
'userid' => '51209602'
},
....(略)....
}
# useridとstatusの組をキーに指定
$ary_ref = $dbh->selectall_hashref("SELECT userid,settime,status FROM tableA", ['userid','status']);
# 取り出しの例
foreach my $id (keys %$ref) {
foreach my $status (keys %{$ref->{$id}}) {
print "$id,$ref->{$id}{$status}{settime}}\n";
}
}
# $ary_refの中身はこうなっている
{
'76306018' => {
'0' => {
'settime' => '2006-07-12 03:37:58',
'status' => '0',
'userid' => '76306018'
}
},
'51209602' => {
'2' => {
'settime' => '2006-07-12 03:38:53',
'status' => '2',
'userid' => '51209602'
}
},
....(略)....
}
指定した$key_fieldに重複がある場合は、Perlのハッシュの性質上、後からの物で上書きされてしまう。そのためハッシュのキーとしてはテーブルのPRIMARY KEYを指定するか、もしくはその重複を理解した上でキー指定しないといけない。複数キーでuniqueにできる時は、2例目のように複数カラムを$key_fieldにして取り出せば良い。
更新系の場合、(1)prepareして、(2)executeする、というのが基本手順である。prepareするとステートメントハンドル($sth)が、executeすると更新した行数が返る。
同じようなSQLを複数回実行する場合は、変数を入れるところにプレースホルダ("?"記号で記述している、後からバインド値を入れる箇所)を利用して最初の1回だけprepareして、その後に好きな回数だけexecuteすれば良い。こうした方が速度的にも優れているし、シングルクオートなどの記号の余計なエスケープも考える必要が無いので、SQLインジェクションの心配もなくなる。
# 基本的なprepareとexecuteの使い方
my $sth = $dbh->prepare("INSERT INTO customer(userid,status) VALUES(?,?)");
my $retVal = $sth->execute("AAA", 4);
# ファイルから読み込んで順にINSERTする例。この場合、ファイルにユーザIDとして
# [hoge'aaf] とか [ars"#$%&'")]
# などの記号類が入っていても正しく動作する。
my $sth = $dbh->prepare("INSERT INTO customer(userid,status) VALUES(?,?)");
open(FP,"<user.txt");
while(<FP>) {
chomp;
my ($foo,$bar) = split /,/;
$sth->execute($foo, $bar);
}
close(FP);
prepareとexecuteで2行書くのが面倒くさい人向けに(?)、単にSQL文を渡せばそのまま実行してくれるdoという関数もある。do関数はselectall_arrayref等と同様、引数によって以下の3パターンがある。
$rows = $dbh->do($statement); $rows = $dbh->do($statement, \%attr); $rows = $dbh->do($statement, \%attr, @bind_values);
do関数でプレースホルダにバインド値を渡す場合は、返却される値の形式を指定するための\%attrという属性値も渡さなければならない(\%attrについては、上のselectall_arrayrefの記述を参照)。だがSELECT系と違って更新系は大したものが返るわけではないので、ここは決めうちで空っぽのハッシュリファレンスを渡しておけば十分である。
my $sth = $dbh->do("INSERT INTO customer(userid,status) VALUES(?,?)", {}, ('ozuma5119', 4));
なおdo関数はシンプルに書けて便利だが、内部的にはprepareとexecuteをセットにして実行している。そのため同一SQLを複数回実行する時は、速度的にも負荷的にも自前でprepareとexecuteした方が良い(もっと言えば、AutoCommitをオフにして自前でコミットした方が良い。次項のトランザクション制御を参照)。
DBIではデフォルトでAutoCommitが有効になっているため、トランザクションの制御をする場合にはAutoCommit属性を無効にするか、begin_workメソッドを利用する必要がある。トランザクション制御をする理由は大抵の場合2つあって、(1)一連の処理をatomicな物にしたい、(2)大量のUPDATEをかけるため速度を出したい、のどちらかだろう。
(1)についてはDBIとか以前にデータベースという物の性質なので詳しくは述べないけど、例えばAさんからBさんへの銀行振込の処理を作るときに、Aさんの預金額を減らしてからBさんの預金額を増やす。このBさんの預金額を増やすところでエラーが起きた場合、Bさんの預金は増えていないのにAさんの預金額が減ったままでは困るわけだ。こういう場合は、エラー処理の後にAさんの預金額を減らす処理を取り消すロールバック(rollback)が必要になる。このように、一連の処理を一括したまとまりにしたい場合にトランザクションを用いる。
(2)については、たとえば10000件のUPDATEを行う場合、デフォルトでは10000回のコミットが走ってしまい非常に遅い。これを、AutoCommitをオフにして10000回アップデートして、最後に明示的にコミットするようにすれば劇的に速くなるわけだ。
DBIでAutoCommitをオフにするには、connect時にAutoCommit属性にゼロを指定してやれば良い。
my $dbh = DBI->connect("dbi:Pg:dbname=$DB_NAME;host=$DB_HOST", "$DB_USER", $DB_PASSWD", {AutoCommit => 0});
この状態では、$dbh->do()などでUPDATE/INSERTを行っても、そのまま$dbh->disconnectしたりプログラムが終了したりすると元の値に戻ってしまう。そのため、一連の処理終了後に最後にCOMMITしてやり、エラーが起きた場合はそれを拾ってROLLBACKしてやるようにする。
なお、「disconnectしたら元の値に戻る(rollbackされる)なら、コミットするコードだけ書いておいて、エラー処理ではわざわざロールバックせずにそのままdisconnectすればいいじゃん」と思うかもしれないが、これは誤りである。perldoc DBIしてdisconnectメソッドの項目を読むと、"The transaction behaviour of the disconnect method is, sadly, undefined. "と書いてある。つまりPerlのDBI.pmにおいて、AutoCommitがオフの時にdisconnectした時のトランザクションの振る舞いは未定義である。よって今DBから切断時にrollbackされたのは、たまたま私が使っていたDBIのバージョンとPostgreSQLがそうだっただけで、今後DBIをバージョンアップしたり、利用するデータベースをMySQLやOracleに変えた際に同じ動きをする保証は無い。そのため、自動コミットをオフにする場合は、必ずdisconnectする前に自分でcommitもしくはrollbackのどちらかをしないといけない。
以下の例では、user.txtから値を読み込んで該当IDのステータスを更新する。入力ファイルにゴミがあるなどして1行でもエラーが出た場合はトランザクション全体がロールバックされるため、例えばuser.txtの100行目でエラーになっても、1-99行目は元の値に戻る(1-99行目だけが更新されている、など中途半端なことは起こらない)。また速度的にも有利で、このuser.txtが何万行もあれば、AutoCommitよりこちらの方がずっと速く処理が終わる。
my $dbh = DBI->connect("dbi:Pg:dbname=$DB_NAME;host=$DB_HOST", "$DB_USER", $DB_PASSWD", {AutoCommit => 0});
my $sth = $dbh->prepare("INSERT INTO customer(userid,status) VALUES(?,?)");
eval {
open(FP,"<user.txt");
while(<FP>) {
chomp;
my ($foo,$bar) = split /,/;
$sth->execute($foo, $bar);
}
close(FP);
};
if ($@) {
$dbh->rollback;
} else {
$dbh->commit;
}
$dbh->disconnect;
connect時はAutoCommitを有効にしていたけど、特定の部分だけトランザクション制御したい場合がある。こういう時は、begin_workメソッドを利用して明示的にトランザクションを指定してやれば良い。begin_workからcommit/rollbackで囲まれた部分が、AutoCommitされなくなる。なお、begin_work部分を抜けると元通りにAutoCommitされるようになる。
my $dbh = DBI->connect("dbi:Pg:dbname=$DB_NAME;host=$DB_HOST", "$DB_USER", $DB_PASSWD", {AutoCommit => 1});
my $sth = $dbh->prepare("INSERT INTO customer(userid,status) VALUES(?,?)");
# トランザクションの明示的開始
$dbh->begin_work;
$sth->execute('ozuma5119', 4);
$dbh->rollback;
# ここは既にbegin_workから抜けているので、AutoCommitされる
$sth->execute('ozuma5119', 2);
$dbh->disconnect;
例えば上記のサンプルコードでは、(ozuma5119, 4)はロールバックされてINSERTされず、後の(ozuma5119, 2)はAutoCommitされてINSERTされる。
$dbh->disconnect;
接続したデータベースからは、必ず切断する。切断時のトランザクションの動作は残念ながら未定義なので、AutoCommitをオフにしている時には、disconnectする前にROLLBACKもしくはCOMMITしてやらねばならない。
コーディングテクとしては、connectメソッドを書いた時点ですぐにdisconnectを書いてしまい、その真ん中にコードを書いていけばDB切断を忘れない、というのがある。もっと極まった人になると、connectしてもいないのにまずdisconnectを書き始める。まぁこのくらいの用意周到さが欲しいものである。