【perlメモ】SQLite DBI/DBDを使ったCREATE TABLE,INSERT,SELECT,UPDATE,DELETEのサンプル
SQLiteとDBI/DBDを使ってperlでプログラミングすることがよく有りますが、よく使うけどそのたびに過去のに書いたソース探したりして面倒なのでまとめておきます。
データベースファイルの作成
データベースファイルとテーブルを作成するサンプル。
make.pl
#!/usr/bin/perl
#
#
#http://www.sqlite.org/datatype3.html
#
# 1. Storage Classes
# Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:
#
# * NULL. The value is a NULL value.
# * INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
# * REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
# * TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
# * BLOB. The value is a blob of data, stored exactly as it was input.
#
#
# テーブル名：TBL_USERS
#0 idx INTEGER PRIMARY KEY プライマリーキー
#1 name INTEGER 名前
#2 stat INTEGER ステータス
#
use DBI;
my $mydb = "TEST.DB";
my $hDB = DBI->connect("dbi:SQLite:dbname=$mydb","","",{PrintError=>0});
die $DBI::errstr unless($hDB);
my $sql = "CREATE TABLE TBL_USERS(
idx INTEGER PRIMARY KEY,
name TEXT,
stat INTEGER
)";
my $ret = $hDB->do($sql);
unless($ret){
die $hDB->errstr . " ret=$ret";
}
$hDB->disconnect;
実行するとTEST.DBというデータベースファイルが作成されます。
データの挿入(INSERT)
データベースにデータを追加します。
prepareしてexcuteするやり方
insertdb01.pl
#!/usr/bin/perl
#
#http://www.sqlite.org/datatype3.html
#
# テーブル名：TBL_USERS
#0 idx INTEGER PRIMARY KEY プライマリーキー
#1 name INTEGER 名前
#2 stat INTEGER ステータス
#
use strict;
use warnings;
use utf8;
use DBI;
my $mydb = "TEST.DB";
my $hDB = DBI->connect("dbi:SQLite:dbname=$mydb","","",{PrintError=>0});
die $DBI::errstr unless($hDB);
#$hDB->do(‘BEGIN’);
my $sql = "INSERT INTO TBL_USERS VALUES(NULL,?,?)";
#戻り値はDBハンドル
my $hst = $hDB->prepare($sql);
unless($hst){
my $errstr = $hDB->errstr;
$hDB->disconnect;
die $errstr;
}
my $ret;
#戻り値は真偽値、select以外は影響を受けたレコード数0件の時は0E0(真値の0)を返す。
$ret = $hst->execute(‘TANAKA’,0);
if(!$ret || $ret==0E0){
my $errstr = $hDB->errstr;
$hDB->rollback;
$hst->finish;
undef $hst;
$hDB->disconnect;
die $errstr;
}
$ret = $hst->execute(‘NAKAMURA’,0);
if(!$ret || $ret==0E0){
my $errstr = $hDB->errstr;
$hDB->rollback;
$hst->finish;
undef $hst;
$hDB->disconnect;
die $errstr;
}
$ret = $hst->execute(‘SUZUKI’,0);
if(!$ret || $ret==0E0){
my $errstr = $hDB->errstr;
$hDB->rollback;
$hst->finish;
undef $hst;
$hDB->disconnect;
die $errstr;
}
#$hDB->do(‘COMMIT’);
$hDB->commit;
$hst->finish;
undef $hst;#SQLiteバグ対策のおまじない
$hDB->disconnect;
doでやる方法
insertdb02.pl
#!/usr/bin/perl
#
#http://www.sqlite.org/datatype3.html
#
# テーブル名：TBL_USERS
#0 idx INTEGER PRIMARY KEY プライマリーキー
#1 name INTEGER 名前
#2 stat INTEGER ステータス
#
use strict;
use warnings;
use utf8;
use DBI;
my $mydb = "TEST.DB";
my $hDB = DBI->connect("dbi:SQLite:dbname=$mydb","","",{PrintError=>0});
die $DBI::errstr unless($hDB);
#$hDB->do(‘BEGIN’);
my $sql = "INSERT INTO TBL_USERS VALUES(NULL,?,?)";
my $ret;
#doはprepareとexecuteをまとめて行なう。影響を受けたレコード数を返す0件の時は0E0(真値の0)を返す。
$ret = $hDB->do($sql,undef,(‘ITOU’,1));
if(!$ret || $ret==0E0){
my $errstr = $hDB->errstr;
$hDB->rollback;
$hDB->disconnect;
die $errstr;
}
$ret = $hDB->do($sql,undef,(‘SAKATA’,2));
if(!$ret || $ret==0E0){
my $errstr = $hDB->errstr;
$hDB->rollback;
$hDB->disconnect;
die $errstr;
}
$ret = $hDB->do($sql,undef,(‘SATO’,3));
if(!$ret || $ret==0E0){
my $errstr = $hDB->errstr;
$hDB->rollback;
$hDB->disconnect;
die $errstr;
}
#$hDB->do(‘COMMIT’);
$hDB->commit;
$hDB->disconnect;
上記3つのプロうグラムの実行結果
F:\kumacchi\MyProgram\perl\sample\sqlite>perl makedb.pl
F:\kumacchi\MyProgram\perl\sample\sqlite>perl insertdb01.pl
F:\kumacchi\MyProgram\perl\sample\sqlite>perl insertdb02.pl
F:\kumacchi\MyProgram\perl\sample\sqlite>
データの抽出（SELECT)
総レコード件数と前レコードを抽出して表示するサンプル。
selectdb01.pl
#!/usr/bin/perl
#
#http://www.sqlite.org/datatype3.html
#
# テーブル名：TBL_USERS
#0 idx INTEGER PRIMARY KEY プライマリーキー
#1 name INTEGER 名前
#2 stat INTEGER ステータス
#
use strict;
use warnings;
use utf8;
use DBI;
my $mydb = "TEST.DB";
my $hDB = DBI->connect("dbi:SQLite:dbname=$mydb","","",{PrintError=>0});
die $DBI::errstr unless($hDB);
my $hst = $hDB->prepare(‘SELECT COUNT(*) FROM TBL_USERS’);
unless($hst){
my $errstr = $hDB->errstr;
$hDB->disconnect;
die $errstr;
}
#戻り値は真偽値、select以外は影響を受けたレコード数0件の時は0E0(真値の0)を返す。
my $ret = $hst->execute();
unless($ret){
my $errstr = $hDB->errstr;
$hst->finish;
undef $hst;
$hDB->disconnect;
die $errstr;
}
if(my $ref = $hst->fetchrow_arrayref){
my $count = $ref->[0];
print "count = $count\n";
}
#my $sql = "SELECT idx,name,stat FROM TBL_USERS WHERE stat=?";
my $sql = "SELECT idx,name,stat FROM TBL_USERS";
#戻り値はDBハンドル
$hst = $hDB->prepare($sql);
unless($hst){
my $errstr = $hDB->errstr;
$hDB->disconnect;
die $errstr;
}
#戻り値は真偽値、select以外は影響を受けたレコード数0件の時は0E0(真値の0)を返す。
#$ret = $hst->execute(0);
$ret = $hst->execute();
unless($ret){
my $errstr = $hDB->errstr;
$hst->finish;
undef $hst;
$hDB->disconnect;
die $errstr;
}
while(my $ref = $hst->fetchrow_arrayref){
my($idx,$name,$stat) = @$ref;
print "idx=$idx name=$name stat=$stat\n";
}
$hst->finish;
undef $hst;#SQLiteバグ対策のおまじない
$hDB->disconnect;
実行結果
F:\kumacchi\MyProgram\perl\sample\sqlite>perl selectdb01.pl
count = 6
idx=1 name=TANAKA stat=0
idx=2 name=NAKAMURA stat=0
idx=3 name=SUZUKI stat=0
idx=4 name=ITOU stat=1
idx=5 name=SAKATA stat=2
idx=6 name=SATO stat=3
F:\kumacchi\MyProgram\perl\sample\sqlite>
データの更新(UPDATE)
statが0のレコードのnameをYAMADA、stat=33に更新するプログラム
updatedb.pl
#!/usr/bin/perl
#
#http://www.sqlite.org/datatype3.html
#
# テーブル名：TBL_USERS
#0 idx INTEGER PRIMARY KEY プライマリーキー
#1 name INTEGER 名前
#2 stat INTEGER ステータス
#
use strict;
use warnings;
use utf8;
use DBI;
my $mydb = "TEST.DB";
my $hDB = DBI->connect("dbi:SQLite:dbname=$mydb","","",{PrintError=>0});
die $DBI::errstr unless($hDB);
#$hDB->do(‘BEGIN’);
my $sql = "UPDATE TBL_USERS SET name=?, stat=? WHERE stat=?";
my $ret;
#doはprepareとexecuteをまとめて行なう。影響を受けたレコード数を返す0件の時は0E0(真値の0)を返す。
$ret = $hDB->do($sql,undef,(‘YAMADA’,33,0));
#if(!$ret || $ret==0E0){
if(!$ret){
my $errstr = $hDB->errstr;
$hDB->rollback;
$hDB->disconnect;
die $errstr;
}
print "ret=$ret\n";
#$hDB->do(‘COMMIT’);
$hDB->commit;
$hDB->disconnect;
実行結果
- selectdb01.plで更新前の状態を表示。
- updatedb.plを実行しデータを更新。
- selectdb01.plを実行し更新後のデータを表示。
F:\kumacchi\MyProgram\perl\sample\sqlite>perl selectdb01.pl
count = 6
idx=1 name=TANAKA stat=0
idx=2 name=NAKAMURA stat=0
idx=3 name=SUZUKI stat=0
idx=4 name=ITOU stat=1
idx=5 name=SAKATA stat=2
idx=6 name=SATO stat=3
F:\kumacchi\MyProgram\perl\sample\sqlite>perl updatedb.pl
ret=3
F:\kumacchi\MyProgram\perl\sample\sqlite>perl selectdb01.pl
count = 6
idx=1 name=YAMADA stat=33
idx=2 name=YAMADA stat=33
idx=3 name=YAMADA stat=33
idx=4 name=ITOU stat=1
idx=5 name=SAKATA stat=2
idx=6 name=SATO stat=3
F:\kumacchi\MyProgram\perl\sample\sqlite>
データの削除(DELETE)
statが33のレコードを削除するプログラムサンプル。
deletedb.pl
#!/usr/bin/perl
#
#http://www.sqlite.org/datatype3.html
#
# テーブル名：TBL_USERS
#0 idx INTEGER PRIMARY KEY プライマリーキー
#1 name INTEGER 名前
#2 stat INTEGER ステータス
#
use strict;
use warnings;
use utf8;
use DBI;
my $mydb = "TEST.DB";
my $hDB = DBI->connect("dbi:SQLite:dbname=$mydb","","",{PrintError=>0});
die $DBI::errstr unless($hDB);
#$hDB->do(‘BEGIN’);
my $sql = "DELETE FROM TBL_USERS WHERE stat=?";
#doはprepareとexecuteをまとめて行なう。影響を受けたレコード数を返す0件の時は0E0(真値の0)を返す。
my $ret = $hDB->do($sql,undef,(33));
#if(!$ret || $ret==0E0){
if(!$ret){
my $errstr = $hDB->errstr;
$hDB->rollback;
$hDB->disconnect;
die $errstr;
}
print "ret=$ret\n";
#$hDB->do(‘COMMIT’);
$hDB->commit;
$hDB->disconnect;
実行結果
- selectdb01.plで削除前の状態を表示。
- deletedb.plでレコードの削除。
- selectdb01.plで削除後の状態を表示。
F:\kumacchi\MyProgram\perl\sample\sqlite>perl selectdb01.pl
count = 6
idx=1 name=YAMADA stat=33
idx=2 name=YAMADA stat=33
idx=3 name=YAMADA stat=33
idx=4 name=ITOU stat=1
idx=5 name=SAKATA stat=2
idx=6 name=SATO stat=3
F:\kumacchi\MyProgram\perl\sample\sqlite>perl deletedb.pl
ret=3
F:\kumacchi\MyProgram\perl\sample\sqlite>perl selectdb01.pl
count = 3
idx=4 name=ITOU stat=1
idx=5 name=SAKATA stat=2
idx=6 name=SATO stat=3
F:\kumacchi\MyProgram\perl\sample\sqlite>
参考
関連記事：
[SQLiteメモ][perlメモ]SQLiteで一つのコネクションで複数のデータベースファイルを扱うには？- SQLiteメモ：オートインクリメントするカラムの値をINSERT直後に取り出すには
- 【perlメモ】XML::Simpleの使い方。
- 【perlメモ】ワイルドカード「.」(ピリオド)がパターンに含まれるときマッチしてしまわないようにするには？
- 【perlメモ】perl5.8系utf8関連の自分用まとめ
- 【perlメモ】Algorithm::NaiveBayesモジュールを使ったスパム判定
- 【perlメモ】短縮URLなどからリダイレクト先の実際のURLを取得する方法
【perlメモ】CPANモジュール「GD::Barcode」を利用してQRコードを作成するサンプル- 【perlメモ】coreserverでcronでDBIを使ってSQLiteを弄るcgiを実行すると「database table is locked(1) at dbdimp.c line 398」になる件
- [MySQLメモ]オートインクリメントするカラムの値をINSERT直後に取り出すには
- 【perlメモ】Algorithm::NaiveBayesのサンプル
- [SQLite]レコードの内新しい方から指定した件数残すには？
sqlite3でデータをインポートとエクスポートする方法
[perlメモ]Cache::FileCacheを利用したキャッシュでキャッシュの作成日時と期限日時の情報を取得したい。- [perlメモ]ファイルの情報を取得するstat関数
- 【perlメモ】測地系変換モジュール『Location::GeoTool』のサンプル
MTからWPに移行してタグがおかしくなっていた記事をリペアした件。- perlメモ：ファイルのタイムスタンプを変更するには？
- 【perlメモ】ヒアドキュメントの中に直接コードを書く方法
- CentOS5.6にSybase ASE 11.0.3.3とDBD::Sybase 1.14をインストールしてみた
- 日時の処理
[perlメモ]楽天ウェブAPIのlastBuildDateを通常日付に変換する方法
[SQLiteメモ]SQLiteのデータ型- 【SQLiteメモ】DEFAULT制約を使ってINSERT時に自動でタイムスタンプを設定する。
sqliteのインストール- [perlメモ]ActivePerlでUNZIP32.DLLを使ってzipのパスワードを解析してみる実験。
- 【perlメモ】use bigint時にmkdirでハマった件。
- 【perlメモ】配列の処理を改善したら処理がずいぶん早くなった件
- 【perlメモ】flockの使い方
【perlメモ】画像にモザイクをかけるサンプル
