第4回　PerlでMySQL操作CGIを作ろう

鶴長 鎮一
MySQLユーザ会
2001/3/6

PerlによるDBプログラミング

■サンプルデータベースの準備

　これから紹介するサンプルスクリプトで使用するため、以下のようなデータベースとテーブルを作成します。

・“ATMARKIT”データベース中に“list”テーブルを作成

・テーブルの定義

フィールド  |  データ型   |   備考 id          |  int        |  主キー・オートインクリメント・空白不可 name        |  char(20)   |  空白不可 memo        |  char(240)  |

・ユーザー名：test、パスワード：test2001で参照可能に設定

　順を追っておさらいしましょう。まずデータベースを作成します。クリエイト権限を持っているのはデフォルトではスーパーユーザーだけになります。

# mysqladmin create ATMARKIT

　権限の問題でデータベースが作成されなかったとしてもエラーは表示されないので、ちゃんと作成されているかを“mysqlshow”コマンドで確認しておきましょう。確認できたところでテーブルの作成です。

# mysql ATMARKIT mysql> CREATE TABLE list (     ->   id int(10) NOT NULL auto_increment,     ->   name char(20) DEFAULT '' NOT NULL,     ->   memo char(240) DEFAULT '',     ->   PRIMARY KEY (id),     ->   KEY k_name (name)     -> ); Query OK, 0 rows affected (0.00 sec)

　いつまでもスーパーユーザーで作業をするのは危険です。ユーザー“test”にだけ、今回作成したデータベースを操作できるように権限を設定します。

mysql> GRANT Select,Update,Insert,Delete ON ATMARKIT.* TO 'test' IDENTIFIED BY 'test2001'; Query OK, 0 rows affected (0.00 sec)

　これで、ユーザー名とパスワードさえ合っていれば、どこのホストからでもATMARKITデータベースを操作できます。しかし、ユーザー名とパスワードが合っていても、操作が許されないホストが1つだけあります。それはなんとlocalhost自身です。

　これはMySQLのセキュリティを管理している特権データベース“mysql”中の“user”テーブル中に下記の項目が含まれるためです。

Host        | User ---------------------------------- localhost   | (なし)

　この場合、今回設定した

Host        | User ---------------------------------- %           | test

よりも先に評価されるため、“Access denied”となります。そこで、ローカルマシンからのアクセスを許可するように権限を追加します。

mysql> GRANT Select,Update,Insert,Delete ON ATMARKIT.* TO test@localhost IDENTIFIED BY 'test2001'; Query OK, 0 rows affected (0.00 sec)

　これでサンプルデータベースの作成は終了です。上記のように1行ずつ打ち込んでも構いませんが、ファイルに一連のSQL文を記述し、

# mysql ATMARKIT < ファイル名

で一気に流し込むこともできます（第3回「外部ファイルによるバッチ処理」参照）。そちらの方がミスもなく、確実に作業が進められますね。

■DB接続・登録・切断のコーディング

　準備ができたところで、データベース操作ができることを確認するために簡単なスクリプトを動かしてみましょう。まず、下記のPerlスクリプトを用意します。Emacsなどの適当なエディタで編集します。

1  use DBI; 2 3  $user = 'test'; 4  $passwd = 'test2001'; 5  $db = DBI->connect('DBI:mysql:ATMARKIT:localhost', $user, $passwd); 6  $sth = $db->prepare("INSERT INTO list VALUES (1,'1st','memo 1st')"); 7  $sth->execute; 8  $sth->finish; 9  $db->disconnect;

リスト1　接続・登録・切断を行うサンプルスクリプト（sample1.pl）。［ココ］をクリックすると、行番号のないスクリプトファイルをダウンロードできます

　このPerlスクリプトでは、以下のような処理を行っています。

・1行目 use DBI;
　ここでDBIの使用を宣言して、先ほどインストールしたDBIモジュールを利用できるようにします。Perlスクリプトでデータベースに関連する作業をするときには欠かせない1行です。

・5行目 $db = DBI->connect('DBI:mysql:データベース名:サーバ名', 'ユーザー名', 'パスワード');
　ここでデータベースハンドルオブジェクト($db)を生成します。

・6行目 $sth = $db->prepare("SQL文");
　5行目で生成したデータベースハンドルオブジェクトのprepareメソッドに、実行したいSQL文を引数に渡し、ステートメントハンドルオブジェクト($sth)を生成します。この後の処理は、この2つのオブジェクトに用意されたメソッドを呼び出すことで進めていきます。この時点ではまだデータベースにはSQL文は発行されていません。

・7行目 $sth->execute;
　ここで初めて、"INSERT INTO list VALUES (1,'1st','memo 1st')"がMySQLサーバに対して実行されます。

　これで処理が終了するわけではありません。このままではMySQLサーバに接続したままになり、サーバ側ではmysqlクライアントプロセスが起動したままになります。実際にはPerlスクリプトの終了とともに、$dbも$sthも開放されますが、開いたものは閉じる癖をつけておきましょう。

・8行目 $sth->finish;
　ステートメントハンドルオブジェクトの終了。

・9行目 $db->disconnect;
　データベースハンドルオブジェクトの切断。

