Perl-データベースアクセス

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

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

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

DBIは、バックエンドで使用可能なデータベースから独立しています。Oracle、MySQL、Informixなどを使用している場合でも、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のフィールドがあります。
TESTDBにアクセスするためのユーザーID「testuser」とパスワード「test123」が設定されています。
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に保存されます。それ以外の場合、$ dbhはundef値に設定され、$ DBI :: errstrはエラー文字列を返します。

INSERT操作

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

レコードの作成には次の手順があります-

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

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;

READ操作

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

必要な条件に基づいてSQLSELECTクエリを準備します。これはを使用して行われますprepare() API。
SQLクエリを実行して、データベースからすべての結果を選択します。これはを使用して行われますexecute() API。
すべての結果を1つずつ取得し、それらの結果を印刷します。これは、 fetchrow_array() API。
スタッテメントハンドルを解放します。これはを使用して行われます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操作

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

必要な条件に基づいてSQLクエリを準備します。これはを使用して行われますprepare() API。
SQLクエリを実行して、データベースからすべての結果を選択します。これはを使用して行われますexecute() API。
スタッテメントハンドルを解放します。これはを使用して行われますfinish() API。
すべてがうまくいけば、 commit この操作以外の場合はできます rollbackトランザクションを完了します。コミットおよびロールバック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;

バインド値の使用

$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操作

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

必要な条件に基づいてSQLクエリを準備します。これはを使用して行われますprepare() API。
SQLクエリを実行して、データベースから必要なレコードを削除します。これはを使用して行われますexecute() API。
スタッテメントハンドルを解放します。これはを使用して行われますfinish() API。
すべてがうまくいけば、 commit この操作以外の場合はできます rollback トランザクションを完了します。

$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成功した場合はtrue値を返し、失敗した場合はfalse値を返します。実際には、成功すると、影響を受ける行の数が返されます。この例では、実際に削除された行数を返します。

COMMIT操作

コミットは、変更を確定するためにデータベースに緑色の信号を与える操作であり、この操作の後、変更を元の位置に戻すことはできません。

これが呼び出す簡単な例です commit API。

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

ロールバック操作

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

これが呼び出す簡単な例です rollback API。

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

トランザクションを開始します

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

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

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

自動コミットオプション

トランザクションが単純な場合は、多くのコミットを発行する手間を省くことができます。接続呼び出しを行うときに、を指定できます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;

残念ながら、切断メソッドのトランザクション動作は未定義です。一部のデータベースシステム（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{} 引用符で囲まれた文字列をに返すために使用されます prepareAPI。ただし、WHERE句でNULL値を使用する場合は注意が必要です。考えてください-

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

undef（NULL）をプレースホルダーにバインドしても、年齢がNULLの行は選択されません。少なくともSQL標準に準拠するデータベースエンジンの場合。この理由については、データベースエンジンのSQLマニュアルまたはSQLブックを参照してください。NULLを明示的に選択するには、「WHERE ageISNULL」と言う必要があります。

一般的な問題は、実行時に定義または定義解除（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 :: *モジュールを検索して、使用可能なすべてのドライバのリストを返します。デフォルトでは、一部のドライバーが以前のディレクトリで同じ名前の他のドライバーによって非表示になっている場合、警告が表示されます。$ quickに真の値を渡すと、警告が抑制されます。

インストールされたドライバー

%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は「Don」「t」（外側の引用符を含む）を返します。quote（）メソッドが目的の文字列に評価されるSQL式を返すことは有効です。例-

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

may produce results which will be equivalent to

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

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

エラー

$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と同等です。

行

$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 ...
}

したがって、補間されたステートメントを使用せず、代わりに使用してください bind value 動的SQLステートメントを準備します。