Perlゼミ

  1. Perl
    2. ›
  2. データベース
    3. ›
  3. here

SQLiteで学ぶデータベース操作の基礎

Perlを使ってデータベースは初級者が学ぶには今までは少し敷居の高いものでした。まずSQLの実行環境を構築する必要がありすぐに勉強したいという需要を満たすことはできていませんでした。

ですが現在はこの状況は変わりました。現在ではActivePerlをダウンロードするとDBD::SQLiteと呼ばれるSQLiteを利用するためのモジュールがパッケージングされています。

またデータベースに接続するためのユーザから見たインターフェイス部分にあたるDBIというモジュールもパッケージングされています。

ですのでActivePerlの最新版をダウンロードするだけで何の環境構築も行わないでデータベースを学習し始めることができます。

DBD::SQLiteとDBIがインストールされていることを確認する

まずDBD::SQLiteとDBIがインストールされているかどうかを最初に確認してください。コマンドプロンプトからPerlのドキュメントを見るためのコマンド「perldoc」でこれらのモジュールがインストールされているかどうかを確認することができます。 

perldoc DBD::SQLite

perldoc DBI

もしこれらのモジュールがインストールされていなかった場合はppmと呼ばれるWindows用のパッケージマネージャを使用してDBD::SQLiteをインストールすることができます。また最新版のActivePerlをインストールするのも良いかもしれません。

新しくActivePerlをインストールする場合の手順はこちらになります。

ActivePerlのインストール

古いActivePerlがインストールされている場合は「コントロールパネル」→「プログラムの追加と削除」から古いActivePerlをアンインストールしてからインストールするか、Perlをインストールするディレクトリの名前を変更する必要があります。

最新のActivePerlをインストールできたら「DBD::SQLite」と「DBI」がインストールされていることを確認しましょう。

ppmでインストールする手順はこちらになります。 

ppm install DBD-SQLite

ppm install DBI

UnixやLinuxを使用している人cpanコマンドでDBD::SQLiteとDBIをインストールするだけです。(Windowsでもcpanコマンドを使用してインストールできるかもしれませんがDBD::SQLiteのコンパイルに失敗する可能性があります。) 

cpan DBD::SQLite

cpan DBI

これでデータベースを学習する準備は整いました。

データベースに接続する

では早速データベースに接続してみましょう。データベースに接続するにはDBIモジュールのconnectメソッドを使用します。connectの第1引数はデータソースと呼ばれるデータベースに接続するための情報を指定します。フォーマットは少し特殊ですが覚えてしまいましょう。

データソースの中で接続するデータベース名を指定することができます。SQLiteではデータベースは単純にファイルとして作成されるのでデータベース名として好きな名前を指定します。戻り値にはデータベースハンドルと呼ばれるデータベースと通信するためのオブジェクトが返されます。 
use DBI;
my $database = 'test.db';
my $data_source = "dbi:SQLite:dbname=$database";
my $dbh = DBI->connect($data_source);

これでカレントディレクトリにtest.dbというファイルができているはずです。

データベースを切断する

データベースの接続と対になるデータベースの切断の処理をここで覚えておきましょう。データベースを切断するにはdisconnectを使用します。 

$dbh->disconnect;

データベースへ接続する必要がなくなった場合は切断しておくのが賢明です。

SQLを実行する

SQLとはテーブルを作成したり、データベースにデータを登録したり、データを取り出したりするために使用する問い合わせ言語のことです。基本的なSQLについてはこれから解説します。ここではまずSQLを実行する方法を解説します。

SQLを実行する方法には2通りの方法があります。

  1. doメソッドを使用して直接実行する方法
  2. prepareメソッドでSQL文を準備してからexecuteメソッドで実行する方法

ふたつめの方法はSQLでselect文と呼ばれるものを実行する場合に使用します。ひとつめの方法はselect文以外のSQLを実行するために使用します。「selectはprepareとexecute」で「それ以外はdo」と覚えます。

テーブルを作成する

ではテーブルを作成するための一番簡単なSQLを作成してみましょう。テーブルを作成するには「create table」文を使用します。「create table」文では作成するテーブルの名前、テーブルに含むフィールド名を指定します。「SQLiteではフィールドのデータ型を必ずしも指定する必要はないのでここでは簡単のために型を省略します。

bookというテーブル名で、titleとauthorというフィールドを持つテーブルを作成するSQLです。 
create table book (
  title,
  author
);

