Perl から DBI で SQLite にアクセスする。

SETUP

Perl + DBI

Perl では DBI というデータベースへのアクセス機能を提供するインターフェイスを利用できます。

DBI のインストール方法については CentOS で Perl から SQLite を使えるようにするに記してあるので、参考にしてみてください。

今回は、DBI を用いて SQLite を使用する方法について記します。

DBI で SQLite に接続する

まずは DBI を利用できるように、Perl スクリプトの冒頭あたりで次の命令を実行します。

use DBI;

そして、たとえば SQLite データベースファイルへのパスを $database に設定した場合、次のようにします。

$dbi = DBI->connect("dbi:SQLite:dbname=$database");

これで、$database で指定した SQLite データベースファイルに接続できました。

これ以降は、上記では $dbi 変数に格納した DBI を使って、データベースの操作を行います。

データベースの使用が終わったら、次のようにしてデータベースから切断します。

$dbi->disconnect;

DBI を使った SQLite への接続と切断は、このような感じになります。

SQL 文を実行する

DBI で SQLite に接続すると、接続時に取得した DBI インスタンスを使って SQL 文を実行できるようになります。

結果を返さない SQL の実行

たとえば、結果を返さない SQL 文の実行は、次のように "do" メソッドを使って実行します。

$dbi->do('BEGIN TRANSACTION name1');

これで、引数に指定した SQL 文が DBI によって実行されます。

結果を返す SQL の実行

SELECT 文を実行したい場合は "prepare" メソッドを使用します。

$statement = $dbi->prepare('SELECT * FROM table1 ORDER BY score DESC');

$statement->execute();

"prepare" メソッドを実行したときに得られるインスタンスの "execute" を実行することで、SQL 文が実行されます。

得られた結果は "fetch" メソッドを使えば 1 行ずつ取得できます。

while ($row = $statement->fetch())

{

# ここで 1 行ずつの処理を行います。

print ($row->[0]);

}

"fetch" メソッドを実行すると、行のデータが配列の参照で取得できます。

取得した行は、先頭から順に $row->[0], $row->[1], ... というように、0 から始まる通し番号でアクセスできます。

通し番号でのアクセスだと判りにくい場合には、"fetchrow_hashref" メソッドを使うと連想配列の参照として取得できるので、カラム名でアクセスすることができるようになります。

while ($row = $statement->fetchrow_hashref())

{

# ここで 1 行ずつの処理を行います。

print ($row->{column1});

}

上記の例では、取得した行の "column1" という名前の列の値を操作しています。

使い終わった $statement は "finish" メソッドで閉じます。

$statement->finish();

これをしないと DBI の "disconnect" 時に "DBI::db=HASH(0x1aa81a90)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at" というようなメッセージが表示される場合があります。

取得した行数を調べるには

SELECT 文で取得できた行の数を知りたい場合、DBI の場合はあまりスマートな方法はなさそうです。

実行する SELECT 文と同等のものを用意して、その行数を取得する SQL 関数 "COUNT" を使う必要があるようでした。

例えば、次のようにします。

$statement = $dbi->prepare('SELECT COUNT(*) FROM table1');

$statement->execute();

$row = $statement->fetch();

$count = $row->[0];

このように、行数を調べる SQL 文を実行して、その結果を fetch メソッドで取得するという流れになります。

SELECT の SQL 文が $query に入っていて、行数を得るための SQL 文を生成するのが面倒な場合は、次のようにサブクエリーを使う手もあるかもしれません。

$statement = $dbi->prepare("SELECT COUNT(*) FROM ($query)");

$statement->execute();

$row = $statement->fetch();

$count = $row->[0];

このようにすれば、同等の SQL 文を作成する際に抽出条件などを間違えることがないので、矛盾を避けるには良さそうです。

1 行だけの結果を返す SQL 文

1 行だけの結果を返す SQL 文の場合には、もう少し簡単に記述することができます。

たとえば先ほどの行数を取得する SQL 文の実行は、"selectrow_arrayref" メソッドを使って、次のように書き換えられます。

$row = $dbi->selectrow_arrayref('SELECT COUNT(*) FROM table1');

$count = $row->[0];

このように "selectrow_arrayref" メソッドは、"prepare" → "execute" → "fetch" の流れを 1 度に実行するメソッドと言えます。

配列の参照ではなくハッシュの参照で取得したい場合には、"selectrow_arrayref" メソッドの代わりに "selectrow_hashref" メソッドを使用します。

SQL 文に値を設定する場合

SQL 文で使用する文字列をエスケープする

SQL 文では、文字列は基本的にシングルクォーテーションで括る必要があります。

文字列の中にシングルクォーテーションが含まれる場合などの配慮が面倒ですが、DBI にはこれを自動で行ってくれる機能が用意されています。

$value = $dbi->quote($string);

このようにすることで、変数 $string に格納されている文字列を、SQL 文で使用可能な文字列に変換してくれます。

変換された文字列は、両側がシングルクォーテーションで括られています。

プレースホルダーを使用する

WHERE 条件が複雑になると、SQL 文を文字列で生成するのも大変です。

そのようなときには、値を後から設定できるプレースホルダーという機能が便利です。

$statement = $dbi->prepare('SELECT * FROM table WHERE Title = ? AND Year = ? AND Month = ?');

@params = ( 'TITLE', 2012, 11 );

$statement->execute(@params);

このように、"prepare" の段階では値に該当する部分を "？" で指定しておいて、実際に SQL 文を処理する "execute" のときに "？" で指定した部分に対応する値を配列を使って指定します。

文字列として使用する値は、自動的に両側をシングルクォーテーションで括ってくれるのも便利です。

上記の例は SELECT 文だけでなく INSERT 文などでも使用できます。

INSERT 文や REPLACE 文などの実行の場合は DBI の do メソッドを使って実行することができますが、その時にもプレースホルダーを使用できます。

$query = 'REPLACE table (Title, Year, Month) VALUES (?, ?, ?)';

@params = ( 'TITLE', 2012, 11 );

$dbi->do($query, {}, @params);

このように、第三引数としてプレースホルダーに適用する値の配列を指定します。

"selectrow_arrayref" メソッドのような SELECT 系メソッドでも同様です。

$query = 'SELECT COUNT(*) FROM table WHERE Title = ? AND Year = ? AND Month = ?';

@params = ( 'TITLE', 2012, 11 );

$row = $dbi->selectrow_arrayref($query, {}, @params);

このようにプレースホルダーを使うことで、SQL 文とデータとを扱えるようになるので、コードの見通しもよくなります。