Perlデータベースアクセス

このチュートリアルでは、Perlスクリプト内のデータベースにアクセスする方法を説明します。 Perl 5から、 DBI モジュールを使用してデータベースアプリケーションを非常に簡単に作成できるようになりました。 DBIはPerlの Database Independent Interface の略で、DBIがPerlコードと基礎となるデータベース間の抽象化レイヤーを提供し、データベース実装を簡単に切り替えることができることを意味します。

DBIは、Perlプログラミング言語用のデータベースアクセスモジュールです。使用される実際のデータベースに関係なく、一貫したデータベースインターフェイスを提供する一連のメソッド、変数、および規則を提供します。

DBIアプリケーションのアーキテクチャ

DBIは、バックエンドで使用可能なデータベースから独立しています。 Oracle、MySQL、Informixなどのいずれで作業していても、DBIを使用できます。これは、次のアーキテクチャ図から明らかです。

PerlデータベースモジュールDBIアーキテクチャ

ここで、DBIはAPIを介してすべてのSQLコマンドを取得する責任があります（つまり、アプリケーションプログラミングインターフェイス）、および実際の実行のために適切なドライバーにディスパッチします。そして最後に、DBIはドライバーから結果を取得し、呼び出し元のスクリプトに返す責任があります。

表記法と規則

この章では、次の表記法を使用します。同じ規則に従うことをお勧めします。

  $dsn    Database source name
  $dbh    Database handle object
  $sth    Statement handle object
  $h      Any of the handle types above ($dbh, $sth, or $drh)
  $rc     General Return Code  (boolean: true=ok, false=error)
  $rv     General Return Value (typically an integer)
  @ary    List of values returned from the database.
  $rows   Number of rows processed (if available, else -1)
  $fh     A filehandle
  undef   NULL values are represented by undefined values in Perl
  \%attr  Reference to a hash of attribute values passed to methods

データベース接続

MySQLデータベースを使用すると仮定します。データベースに接続する前に、以下を確認してください。 MySQLデータベースにデータベースとテーブルを作成する方法を知らない場合は、MySQLチュートリアルを利用できます。

TESTDBという名前のデータベースを作成しました。
TESTDBにTEST_TABLEという名前のテーブルを作成しました。
このテーブルには、FIRST_NAME、LAST_NAME、AGE、SEX、およびINCOMEフィールドがあります。
ユーザーID「testuser」とパスワード「test123」がTESTDBにアクセスするように設定されています
PerlモジュールDBIがマシンに正しくインストールされています。
MySQLの基本を理解するために、MySQLチュートリアルを完了しました。

以下は、MySQLデータベース「TESTDB」と接続する例です

#!/usr/bin/perl

use DBI
use strict;

my $driver = "mysql";
my $database = "TESTDB";
my $dsn = "DBI:$driver:database=$database";
my $userid = "testuser";
my $password = "test123";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

データソースとの接続が確立されると、データベースハンドルが返され、さらに使用するために$ dbhに保存されます。

INSERT操作

いくつかのレコードをテーブルに作成する場合は、INSERT操作が必要です。ここでは、テーブルTEST_TABLEを使用してレコードを作成しています。したがって、データベース接続が確立されると、TEST_TABLEにレコードを作成する準備が整います。以下は、TEST_TABLEに単一のレコードを作成する手順です。同じコンセプトを使用して、好きなだけレコードを作成できます。

レコードの作成には次の手順が必要です

INSERTステートメントを使用したSQLステートメントの準備。これは、* prepare（）* APIを使用して行われます。
SQLクエリを実行して、データベースからすべての結果を選択します。これは、* execute（）* APIを使用して行われます。
Stattementハンドルを解放します。これは* finish（）* APIを使用して行われます
すべてが正常に完了したら、この操作を*コミット*します。それ以外の場合は、トランザクションを*ロールバック*できます。コミットとロールバックについては、次のセクションで説明します。

my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                        values
                       ('john', 'poul', 'M', 30, 13000)");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

バインド値の使用

