お気楽 SQLite 超入門

Perl DE SQLite

SQLite は Perl, Python, Ruby などのスクリプト言語からでも簡単に利用することができます。本稿では Perl から SQLite にアクセスする基本的な方法について簡単に説明します。

●インストール

Perl から SQLite にアクセスする場合、モジュール DBI (DataBase Interface) を利用するのが一番簡単です。DBI にはデータベースを操作するための共通なインターフェースが定義されていて、実際には各種の DBD (DataBase Driver) を呼び出すことになります。SQLite であれば、モジュール DBD::SQLite になります。

DBI や DBD::SQLite がインストールされていない場合、Ubuntu 系の OS では次のコマンドで簡単にインストールすることができます。

$ sudo apt-get install libdbd-sqlite3-perl

これで SQLite にアクセスするための必要なファイルがインストールされます。

●接続と切断

Perl で SQLite を使用する場合、最初に use DBI; でモジュール DBI をロードしてください。データベースの接続はメソッド connect を、切断は disconnect を使います。次の例を見てください。

リスト : データベースの接続と切断 (test01.pl)

use strict;
use warnings;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=sample_perl.sqlite");

# 切断
$dbh->disconnect;

connect の引数には dbi:SQLite:dbname=データベース名を指定します。データベース名と一致するファイルが見つからない場合、新しいファイルが生成されます。同名のファイルがある場合はそれをデータベースとして使用します。connect の返り値 (データベースハンドル) はデータベースの操作に必要なので、変数 $dbh に格納しておきます。切断はメソッド disconnect を呼び出すだけです。

●テーブルの作成

DBI で SQL 文を実行するには 2 つの方法があります。一つはメソッド do を使う方法です。do は引数に渡された文字列を SQL 文として実行します。ただし、同じようなクエリ (問い合わせ) を何度も繰り返すと、RDBMS では同じような解析処理を繰り返すというオーバーヘッドが発生します。メソッド prepare を使うと、このようなオーバーヘッドを避けることができます。

prepare はパラメータ情報を含んだ SQL 文をプリコンパイルしておきます。prepare の返り値をステートメントハンドルと呼びます。パラメータは ? で表します。これを「プレースホルダー」といいます。SQL 文の実行はメソッド execute で行います。このとき、パラメータに対応する値を引数として渡します。このような値を「バインド値」と呼びます。

それでは簡単な例題として、次に示すテーブルを作成してみましょう。

テーブル名 : person
id	name	age	sex	email
1	Foo	50	male	foo@yahoo.co.jp
2	Bar	35	female	bar@yahoo.co.jp
3	Baz	40	male	baz@yahoo.co.jp
4	Oops	30	female	oops@yahoo.co.jp

リスト : テーブルの作成 (test02.pl)

use strict;
use warnings;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=sample_perl.sqlite");

# テーブルの作成
$dbh->do("create table person (id integer, name text, age integer, sex text, email text);");

# データの挿入
my $sth = $dbh->prepare("insert into person (id, name, age, sex, email) values (?, ?, ?, ?, ?)");
$sth->execute(1, 'Foo', 50, 'male', 'foo@yahoo.co.jp');
$sth->execute(2, 'Bar', 35, 'female', 'bar@yahoo.co.jp');
$sth->execute(3, 'Baz', 40, 'male', 'baz@yahoo.co.jp');
$sth->execute(4, 'Oops', 30, 'female', 'oops@yahoo.co.jp');

$sth->finish;

#切断
$dbh->disconnect;

execute の引数は SQLite に渡されるとき文字列になるので、カラムには適切なデータ型を指定してください。ステートメントハンドル $sth の使用が終了したらメソッド finish で $sth を廃棄します。

また、次のようにヒアドキュメントを使うと、do でも簡単にデータを挿入することができます。

リスト : テーブルの作成 (test021.pl)

use strict;
use warnings;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=sample_perl.sqlite");

# テーブルの作成
$dbh->do("create table person (id integer, name text, age integer, sex text, email text);");

# データの挿入 (ヒアドキュメント)
my $str = << 'SQL';
insert into person (id, name, age, sex, email) values
(1, 'Foo', 50, 'male', 'foo@yahoo.co.jp'),
(2, 'Bar', 35, 'female', 'bar@yahoo.co.jp'),
(3, 'Baz', 40, 'male', 'baz@yahoo.co.jp'),
(4, 'Oops', 30, 'female', 'oops@yahoo.co.jp');
SQL

$dbh->do($str);

#切断
$dbh->disconnect;

●データの抽出

select 文のように結果を返す SQL 文は、メソッド prepare と execute を使って SQL 文を実行します。そのあと、メソッド fetch で 1 行ずつ結果を取得します。

リスト : データの抽出 (test03.pl)

use strict;
use warnings;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=sample_perl.sqlite");

# データの取得
my $sth = $dbh->prepare("select * from person");
$sth->execute;

while (my $row = $sth->fetch) {
    print "@$row\n";
}

$sth->finish;

#切断
$dbh->disconnect;

