SQLiteを使うので、DBIとDBD::SQLiteがインストールされていること
<参考>
DBI [ver1.628]
DBD::SQLite [ver1.40]
覚えること
| No | メソッド | 解説 |
|---|---|---|
| 1 | connect | DBに接続 |
| disconnect | DBと切断 | |
| 2 | do | SQLを実行 |
| prepare | SQLを準備 | |
| execute | SQLを実行 | |
| prepare_cached | 準備した結果をcacheする | |
| 3 | fetch | 配列のリファレンスを返す |
| fetchrow_arrayref | 配列のリファレンスを返す | |
| fetchrow_hashref | 列をキーに値をリファレンスで返す |
■1.connect, disconnectメソッド
use strict;
use warnings;
use utf8;
use DBI;
use feature 'say';
my $database = ':memory:';
my $connectStr = "dbi:SQLite:dbname=$database";
my $userName = "";
my $passWord = "";
#接続
my $dbh = DBI->connect($connectStr,
$userName,
$passWord,
{RaiseError => 1,
PrintError => 0,
AutoCommit => 1});
#切断
$dbh->disconnect();
[第一引数]接続するための文字列は、
| [SQLite] | dbi:SQLite:DB名 |
| [MySQL] | dbi:mysql:DB名 |
| [PostgreSQL] | dbi:pg:DB名 |
| [Oracle] | dbi:oracle:DB名 |
:memory:と記載するとメモリ上に作成します。
当然ながら、プログラムが終了したら消えます。
サンプルコード等のお試しに最適です。
[第二引数]username
[第三引数]password
SQLiteではユーザー名、パスワードがないので、空文字のままにしています。
他のDBでは設定します。
[第四引数]オプション
ここは、いろいろあるのでDBIを参照するのがよいと思います。
http://search.cpan.org/search?query=DBI&mode=all
よく登場するオプションもメモ。
□RaiseError[デフォルト:偽]
エラーが発生すると自動的にdieして終了してくれます。
これを真にしないのであれば、各メソッド毎にエラー処理を書く必要があります。
□PrintError[デフォルト:真]
エラー時warnで出力してくれる機能。
デフォルトで真だけど、RaiseErrorを真にするのであれば
表示内容がかぶるのでこちらは偽にしておく。
□AutoCommti[デフォルト：真?]
全ての命令毎にCommitします。
ドライバによって、デフォルト値が異なるようですので明示的に指定した方がよいようです。
AutoCommitが真の時、
一つの命令毎にCommitされるため
トランザクションできなくなります。
トランザクションを行いたい時は、明示的に指示をします。
#ここまでAutoCommitエリア
#ここからトランザクション開始
$dbh->begin_work;
#何かしらの処理
$dbh->commit(); or $dbh->rollback();
#トランザクション区間終了
#ここからAutoCommitエリア
オプションに関しては、他にも必要とするのがあるのですが
まずは、DBの操作について話した後でないと検証ができないので
のちほどあらためて掲載します。
■2.do, prepare, execute, prepare_cached
□doメソッド
my $sql = "INSERT INTO sample (col1, col2) VALUES (?, ?)"; my @data = qw(data1 data2); my $sth = $dbh->do($sql, undef, @data);
・ステートメントハンドルを作成しないため、SELECT文のように値を取得する場面では使えません。
・ステートメントハンドルを生成しない分、高速に動きます。
・INSERT, UPDATE, DELETEなどなど
□prepare, executeメソッド
my $sql = "SELECT * FROM sample WHERE col1 = ? AND col2 = ?"; my $sth = $dbh->prepare($sql); my @data = qw(data1 data2); my $rv = $sth->execute(@data);・事前に準備(prepare)するため、何度もINSERTする際などループの外でprepareしておけば
その分高速に動きます。つまり、繰り返し同じSQLを実行する場合prepareしておけばよいということです。
□prepare_cachedメソッド
prepareした物を内部でハッシュに紐づけてキャッシュします。
ループ分のように連続して使用するのでなく、別の関数等で再び利用する場合
キャッシュしておけば、prepareの行為を省略できるのでその分だけ高速になります。
あちこちで同じSQLを使用するのであればキャッシュして使いましょう。
■3.fetch, fetchrow_arrayref, fetchrow_hashref
□fetch, fetchrow_arrayref
my $sql = "SELECT * FROM sample";
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute();
while (my $row = $sth->fetch()){
my ($col1, $col2, col3) = @{$row};
#処理
}fetchとfetchrow_arrayrefは同じ結果となります。つまり、配列のリファレンスを返してくれます。
□fetchrow_hashref
my $sql = "SELECT * FROM sample";
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute();
while (my $row = $sth->fetchrow_hashref()){
say $row->{'col1'};
say $row->{'col2'};
say $row->{'col3'};
}■DBI->connectのオプションについて
他のオプションについても紹介しときます。
□ShowErrorStatement
エラーとなったSQLを出力します。
通常、行数しか表示されないためその行まで移動しないと何が原因かわからないところを
SQLを表示するので何でエラーとなったか把握しやすくなります。
□DBから受け取る時、内部表現にしてくれる
MySQL→ mysql_enable_utf8
SQLite→ sqlite_unicode
PostgresSQL → pg_enable_utf8
出力時自動的に内部表現から戻す処理になっているのであれば
DBから受け取る際に内部表現にしとく必要がありますよね。。
その時に役立ちます。
■サンプルコード
地名を５つほど登録して出力するプログラムです。
use strict;
use warnings;
use utf8;
use DBI;
use feature 'say';
binmode STDOUT, ":utf8";
my $database = ':memory:';
my $connectStr = "dbi:SQLite:dbname=$database";
my $userName = "";
my $passWord = "";
my $sql = "";
my $dbh = DBI->connect($connectStr,
$userName,
$passWord,
{RaiseError => 1,
PrintError => 0,
AutoCommit => 1,
sqlite_unicode => 1
});
#CreateTable & InsertData
initialize($dbh);
$sql = "SELECT * FROM test";
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute();
#arrayrefで受け取る場合
#$sth->fetchrow_arrayref()
#$sth->fetch()は、同じメソッド
while (my $row = $sth->fetch()){
my ($id, $area) = @{$row};
say "$id:$area";
}
$sth->finish();
$dbh->disconnect();
sub initialize{
my $dbh = shift;
#テーブルを作成(idとstrの列のみ)
my $sql = "CREATE TABLE IF NOT EXISTS test(id INTEGER PRIMARY KEY AUTOINCREMENT, area TEXT)";
$dbh->do($sql);
#str列に名前を登録
my @list = qw(興部 椴法華村 音威子府 長万部 札幌);
$sql = "INSERT INTO test (area) VALUES(?)";
for my $area (@list){
$dbh->do($sql, undef, $area);
}
}