入力する値が事前に指定されていない場合があります。そのため、実行時に必要な値を取るバインド変数を使用できます。 Perl DBIモジュールは、実際の値の代わりに疑問符を使用し、実行時に実際の値がexecute（）APIを介して渡されます。次に例を示します。

my $first_name = "john";
my $last_name = "poul";
my $sex = "M";
my $income = 13000;
my $age = 30;
my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                        values
                       (?,?,?,?)");
$sth->execute($first_name,$last_name,$sex, $age, $income)
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

読み取り操作

任意のデータベースでの読み取り操作とは、データベースからいくつかの有用な情報、つまり1つ以上のテーブルから1つ以上のレコードを取得することです。したがって、データベース接続が確立されると、このデータベースにクエリを実行する準備が整います。以下は、20より大きいAGEを持つすべてのレコードを照会する手順です。これには4つのステップが必要です

必要な条件に基づいたSQL SELECTクエリの準備。これは、* prepare（）* APIを使用して行われます。
SQLクエリを実行して、データベースからすべての結果を選択します。これは、* execute（）* APIを使用して行われます。
すべての結果を1つずつ取得し、それらの結果を出力します。これは、* fetchrow_array（）* APIを使用して行われます。
Stattementハンドルを解放します。これは* finish（）* APIを使用して行われます

my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE
                        WHERE AGE > 20");
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

バインド値の使用

事前に条件が与えられない場合があります。そのため、実行時に必要な値を取るバインド変数を使用できます。 Perl DBIモジュールは、実際の値の代わりに疑問符を使用し、実行時に実際の値がexecute（）APIを介して渡されます。次に例を示します。

$age = 20;
my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE
                        WHERE AGE > ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

更新操作

UPDATE操作は、データベーステーブルで既に利用可能な1つ以上のレコードを更新することを意味します。以下は、SEXが「M」であるすべてのレコードを更新する手順です。ここでは、すべての男性の年齢を1年増やします。これには3つのステップが必要です

必要な条件に基づいてSQLクエリを準備します。これは、* prepare（）* APIを使用して行われます。
SQLクエリを実行して、データベースからすべての結果を選択します。これは、* execute（）* APIを使用して行われます。
Stattementハンドルを解放します。これは* finish（）* APIを使用して行われます
すべてが正常に完了したら、この操作を*コミット*します。それ以外の場合は、トランザクションを*ロールバック*できます。コミットおよびロールバックAPIについては、次のセクションをご覧ください。

my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1
                        WHERE SEX = 'M'");
$sth->execute() or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

バインド値の使用

事前に条件が与えられない場合があります。そのため、実行時に必要な値を取るバインド変数を使用できます。 Perl DBIモジュールは、実際の値の代わりに疑問符を使用し、実行時に実際の値がexecute（）APIを介して渡されます。次に例を示します。

$sex = 'M';
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1
                        WHERE SEX = ?");