mhiroi@mhiroi-VirtualBox:~/sqlite$ perl test03.pl
1 Foo 50 male foo@yahoo.co.jp
2 Bar 35 female Bar@yahoo.co.jp
3 Baz 40 male baz@yahoo.co.jp
4 Oops 30 female oops@yahoo.co.jp

fetch は配列へのリファレンスを返します。この場合、0 番目にカラム id, 1 番目に name, 2 番目に age, 3 番目に sex, 4 番目に email の値が格納されます。アクセスが終了したらメソッド finish で $sth を廃棄します。

カラム名でアクセスしたい場合は、連想配列へのリファレンスを返すメソッド fetchrow_hashref を使ってください。

リスト : データの抽出 (test04.pl)

use strict;
use warnings;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=sample_perl.sqlite");

# データの取得
my $sth = $dbh->prepare("select * from person");
$sth->execute;

while (my $row = $sth->fetchrow_hashref) {
    print "$row->{'name'} $row->{'email'}\n";
}

$sth->finish;

#切断
$dbh->disconnect;

mhiroi@mhiroi-VirtualBox:~/sqlite$ perl test04.pl
Foo foo@yahoo.co.jp
Bar Bar@yahoo.co.jp
Baz baz@yahoo.co.jp
Oops oops@yahoo.co.jp

●トランザクションと AutoCommit

「トランザクション (transaction)」は処理とか取引という意味ですが、SQL では「関連した複数の処理を一つの処理にまとめたもの」をトランザクションといいます。SQL 文では、BEGIN でトランザクションを開始します。この場合、データベースの変更を伴う行う作業 (insert, update など) では、そのつどデータベースに変更が反映されるのではありません。トランザクションを終了して実際にデータベースの変更を行う SQL 文が COMMIT (コミット) です。

Perl で SQLite を操作する場合、オートコミット (AutoCommit) モードが設定されていると、トランザクションやコミットを明示的に指定しなくても、データベースを操作することができます。insert や update などの SQL 文を実行するとき、SQLite は暗黙のうちにトランザクションを開始します。さらに、オートコミットモードが有効だと、SQL 文が終了したとき、SQLite は自動的にコミットしてくれます。

これはとても便利な機能なのですが、コミットはけっこう時間がかかる処理なので、オートコミットモードでたくさんのデータをいっきに挿入しようとすると、時間がとてもかかるのです。次の例を見てください。

リスト : オートコミットモードでの挿入 (test05.pl)

use strict;
use warnings;
use DBI;

# 接続
my $dbh = DBI->connect('dbi:SQLite:dbname=test_perl.sqlite');

# テーブル作成
$dbh->do('create table test (name text, val real);');
$dbh->do('create index name_idx on test(name);');
my $sth = $dbh->prepare('insert into test (name, val) values (?, ?);');

# データ挿入
my $s = 'test00001';
for (my $i = 0; $i < 10000; $i++, $s++) {
    $sth->execute($s, rand());
}
$sth->finish;

# 切断
$dbh->disconnect;
print "OK\n";

test_perl.sqlite に TEXT と REAL を 10000 件挿入します。実行時間は次のようになりました。

mhiroi@mhiroi-VirtualBox:~/sqlite$ time perl test05.pl
OK

real	1m18.976s
user	0m0.996s
sys	0m6.336s

実行環境 : Lubuntu 16.04 on VirtualBox, Core i7-2670QM 2.20GHz

10000 件のデータを挿入するのに約 1 分 19 秒もかかりました。オートコミットモードをオフにすると、もっと高速にデータを挿入することができます。

リスト : オートコミットモードをオフにする (test06.pl)

use strict;
use warnings;
use DBI;

# 接続
my $dbh = DBI->connect('dbi:SQLite:dbname=test_perl.sqlite', "", "", {AutoCommit => 0});

# テーブル作成
$dbh->do('create table test (name text, val real);');
$dbh->do('create index name_idx on test(name);');
my $sth = $dbh->prepare('insert into test (name, val) values (?, ?);');

# データ挿入
my $s = 'test00001';
for (my $i = 0; $i < 10000; $i++, $s++) {
    $sth->execute($s, rand());
}
$dbh->commit;
$sth->finish;

# 切断
$dbh->disconnect;
print "OK\n";

データベースの設定を変更する場合、メソッド connect の第 4 引数にハッシュを渡します。{AutoCommit => 0} を渡すと、オートコミットモードをオフにすることができます。コミットする場合はメソッド commit を呼び出すだけです。実行結果は次のようになりました。

mhiroi@mhiroi-VirtualBox:~/sqlite$ time perl test06.pl
OK

real	0m0.167s
user	0m0.132s
sys	0m0.008s

実行環境 : Lubuntu 16.04 on VirtualBox, Core i7-2670QM 2.20GHz

10000 件のデータを 1 秒もかからずに挿入することができました。

●メモリ上にデータベースを作成する

SQLite はデータベース名に :memory: を指定すると、メモリ上にデータベースを作成することができます。簡単な例として、拙作のページお気楽 Perl プログラミング入門パズルの解法 (2) の 8 パズルのプログラムで、Perl のハッシュのかわりに SQLite を使ってみましょう。プログラムは次のようになります。

