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です。
tasksel_select_server

なお、「OK」を押した後に以下のようなエラーが出る場合は、「sudo apt-get update」を実行してから再度上記のtaskselコマンドを実行してみて下さい。

$ tasksel aptitude failed (100)


インストール途中で、以下のようにMySQLのrootユーザに対するパスワードを聞いてくるので、適当に入力してOKします。ここで入力したパスワードは後で使用します。
mysql_root_pass

パスワードを再入力するよう言われるので入力します。
mysql_root_pass_retype

以上で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メソッドで直接実行

以降で上記コード内にある各SQL文と絡めてこの2通りについてメモします。

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


参考サイト様

SPONSORED LINK

この投稿へのコメント

コメントはありません。

コメントを残す

メールアドレスが公開されることはありません。

この投稿へのトラックバック

  1. PerlでMySQLを扱う | sleepingrabbit_2 said on 2017年6月26日 at 15:18

    […] […]

トラックバック URL