ではdoメソッドを使用して実際にテーブルを作成してみましょう。 

my $create_table = <<'EOS';
create table book (
  title,
  author
)
EOS

$dbh->do($create_table);

これでbookという名前のテーブルが作成されました。

テーブルを削除する

テーブルを作成する方法を覚えたので反対の操作であるテーブルを削除する方法も覚えて起きましょう。テーブルを削除するには「drop table」文を使用します。 

drop table テーブル名

doを使用してテーブルを削除してみましょう。 

my $drop_table = "drop table book";
$dbh->do($drop_table);

レコードの追加 insert

テーブルにレコードを挿入するにはinsert文を使用します。 

insert into テーブル名 (フィールド名1, フィールド名2, ..) values (値1, 値2, 値3);

「title」が「Perl」で、「author」が「taro」のレコードをテーブルに追加するには次のようなSQLになります。データが文字列の場合はシングルクォートで囲みます(ですがこれはセキュリティ的にとても危険な方法なので回避策は後で解説します)。 

insert into book (title, author) values ('Perl', 'taro');

doを使ってinsert文を実行してみましょう。insertされたかどうかの確認はもう少し先のselect文を解説するまで待ってください。 

my $insert = "insert into book (title, author) values ('Perl', 'taro');";
$dbh->do($insert);

これで以下のようなレコードがテーブルに追加されます。 

+--------+----------+
|title   |author    | # ヘッダ(これはテーブルを作成したときにできる)
+--------+----------+
|Perl    |taro      | # レコード(insert文で追加されたレコード)
+--------+----------+

レコードの更新 update

テーブルにあるデータを更新するにはupdate文を使用します。一般的にはwhere区で検索条件を指定して更新対象を絞ります。select文のところで解説しますが、where区には複数の条件を指定することもできます。 

update テーブル名 set フィールド名1 = 値1, フィール名2 = 値2, .. where 検索するフィールド名 = 値

「author」が「taro」であるレコードのtitleの値をRubyに変更するSQLは次のようになります。 

update book set title = 'Ruby' where author = 'taro'

doを使ってupdate文を実行してみましょう。 

my $update = "update book set title = 'Ruby' where author = 'taro'";
$dbh->do($update);

先ほどのレコードは更新され次のようになります。 

+--------+----------+
|title   |author    | # ヘッダ(これはテーブルを作成したときにできる)
+--------+----------+
|Ruby    |taro      | # レコード(update文で更新されたレコード)
+--------+----------+

レコードの削除 delete

レコードを削除するにはdelete文を使用します。一般的にはwhere区で検索条件を指定して削除対象を絞ります。 

delete from テーブル名 where 検索するフィールド名 = 値

「author」が「taro」であるレコードを削除するSQLは次のようになります。 

delete from book where author = 'taro';

doを使ってdelete文を実行してみましょう。 

my $delete = "delete from book where author = 'taro';";
$dbh->do($delete);

レコードが削除されます。

レコードの取得 select

いよいよselect文です。ここまでは追加したレコードや削除したレコードの確認ができていませんでしたがselect文を覚えれば確認できるようになります。それでは一番簡単なselect文を書いてみましょう。指定したテーブルのすべての列を取得するには次のようにします。*はすべてのフィールドという意味です。 

select * from テーブル名;

bookのすべてレコードを取得するには次のようなSQLになります。 

select * from book;

select文の実行はdoではできません。select文を実行するにはSQL文をprepareというメソッドで準備してからexecuteで実行する必要があります。prepareメソッドは準備されたSQLを含んでいるステートメントハンドルと呼ばれるものを返します。このステートメントハンドルからexecuteを実行します。

では実際にselect文を実行してみましょう。 

my $select = "select * from book;";
my $sth = $dbh->prepare($select);
$sth->execute;

これでselect文を実行することができました。次の行う必要のあることはフェッチという操作によって実際に各行を取得することです。行をフェッチして配列に代入するにはステートメントハンドルからfetchrow_arrayというメソッドを使用します。 

$sth->fetchrow_array;

通常これはwhile文を使って、すべての行を取得するまで繰り返します。 

while (my @row = $sth->fetchrow_array) {
  ...
}

SQLiteではメモリ上にデータベースを作成することがで便利