リスト: 8 パズルの解法 (幅優先探索)

sub bfs {
    my ($start, $goal) = @_;
    my @que = (make_state($start, index($start, '0'), $nil));
    my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:', "", "", {AutoCommit => 0});
    $dbh->do('create table state (board text);');
    $dbh->do('create index state_idx on state(board);');
    my $set = $dbh->prepare('insert into state (board) values (?);');
    my $get = $dbh->prepare('select board from state where board = ?;');
    $set->execute($start);
    while (@que > 0) {
        my $st = shift @que;
        my $s = $st->{'space'};
        foreach my $x (@{$adjacent[$s]}) {
            my $b = $st->{'board'};
            my $c = substr($b, $x, 1);
            $b =~ s/([0$c])(.*)([0$c])/$3$2$1/;     # 0 と交換する
            my $new_st = make_state($b, $x, $st);
            if ($b eq $goal) {
                print_answer($new_st);
                $set->finish;
                $get->finish;
                $dbh->disconnect;
                return;
            } else {
                $get->execute($b);
                if (!$get->fetch) {
                    $set->execute($b);
                    push @que, $new_st;
                }
            }
        }
    }
}

prepare でデータの挿入とチェックを行う SQL 文を生成して、変数 $set と $get にセットします。あとは、ハッシュのかわりに $set->execute と $get->execute を呼び出すだけです。実行結果は次のようになりました。

mhiroi@mhiroi-VirtualBox:~/sqlite$ time perl eight.pl
867254301
867204351
807264351
087264351
287064351
287364051
287364501
287364510
287360514
280367514
208367514
268307514
268037514
268537014
268537104
268537140
268530147
260538147
206538147
236508147
236058147
236158047
236158407
236158470
236150478
230156478
203156478
023156478
123056478
123456078
123456708
123456780

real	0m12.422s
user	0m12.344s
sys	0m0.040s

実行環境 : Lubuntu 16.04 on VirtualBox, Core i7-2670QM 2.20GHz

ハッシュを使ったプログラムは約 6.5 秒だったので、約 2 倍くらい遅くなりました。もっと遅くなると予想していたので、この結果にはちょっと驚きました。また、データベースをインメモリではなくファイルに変更したところ、実行時間は次のようになりました。

mhiroi@mhiroi-VirtualBox:~/sqlite$ time perl eight.pl
867254301
867204351
807264351

・・省略・・

123456078
123456708
123456780

real	0m12.886s
user	0m12.664s
sys	0m0.164s

実行環境 : Lubuntu 16.04 on VirtualBox, Core i7-2670QM 2.20GHz

実行速度はインメモリとほとんど同じでした。SQLite はとても速いですね。この結果には M.Hiroi も大変驚きました。

●参考文献, URL

Sriram Srinivasan, 『実用Ｐｅｒｌプログラミング』, オライリー・ジャパン, 1998
DBI - Perl 用データベース独立インターフェース, (perldoc.jp)
データベース入門 - PerlでSQLite, (KENT さん)

●プログラムリスト

#
# eight.pl : ８パズルの解法 (SQLite 版)
#
#            Copyright (C) 2016 Makoto Hiroi
#
use strict;
use warnings;
use DBI;

# 連結リストの終端
our $nil = {};

# 局面
sub make_state {
    my ($board, $space, $prev) = @_;
    {board => $board, space => $space, prev => $prev}
}

# 隣接リスト
our @adjacent = (
    [1, 3],       # 0
    [0, 4, 2],    # 1
    [1, 5],       # 2
    [0, 4, 6],    # 3
    [1, 3, 5, 7], # 4
    [2, 4, 8],    # 5
    [3, 7],       # 6
    [4, 6, 8],    # 7
    [5, 7]        # 8
    );

# 手順の表示
sub print_answer {
    my $st = shift;
    if ($st->{'prev'} != $nil) {
        print_answer($st->{'prev'});
    }
    print $st->{'board'}, "\n";
}

# 幅優先探索
sub bfs {
    my ($start, $goal) = @_;
    my @que = (make_state($start, index($start, '0'), $nil));
    my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:', "", "", {AutoCommit => 0});
    $dbh->do('create table state (board text);');
    $dbh->do('create index state_idx on state(board);');
    my $set = $dbh->prepare('insert into state (board) values (?);');
    my $get = $dbh->prepare('select board from state where board = ?;');
    $set->execute($start);
    while (@que > 0) {
        my $st = shift @que;
        my $s = $st->{'space'};
        foreach my $x (@{$adjacent[$s]}) {
            my $b = $st->{'board'};
            my $c = substr($b, $x, 1);
            $b =~ s/([0$c])(.*)([0$c])/$3$2$1/;     # 0 と交換する
            my $new_st = make_state($b, $x, $st);
            if ($b eq $goal) {
                print_answer($new_st);
                $set->finish;
                $get->finish;
                $dbh->disconnect;
                return;
            } else {
                $get->execute($b);
                if (!$get->fetch) {
                    $set->execute($b);
                    push @que, $new_st;
                }
            }
        }
    }
}

# 実行
bfs("867254301", "123456780");

Linux Programming