　オブジェクトだとかメソッドだとか、「聞き慣れないなぁ」と思われている方は、上記のスクリプトをMySQLデータベースを操作する際のひな形として、どんなプログラムを作るときもこれを出発点にするようにとらえてください。ユーザー名、パスワード、ホスト名など、必要な個所を書き換えれば応用できますし、INSERT、DELETE、UPDATEも6行目の

$sth = $db->prepare("SQL文");

を書き換えることで対応できますね。

$ perl sample1.pl

で実行します。

■コラム　ハンドルオブジェクトとは

「DBIはデータベース非依存の機能や変数を提供する」と説明しました。その機能や変数を提供するための手段として「データベースハンドルオブジェクト」が用意されています。オブジェクトという言葉が示すとおり、データベースハンドルオブジェクトにはデータベース操作に関連したデータと操作が一体化されています。データベースハンドルオブジェクトに定義された操作を呼び出したい場合は下記のようにします。 データベースハンドルオブジェクト->操作("引数"); 　操作を呼び出す“->”記号を見ると、まさにオブジェクト指向構文であることが分かります。使い慣れないうちは独特の構文にとまどいますが、例えば“$db->disconnect;”を次のように読み直したらどうでしょう。 「$dbの中のdisconnectを呼び出す」 　 なら、 $sth = $db->prepare("INSERT INTO list VALUES (1,'1st','memo 1st')"); はどうなるでしょうか。 「$dbの中の操作prepare(引数....)を呼び出す。その戻り値として$sthを得る」 となります。得られた$sthはSQL文を発行したり、抽出結果を取得するオブジェクトとして使用します。このオブジェクトを「ステートメントハンドルオブジェクト」と呼びます。

■スクリプトでデータ抽出

　SELECT文の場合はどうでしょう。たとえSELECT文が実行できたとしても、抽出内容をどうやって取り出したらいいのでしょうか。

　順番に見ていきましょう。

1   use DBI; 2 3   $user = 'test'; 4   $passwd = 'test2001'; 5   $db = DBI->connect('DBI:mysql:ATMARKIT:localhost', $user, $passwd); 6   $sth = $db->prepare("SELECT id, name, memo FROM list"); 7   $sth->execute; 8   $num_rows = $sth->rows; 9   print "該当 $num_rows 件\n"; 10  for ($i=0; $i<$num_rows; $i++) { 11    @a = $sth->fetchrow_array; 12    print "id=$a[0], name=$a[1] memo=$a[2] \n"; 13  } 14  $sth->finish; 15  $db->disconnect;

リスト2　データ抽出を行うサンプルスクリプト（sample2.pl）。［ココ］をクリックすると、行番号のないスクリプトファイルをダウンロードできます

　7行目までは、"SELECT id, name, memo FROM list"を実行している以外は先ほどのリスト1とまったく同じです。

　その先、ステートメントハンドルオブジェクト（$sth）に用意されているメソッドを使用し、抽出結果を取得します。まず該当件数を得るために“rows”メソッドを呼び出します（8行目）。該当件数の回数だけforでループを回し、該当行を得ます（10〜13行目）。

　ループの中では該当行（@a）から、該当項目（$a[0],$a[1],$a[2]）を取り出します。$a[i]の順番は"SELECT id, name, memo ....."で指定した順になります。

　で、最後は礼儀正しく開いたものを閉じて終了です（14、15行目）。

$ perl sample2.pl

で実行します。

2/4

Index
快速MySQLでデータベースアプリ！
	DB用モジュールのインストール DBD/DBIモジュールとData-ShowTableのインストール MySQLモジュールのインストール
	PerlによるDBプログラミング DB接続・登録・切断のコーディング スクリプトでデータ抽出
	CGIでWeb-DBアプリ化
	次回予告
	Appendix

連載 快速MySQLでデータベースアプリ！

---

Linux Squareフォーラム データベース関連記事

	連載：快速MySQLでデータベースアプリ！（全11回） 軽快な動作で知られるRDBMS、MySQLでDBアプリの構築を行う。MySQLのインストールに始まり、PerlやRubyなどのスクリプトでデータベースを操作する方法までを完全解説
	連載：今から始める MySQL入門（連載中） 定番のLAMP（Linux＋Apache＋MySQL＋PHP）構成でWebアプリケーション開発に挑戦！ サンプルアプリの構築を進めながら、基礎知識や操作方法について詳しく解説する
	連載：Oracleマイスター養成講座（全6回） 本連載では、Oracleの管理・チューニング方法を紹介していく。これからOracleを始める人、そしてOracleをより深く理解したい人のための、一歩踏み込んだ実用講座
	連載：DB2マイスター養成講座（全7回） 本連載では、DB2 UDBの実践的な運用・管理方法を紹介していく。DB2を利用するうえで必要な知識を、実運用を前提にDB2のプロが解説
	特集：エンタープライズ市場に向かうMySQL 5.0［前編］ MySQL 5.0の新機能をアルファ版でチェック 1月に公開された5.0アルファ版は大幅に拡張されており、エンタープライズ市場への進出を予感させる
	特集：Linuxで動くリレーショナルデータベース・カタログ データベースサーバのOSとしてLinuxを採用するケースが増えている。Linuxで動作する7つの主なリレーショナルデータベースを紹介する。製品導入の際の参考にしてほしい
	more

MONOist組み込み開発フォーラムの中から、Linux関連記事を紹介します

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）