$sth->execute('$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

次のようにバインディング値を使用できるように、事前に指定されていない値を設定したい場合があります。この例では、すべての男性の収入が10000に設定されます。

$sex = 'M';
$income = 10000;
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   INCOME = ?
                        WHERE SEX = ?");
$sth->execute( $income, '$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();

削除操作

データベースから一部のレコードを削除する場合は、DELETE操作が必要です。以下は、AGEが30であるTEST_TABLEからすべてのレコードを削除する手順です。この操作では、次の手順を実行します。

必要な条件に基づいてSQLクエリを準備します。これは、* prepare（）* APIを使用して行われます。
SQLクエリを実行して、データベースから必要なレコードを削除します。これは、* execute（）* APIを使用して行われます。
Stattementハンドルを解放します。これは* finish（）* APIを使用して行われます
すべてが正常に完了したら、この操作を*コミット*します。それ以外の場合は、トランザクションを*ロールバック*できます。

$age = 30;
my $sth = $dbh->prepare("DELETE FROM TEST_TABLE
                        WHERE AGE = ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

do ステートメントの使用

UPDATE、INSERT、またはDELETEを実行している場合、データベースから返されるデータはないため、この操作を実行するためのショートカットがあります。 do ステートメントを使用して、次のようにコマンドを実行できます。

$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');

*do* は、成功すると真の値を返し、失敗すると偽の値を返します。 実際、成功すると、影響を受けた行の数を返します。 この例では、実際に削除された行の数を返します。

コミット操作

コミットは、変更をファイナライズするためにデータベースに緑のシグナルを与える操作であり、この操作の後、変更を元の位置に戻すことはできません。

*commit* APIを呼び出す簡単な例を次に示します。

$dbh->commit or die $dbh->errstr;

ロールバック操作

すべての変更に満足できない場合、または操作の間にエラーが発生した場合は、それらの変更を元に戻して rollback APIを使用できます。

以下は、 rollback APIを呼び出す簡単な例です。

$dbh->rollback or die $dbh->errstr;

トランザクション開始

多くのデータベースはトランザクションをサポートしています。これは、データベースを変更するクエリの全体を作成できることを意味しますが、実際には変更は行われません。最後に、特別なSQLクエリ COMMIT を発行すると、すべての変更が同時に行われます。または、クエリROLLBACKを発行できます。この場合、すべての変更は破棄され、データベースは変更されません。

Perl DBIモジュールは begin_work APIを提供し、次のコミットまたはロールバックの呼び出しまでトランザクションを有効にします（AutoCommitをオフにすることにより）。次のコミットまたはロールバックの後、AutoCommitは再び自動的にオンになります。

$rc  = $dbh->begin_work  or die $dbh->errstr;

AutoCommitオプション

トランザクションが単純な場合、多くのコミットを発行する手間を省くことができます。接続呼び出しを行う場合、クエリが成功するたびに自動コミット操作を実行する AutoCommit オプションを指定できます。これは次のようなものです。

my $dbh = DBI->connect($dsn, $userid, $password,
              {AutoCommit => 1})
              or die $DBI::errstr;

ここで、AutoCommitは値1または0を取ることができます。1はAutoCommitがオンになり、0はAutoCommitがオフになることを意味します。

自動エラー処理

接続呼び出しを行うとき、エラーを自動的に処理するRaiseErrorsオプションを指定できます。エラーが発生すると、DBIはエラーコードを返す代わりにプログラムを中止します。エラーでプログラムを中止するだけであれば、これは便利です。これは次のようなものです。

my $dbh = DBI->connect($dsn, $userid, $password,
              {RaiseError => 1})
              or die $DBI::errstr;

ここで、RaiseErrorは値1または0を取ります。

データベースの切断

データベース接続を切断するには、次のように disconnect APIを使用します。

$rc = $dbh->disconnect  or warn $dbh->errstr;

悲しいことに、disconnectメソッドのトランザクションの動作は未定義です。一部のデータベースシステム（OracleやIngresなど）は未解決の変更を自動的にコミットしますが、他のデータベースシステム（Informixなど）は未解決の変更をロールバックします。 AutoCommitを使用しないアプリケーションは、disconnectを呼び出す前に、commitまたはrollbackを明示的に呼び出す必要があります。

NULL値を使用する

未定義の値、またはundefは、NULL値を示すために使用されます。非NULL値の場合と同様に、NULL値を持つ列を挿入および更新できます。次の例では、列の経過時間をNULL値で挿入および更新します。

$sth = $dbh->prepare(qq{
       INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)
       });
 $sth->execute("Joe", undef);

ここで、 qq \ {} は、引用符付き文字列を prepare APIに返すために使用されます。ただし、WHERE句でNULL値を使用する場合は注意が必要です。検討してください：

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

undef（NULL）をプレースホルダーにバインドすると、年齢がNULLの行は選択されません！少なくとも標準SQLに準拠するデータベースエンジン用。この理由については、ご使用のデータベースエンジンのSQLマニュアルまたはSQLの本を参照してください。 NULLを明示的に選択するには、「WHERE age IS NULL」と言わなければなりません。

一般的な問題は、実行時に定義済みまたはundef（非NULLまたはNULL）のいずれかの値をコードフラグメントで処理することです。簡単な手法は、必要に応じて適切なステートメントを準備し、NULL以外の場合のプレースホルダーを置き換えることです。

$sql_clause = defined $age? "age = ?" : "age IS NULL";
$sth = $dbh->prepare(qq{
       SELECT FIRST_NAME FROM TEST_TABLE WHERE $sql_clause
       });
$sth->execute(defined $age ? $age : ());

その他のDBI関数

available_drivers

@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);

@INCのディレクトリからDBD: *モジュールを検索することにより、使用可能なすべてのドライバーのリストを返します。デフォルトでは、一部のドライバーが以前のディレクトリにある同じ名前の他のドライバーによって隠されている場合、警告が表示されます。 $ quietに真の値を渡すと、警告が抑制されます。

installed_drivers

%drivers = DBI->installed_drivers();

現在のプロセスに「インストール」（ロード）されているすべてのドライバーのドライバー名とドライバーハンドルのペアのリストを返します。ドライバー名には「DBD ::」プレフィックスは含まれません。

data_sources

@ary = DBI->data_sources($driver);

指定されたドライバーを介して利用可能なデータソース（データベース）のリストを返します。 $ driverが空またはundefの場合、DBI_DRIVER環境変数の値が使用されます。

見積もり

$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);

