【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;

 

実行結果

  1. selectdb01.plで更新前の状態を表示。
  2. updatedb.plを実行しデータを更新。
  3. 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;

 

実行結果

  1. selectdb01.plで削除前の状態を表示。
  2. deletedb.plでレコードの削除。
  3. 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>


参考

タグ : , , ,