PerlでSQLiteを使う

目次

• 基本事項

• SELECT文の実行

• INSERT、UPDATE、DELETE文の実行

基本事項

PerlでSQLiteを使う方法を簡単にまとめておく。

まず、必要となるのは、DBIモジュールと、対象のデータベースへの接続である。

# DBIモジュールを使う。
use DBI;

# connectでSQLiteのデータベースファイルに接続する。
my $dbname = 'data.db';
my $db = DBI->connect("DBI:SQLite:dbname=$dbname", '', '', { AutoCommit => 0 });

  :

# 全ての操作が終わったらdisconnectで接続を終了する。
$db->disconnect;

以降、サンプルとして、「data.db」にはdataという名前の以下のようなテーブルが記録されているものとする。

SELECT文の実行

SQL文を実行するには、prepare()で文を用意し、execute()で値の挿入と実行を行なう形が基本となる。prepare()では、値を挿入する位置をプレースホルダ?としておき、execute()で?の位置に対応する値を引数に渡す。

実行結果を取得する方法はいくつかあるが、fetchrow_hashref()が使い勝手が良い。これは、呼び出すごとに結果を1行ずつ取得し、列名をキーにしたハッシュリファレンスを返す。

# SQL文を用意
my $s1 = $db->prepare('SELECT * FROM data WHERE value1 = ? AND value2 = ?;');

# 値を渡して実行
$s1->execute(111, 333);

# 結果を1行ずつ取得
while (my $row = $s1->fetchrow_hashref()) {
    my $id     = $row->{id}; # 列名がキーになっている。
    my $name   = $row->{name};
    my $value1 = $row->{value1};
    my $value2 = $row->{value2};
    print "$id: $name, $value1, $value2\n";
}

# 取得し終えたらfinishを呼ぶ
$s1->finish;

SQLで合計などを取得した場合は、列名が無いため、fetchrow_hashref()の代わりにfetchrow_arrayref()を使うとよい。こちらは、1行ずつを配列リファレンスにして返す。

# 列名の無いSELECT文を実行
my $s2 = $db->prepare('SELECT COUNT(*), SUM(value2) FROM data WHERE value1 = ?;');
$s2->execute(111);

# 結果を取得
my $row = $s2->fetchrow_arrayref();
my $count = $row->[0];  # COUNT(*)の値
my $sum   = $row->[1];  # SUM(value2)の値
print "count=$count, sum=$sum\n";  # count=2, sum=777

$s2->finish;

INSERT、UPDATE、DELETE文の実行

INSERT文やUPDATE文の場合もSQL文の実行方法は同じであるが、これらのSQL文の場合はexecute()が影響を受けたレコード数（失敗時はundef）を返す。ただし、影響を受けたのが0行の場合は、0E0という文字列が返る。この値はそのまま評価すると真となり、数値評価すると0となるため、以下のように「成功して1行以上」「成功したが0行」「失敗」の3つの場合を区別することができる。

なお、SELECT文の場合もexecute()が値を返す環境もあるらしいが、そちらは確実ではないので頼らない方がよいとのことである。

データベース接続時にAutoCommit => 0を指定していた場合は、以下のように1つ以上のSQL文を実行した後で、自分でトランザクションをcommitする必要がある。逆に途中で問題が生じた場合は、一連のトランザクションをrollbackすることもできる。

# UPDATE文を実行
my $s3 = $db->prepare('UPDATE data SET value2 = ? WHERE value1 = ?;');
my $result = $s3->execute(777, 111);

# 成功した場合
if ($result) {
    # 実行を確定する
    $db->commit;
    
    # 成功かつ1行以上が影響を受けた場合
    if ($result > 0) {
        print "$result records were updated.\n";
    # 成功したが影響を受けた行が無い場合
    } else {
        print "No records were updated.\n";
    }

# 失敗した場合
} else {
    # 実行を無かったことにする
    $db->rollback;
    
    print "Update statement failed.\n";
}

$s3->finish;