文字列内に含まれる特殊文字（引用符など）をエスケープし、必要な種類の外部引用符を追加することにより、SQLステートメントでリテラル値として使用する文字列リテラルを引用します。

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
                $dbh->quote("Don't");

ほとんどのデータベースタイプでは、quoteは 'Dont'（外側の引用符を含む）を返します。 quote（）メソッドが、目的の文字列に評価されるSQL式を返すことは有効です。例えば：

$quoted = $dbh->quote("one\ntwo\0three")

may produce results which will be equivalent to

CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')

すべてのハンドルに共通のメソッド

err

$rv = $h->err;
or
$rv = $DBI::err
or
$rv = $h->err

最後に呼び出されたドライバーメソッドからネイティブデータベースエンジンのエラーコードを返します。通常、コードは整数ですが、それを想定しないでください。これは、$ DBI: errまたは$ h→ errと同等です。

errstr

$str = $h->errstr;
or
$str = $DBI::errstr
or
$str = $h->errstr

最後に呼び出されたDBIメソッドからのネイティブデータベースエンジンエラーメッセージを返します。これには、上記の「err」メソッドと同じ寿命の問題があります。これは、$ DBI: errstrまたは$ h→ errstrと同等です。

rows

$rv = $h->rows;
or
$rv = $DBI::rows

これは、前のSQLステートメントの影響を受けた行数を返し、$ DBI: rowsと同等です。

トレース

$h->trace($trace_settings);

DBIは、実行中の実行時トレース情報を生成する非常に便利な機能を備えています。これは、DBIプログラムの奇妙な問題を突き止めようとする場合に非常に時間を節約できます。さまざまな値を使用して、トレースレベルを設定できます。これらの値は、0〜4です。値0はトレースを無効にすることを意味し、4は完全なトレースを生成することを意味します。

補間されたステートメントは禁止されています

次のような補間ステートメントを使用しないことを強くお勧めします。

while ($first_name = <>) {
   my $sth = $dbh->prepare("SELECT *
                            FROM TEST_TABLE
                            WHERE FIRST_NAME = '$first_name'");
   $sth->execute();
   # and so on ...
}

したがって、代わりに*バインド値*を使用して動的SQLステートメントを準備する補間ステートメントを使用しないでください。

Perl-database

目次

Perlデータベースアクセス

DBIアプリケーションのアーキテクチャ

表記法と規則

データベース接続

INSERT操作

バインド値の使用

読み取り操作

バインド値の使用

更新操作

バインド値の使用

削除操作

do ステートメントの使用

コミット操作

ロールバック操作

トランザクション開始

AutoCommitオプション

自動エラー処理

データベースの切断

NULL値を使用する

その他のDBI関数

available_drivers

installed_drivers

data_sources

見積もり

すべてのハンドルに共通のメソッド

err

errstr

rows

トレース

補間されたステートメントは禁止されています