perlでMySQLデータベースへのデータ挿入、データ取得するためのコードをメモ
やりたいことと環境
perlを使って、MySQLサーバのデータベースにデータを挿入し、さらに挿入した値をデータベースから取得してターミナル上に表示します。MySQLサーバはLinux Mint 16上に構築し、以降に載せるperlコードも同じLinux Mint 16上で実行しました。
LAMPサーバのインストール
ここでは、準備としてMySQLサーバの構築とテーブルの作成手順をメモします。不要な方は飛ばして下さい。 MySQLサーバの構築とはいうものの、ここではLAMP環境の構築をもってMySQLサーバの構築とします。LAMP環境を構築するために以下のコマンドを実行します。
$ sudo apt-get install tasksel
taskselは、色々なサーバソフトウェアを簡単にインストールして構築するためのコマンドです。インストールしたら、以下で実行します。
$ sudo tasksel
以下のような画面が表示されるので、中からインストールしたいサーバソフトウェアを選択してチェックを入れ(十字キーでカーソル移動、スペースでチェックできます)、TABを押して「OK」します。私はLAMP以外にもSamabaとVirtualHostを入れたかったので、以下の画面ではチェックが入っています。LAMP環境だけを構築する場合は「LAMP Server」にだけチェックを入れればOKです。
なお、「OK」を押した後に以下のようなエラーが出る場合は、「sudo apt-get update」を実行してから再度上記のtaskselコマンドを実行してみて下さい。
$ tasksel aptitude failed (100)
インストール途中で、以下のようにMySQLのrootユーザに対するパスワードを聞いてくるので、適当に入力してOKします。ここで入力したパスワードは後で使用します。
パスワードを再入力するよう言われるので入力します。
以上でLAMP環境のインストールは完了です。これでMySQLサーバとして使えます。
MySQLサーバでデータベースを作成
続いてMySQLにログインしてデータベースを作成しておきます。以下のコマンドでMySQLにログインします。
$ mysql -u root -p
するとrootに対するパスワードを求められるので、インストール時に入力したパスワードを入力します。入力すると、以下のように表示されます。ここでSQLを実行していきます。
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.5.34-0ubuntu0.13.10.1 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
以下のコマンドを実行して新しくデータベースを作成します。以下では「TestDatabase」という名前のデータベースを作成しています。データベース作成が完了したら、「exit」コマンドでログアウトします。
mysql> create database TestDatabase;
mysql> exit
これでMySQLサーバの準備は完了です。後は、perlでMySQLを操作するためのモジュールをインストールします。
DBIモジュールのインストール
perlからMySQLを操作するために「DBI」というモジュールを使います。インストールは以下のコマンドを実行して完了です。
$ cpanm DBI
これで準備は完了です。
perlのコード
以下のコードは、上記の準備で作成したデータベース「TestDatabase」内に「testtable」というテーブルを作成し、配列に用意したデータをtesttableに挿入します。また、挿入したデータをtesttableから取得して、ターミナル上に表示します。自己責任での使用をお願い致します。
use strict;
use warnings;
use DBI; # DBIモジュール
my $user = 'root'; # MySQLのユーザ名
my $pass = 'root'; # MySQLのパスワード
my $database = 'TestDatabase'; # 使用するデータベース名
my $hostname = 'localhost'; # データベースサーバのアドレス
my $port = '3006'; # データベースサーバに接続する時のポート番号
my $table = 'testtable'; # 操作するテーブル名
# 挿入するデータ
my @id = (1,2,3);
my @url = ('http://test1.com/', 'http://test2.com/', 'http://test3.com/');
my @desc = ('testsite1', 'testsite2', 'testsite3');
# データベースからのデータ格納用配列
my @data = ();
# データベースへ接続
my $db = DBI->connect(
"DBI:mysql:$database:$hostname:$port",
$user,
$pass
) or die "cannot connect to MySWL: $DBI::errstr";
# $tableで指定したテーブルが無ければ作成
my $create_table = "create table if not exists ".$table."(id int, url text, description text)";
$db->do($create_table) || die $db->errstr;
# $tableからデータを削除
my $sql_delete = "truncate ".$table;
$db->do($sql_delete) || die $db->errstr;
for (my $num = 0; $num < @id; $num++){
# $tableにid, url, descをそれぞれ挿入
my $sql_insert = "insert into ".$table."(id, url, description) values(".$id[$num].",\'".$url[$num]."\',\'".$desc[$num]."\')";
$db->do($sql_insert) || die $db->errstr;
print "ID is ".$id[$num]."\n";
print "url is ".$url[$num]."\n";
print "description is ".$desc[$num]."\n";
}
my $sql = "select * from ".$table;
# $sqlの実行準備
my $sth = $db->prepare($sql);
# SQL実行
$sth->execute;
# fetchrow_arrayを使って行データを項目の配列として取り出す
while (my @tmp = $sth->fetchrow_array) {
push @data, [@tmp];
}
# SQL文を開放
$sth->finish;
# データベースから切断
$db->disconnect;
if ($@) {
print "Error : $@\n";
}
print "\nData from ".$table." \n";
foreach my $tmp (@data) {
print join(",", @{$tmp}), "\n";
}
exit;
DBIを使ったMySQLの基本的な操作方法
DBIでのMySQLの基本的な操作は、SQL文を用意して実行するだけです。ただ、参考にさせて頂いたサイトにも書いてありましたが、SQL文を実行する場合はSQL文によって以下の2通りがあります。
- select文はprepareメソッドでSQL文を準備してからexecuteメソッドで実行
- select文以外はdoメソッドで直接実行
createによるテーブル作成
28行目では「create」を使ったテーブル作成のためのSQL文を定義しています。ここでは、int型の「id」、text型の「url」、text型の「description」というデータ列を作成しています。テーブル作成のcreateコマンドは以下のように使用します。
mysql> create table テーブル名(データ列名1 データ型, データ列名2 データ型, データ列名3 データ型, ...);
28行目ではテーブル作成の条件として「if not exists」を指定しており、指定したテーブルが存在しなかった場合にのみ作成しています。そしてこのSQL文をdoメソッドによって実行しています。
$db->do($create_table) || die $db->errstr;
truncateによるデータ削除
32行目では「truncate」を使ったテーブル内のデータを全て削除するためのSQL文を定義しています。
mysql> truncate テーブル名;
なお、データの削除は「delete」によっても可能ですが、テーブル内の全てのデータを削除する場合はtruncateの方が処理が高速なようです。一方で、deleteは削除するデータの条件指定が可能です。例えば、以下のような感じです。
mysql> delete from テーブル名 where データ列名 between 値の範囲1 and 値の範囲2;
上記のdelete文によって、指定したデータ列名の中のレコードのうち、値が範囲1から範囲2の間にあるレコードが存在する行を削除します。truncateとdeleteの違いについては、以下のサイトに詳しく書いてあり、参考になりました。
TRUNCATE と DELETE の違い | SHIFT the Oracle
insertによるデータ挿入
39行目では「insert」を使ったデータ挿入のためのSQL文を定義しています。insertは以下のように使用します。
mysql> insert into テーブル名(データ列名1, データ列名2, description) values(データ列1の値, データ列2の値, データ列3の値);
selectによるデータ取得
47行目では「select」によるデータ取得のためのSQL文を定義しています。以下のselect文を実行することで、指定したテーブルに存在する全てのデータを取得できます。
mysql> select * from テーブル名
なお、取得するデータの条件指定をしたい場合は、deleteの場合と同様にwhereを使用できます。
mysql> select * from テーブル名 where データ列名 between 値の範囲1 and 値の範囲2;
そしてselect文はdoメソッドではなく、まず50行目のprepareメソッドで準備をして、その後で53行目のexecuteを使って実行します。また、select文の実行結果を56行目の「fetchrow_array」を使用して配列に格納しています。
実行結果
上記のperlコードを実行すると、ターミナル上に以下の出力が得られます。
ID is 1
url is http://test1.com/
description is testsite1
ID is 2
url is http://test2.com/
description is testsite2
ID is 3
url is http://test3.com/
description is testsite3
Data from testtable
1,http://test1.com/,testsite1
2,http://test2.com/,testsite2
3,http://test3.com/,testsite3
参考サイト様
- DBIモジュールについて参考になったサイト
- 配列やその他のリファレンスの書き方などについては以下のサイトが参考になりました。
DBIプログラミング SQLiteで学ぶデータベース操作の基礎 | サンプルコードによるPerl入門
配列のリファレンス | wikibooks
関連記事
- 公開日:2014/01/24 更新日:2014/01/24
perlでHTML5を解析して情報を抽出するコード(HTML::TagParser版)
HTML5で記述されたhtmlファイルから欲しい情報を抽出するためのperlコードをメモします。HTML::TagParserという便利なモジュールを使いました。
- 公開日:2014/01/21 更新日:2014/01/21
perlでHTMLを解析して欲しい情報を抽出するためのコードをメモ
HTMLを解析して特定のタグに囲まれている情報だけを抽出したり、リンクだけを抽出したりするコードを色々調べてperlで作成してみたのでメモしておきます。いわゆるスクレイピングするためのコードです。このコードでは、perlのHTML::TreeBuilderを使いました。