SQLiteではメモリ上にデータべースを作成することができます。ファイル名を指定する必要はなく、データベースを切断するとテーブルは自動的に削除されますので、ちょっとした動作を確認したい場合にとても便利です。ここでの解説の大部分ではメモリ上にデータベースを作成して解説しようと思います。

メモリ上にデータベースを作成するにはデータソースのデータベース名に「:memory:」を指定します。 

my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:");

これでメモリ上のデータベースに接続することができます。

実際に動かせるinsert,update,delete,selectのサンプルコード

ここまでは解説ばかりでしたのでイメージをつかみにくかったのではないかと思います。ここでいくつかサンプルを記述します。実際にソースコードを実行してみてデータベースでのレコード操作になれてください。これが理解できればデータベースの基礎の基礎をマスターしたことになります。 

use strict;
use warnings;

use DBI;

# メモリ上のデータベースへ接続
my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:");

# テーブルの作成
my $create_table = <<'EOS';
create table book (
  title,
  author
)
EOS

$dbh->do($create_table);

# insert文の実行
$dbh->do("insert into book (title, author) values ('Perl', 'taro');");
$dbh->do("insert into book (title, author) values ('C++', 'taro');");
$dbh->do("insert into book (title, author) values ('Python', 'ken');");
$dbh->do("insert into book (title, author) values ('Java', 'mamoru');");

# update文の実行
$dbh->do("update book set title = 'Ruby' where author = 'mamoru'");

# delete文の実行
$dbh->do("delete from book where author = 'ken';");

# select文の実行
my $sth = $dbh->prepare("select * from book;");
$sth->execute;

# 各行のフェッチ
while (my @row = $sth->fetchrow_array) {
  # 各行を出力
  print join(', ', @row), "\n";
}

# データベースの切断
$dbh->disconnect;

次のような出力が得られます。 

Perl, taro
C++, taro
Ruby, mamoru

ユーザ名とパスワードを指定して接続する

SQLiteで作成したデータベースへの接続にはユーザ名やパスワードは必要ありません。ですがMySQLやPostgreSQLやOracleなどの標準的なデータベースではユーザ名とパスワードを接続のときに指定する必要があります。

DBIを使用してユーザ名とパスワードを指定して接続するには次のようにします。第2引数にユーザ名、第3引数にパスワードを指定します。 

my $dbh = DBI->connect($data_source, $user, $password);

DBIのオプションの標準的な設定

DBIではconnectメソッドの第4引数にDBIのオプションを指定することができます。 

DBI->connect($data_source, $user, $password, {opt1 => $val1, opt2 => $val2, ..});

DBIのデフォルトの動作はエラーが発生した場合に警告を標準エラー出力に出力するようになっています。

ですが警告ではなく、エラーメッセージを出力してプログラムを終了するほうがよりよいように思います。

警告を出力しないようにするには次のようにします。 

PrintError => 0

またエラーメッセージを出力してプログラムを終了するようにするには次のようにします。 

RaiseError => 1

またDBIにはAutoCommitと呼ばれるオプションがあります。これはコミットと呼ばれる処理を自動的に行うか行わないかを決めるオプションです。データベースのドライバによってデフォルトの値が異なる可能性がありますので明示的に指定しておいたほうが良いと思います。わたしの場合は自動的にコミットするようにしておいてトランザクションが必要になった場合にAutoCommitを0に設定します。

ですのでこのようなことを踏まえてデータベースに接続する場合は次のような記述にしておくのがよいかもしれません。 

my $dbh = DBI->connect(
  $data_source,
  $user,
  $password,
  {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
  }
);

ユーザ名もパスワードも必要なくオプションだけを指定したい場合は次のようにします。 

my $dbh = DBI->connect(
  $data_source,
  undef,
  undef,
  {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
  }
);

SQLiteのクライアントsqlite3のインストール

SQLiteをWindowsにインストールすれば、コマンドプロンプトから直接SQLを実行することができるようになります。プログラムからでもSQLはDBIを使用して実行できますが対話的に実行できる環境があったほうがSQLの学習には適しています。

WindowsにSQLiteをインストールする方法こちらで解説しています。

WindowsにSQLiteをインストールする

業務に役立つPerl

Perlテキスト処理のエッセンス

PerlでポータブルなLinuxファイル管理入門

ITエンジニアの求人情報など

　ITエンジニアの求人情報・Webサービス・ソフトウェア・スクールなどの情報。

システム開発のお問い合わせ

Perlクラブ