生 DBI ユーザーのための DBI Cookbook (1)

ちょっと前まで DBI で非同期アクセスなエントリが各所で上がっていましたが皆さん如何お過ごしでしょうか？

さてと、、、歴史的な経緯とか歴史的な経緯とかで生 DBI 相当を使ってる方もそれなりにいるでしょう。奥さん、大事な事なんで二度言いましたよ！

DBI のインターフェースってまぁそんな使いやすい物じゃないんですが、工夫次第で出来る事もあります。

ちなみにサンプルデータベースとして、MySQL Documentation - Example Databases の world データベースを使っています。

fetchall_arrayref でデータ整形

まず以下のように使ってみます。

#!/usr/bin/perl

use strict;
use warnings;

use Data::Dump qw(dump);
use DBI;
use Perl6::Say;

my $dbh = DBI->connect('dbi:mysql:db=world', 'root', '', +{ RaiseError => 1, AutoCommit => 0, });
my $sth = $dbh->prepare(q|SELECT CountryCode, COUNT(ID) AS CityCount FROM City GROUP BY CountryCode HAVING CityCount > ?|);
$sth->execute(200);
my $rs = $sth->fetchall_arrayref();

say dump($rs);

$sth->finish;
$dbh->disconnect;

この場合、結果は、

[
  ["BRA", 250],
  ["CHN", 363],
  ["IND", 341],
  ["JPN", 248],
  ["USA", 274],
]

になります。まぁこれは想像通り。

次に HashRef の ArrayRef として取得したい場合は fetchall_arrayref(+{}) とします。

my $rs = $sth->fetchall_arrayref(+{});

として、結果は、

[
  { CityCount => 250, CountryCode => "BRA" },
  { CityCount => 363, CountryCode => "CHN" },
  { CityCount => 341, CountryCode => "IND" },
  { CityCount => 248, CountryCode => "JPN" },
  { CityCount => 274, CountryCode => "USA" },
]

のように取得出来ます。この機能は Slice と言うみたいですね。ここで、CountryCode のみを取得したい場合、

my $rs = $sth->fetchall_arrayref(+{ CountryCode => 1 });

として、

[
  { CountryCode => "BRA" },
  { CountryCode => "CHN" },
  { CountryCode => "IND" },
  { CountryCode => "JPN" },
  { CountryCode => "USA" },
]

と取得出来ます。Slice を配列リファレンスとして定義も出来て、取得したいカラムのインデックスを列挙すれば、その値だけを取得出来ます。

my $rs = $sth->fetchall_arrayref([0]);

のように使います。

ちなみに取得している各レコードのカラム一覧は、

$sth->{NAME};

で配列リファレンスとして取得出来ます。即ち、fetchall_arrayref(+{}) のようなデータ構造は、冗長に書くと、

#!/usr/bin/perl

use strict;
use warnings;

use Data::Dump qw(dump);
use DBI;
use Perl6::Say;

my $dbh = DBI->connect('dbi:mysql:db=world', 'root', '', +{ RaiseError => 1, AutoCommit => 0, });
my $sth = $dbh->prepare(q|SELECT CountryCode, COUNT(ID) AS CityCount FROM City GROUP BY CountryCode HAVING CityCount > ?|);
$sth->execute(200);
my @fields = @{$sth->{NAME}};
my @rs = ();

while (my $row = $sth->fetchrow_arrayref) {
    my %record;
    @record{@fields} = @$row;
    push(@rs, \%record);
}

say dump(\@rs);

$sth->finish;
$dbh->disconnect;

みたいな感じで取得出来ると。

Slice の覚え方

fetchall_arrayref の場合、

• 何も指定しない場合は [] (ArrayRef) を指定したのと同じ
• ArrayRef を指定した場合は各 row を ArrayRef 形式で返す。各カラムのインデックスを指定するとそのインデックスのみをスライスして取得出来る
• HashRef を指定した場合は、各 row を HashRef 形式で返す。各カラムのキーを指定し、その値に 1 を入れておくと、そのキーに対応した値のみをスライスして取得出来る

fetchall_hashref, selectall_hashref でハッシュテーブルを作る

何気にこれは凄い便利。

#!/usr/bin/perl

use strict;
use warnings;

use Data::Dump qw(dump);
use DBI;
use Perl6::Say;

my $dbh = DBI->connect('dbi:mysql:db=world', 'root', '', +{ RaiseError => 1, AutoCommit => 0, });
my $rs = $dbh->selectall_arrayref(q|SELECT ID, Name, CountryCode, District, Population FROM City ORDER BY RAND() LIMIT ?|, +{ Slice => +{} }, 10);
my $cnt_tbl = $dbh->selectall_hashref(q|SELECT CountryCode, COUNT(ID) AS CityCount FROM City GROUP BY CountryCode|, 'CountryCode');

for my $row (@$rs) {
    $row->{CityCount} = $cnt_tbl->{$row->{CountryCode}}->{CityCount};
}

say dump($rs);

$dbh->disconnect;

この $cnt_tbl なんですけど、どういう形式かと言うと、

{
  ABW => { CityCount => 1, CountryCode => "ABW" },
  AFG => { CityCount => 4, CountryCode => "AFG" },
  AGO => { CityCount => 5, CountryCode => "AGO" },
  ### 以下略
}

みたいな形式。CountryCode をキーにして実際のレコードが入ってます。特定のキーで結合したい場合なんかに非常に便利。ちなみについつい prepare しちゃいがちですが、DBI::db の select 系メソッドで直接データ取得出来ます。

生 DBI で DBD::st オブジェクトを作る、つまり prepare するといちいち変数名割り当てたりするとうっとおしい場合がしばしばあるので、適宜使うのもありかなーみたいな。

まとめ

生だって工夫すれば色んな事が出来ます！