[精讚] [會員登入]
109

[PERL] 21- 使用資料庫

Perl 連Mysql

此文完整連結 http://n.sfs.tw/12104

複製連結 [PERL] 21- 使用資料庫@新精讚
(文章歡迎轉載,務必尊重版權註明連結來源)
2019-03-06 01:24:32 最後編修
2018-01-07 01:53:35 By 張○○
 

資料庫有很多套,基本上大同小異,此範例以MYSQL為例。

這個單元很不好寫,我寫了足足快一年,因為項目實在太多,只能挑撿一些我比較熟析或我覺得很重要的內容以饗讀者,其它比較不那麼重要或是進階的部分就留給有興趣的讀者自行研究,請參考最下面的參考資料。

安裝

使用mysql資料庫,在centos linux 7上直接用yum 安裝

yum install "perl(DBD::mysql)"

或是用 cpan 來安裝(Centos <7)

# cpan -i DBD::mysql

如果有CPAN的問題,可參考 [PERL] 使用CPAN安裝模組

這樣就能使用mysql的資料庫

 

資料庫連線

use DBD::mysql
my ($DB_NAME, $DB_IP, $DB_PORT, $DB_TYPE, $DB_USER, $DB_PWD )=
    ("dbname","127.0.0.1","3306","mysql", "username", "password"); 
# 給定連線字串
my $DSN = "DBI:$DB_TYPE:database=$DB_NAME;host=$DB_IP;port=$DB_PORT";
my $dbh = DBI->connect($DSN, $DB_USER, $DB_PWD) or die "無法連到資料庫 $DBI::errstr\n";

第1行 資料庫連線使用模組 DBD::mysql
第2-3行 給定連線參數
第6行 連線成功後取得參照變數$dbh,否則報錯。

指定編碼

這行很重要,尤其是有用到中文字時

my $sth = $dbh->prepare("set names utf8");
$sth->execute();

 

取得資料 SELECT

準備sql字串

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

第2行 叫用dbh(資料庫處理器中)的prepare方法產生 $sth,$sth是語法處理器物件[3]
第3行 叫用execute方法後,資料庫的內容就取回到$sth的物件中(記憶體)。

 

準備具有條件的sql字串

sql字串果有帶條件時,寫成如下

$sql="select * from sometable where name like ? and age=?";
my $sth = $dbh->prepare($sql); 

第1行 使用問號'?' 作為變數放置的位置。

給定變數值有兩個方法,一個是叫用 bind_param ,另一個是叫用 execute時帶入。

$sth->bind_param(1, 'john%'); #第1個問號
$sth->bind_param(2, 30); #第2個問號

或是在execute代入即可

$sth->execute("john%",30);

因為有2個問號,在執行execute時要帶相對應的參數,所以依次序放入變數的值。

由於PERL優異的彈性,你也可以放入一個清單或是陣列

@p = ("john%",30);
$sth->execute(@p);

有趣的是,就算你不放入任何變數也不會出錯,換句話說,不會產生錯誤的sql 字串,但是結果可能不是你要的。

當然,你也可以自己組合成字串,例如:

$sql="select * from sometable where name like 'john%' and age=30";

在 $sth叫用execute方法後,資料庫的內容就取回到$sth的物件中(記憶體),接下來使用幾種方法讀取或使用這些資料。

傾印取回的資料

當要查看執行結果時,可以使用下面的方法一次傾印全部的資料

DBI::dump_results($sth);

以下範例對上面說明的內容作個整合,並傾印出來:

use DBD::mysql;

my ($DB_NAME, $DB_IP, $DB_PORT, $DB_TYPE, $DB_USER, $DB_PWD )=
    ("ddns","127.0.0.1","3306","mysql", "ddns", "1qaz2wsx");
my $DSN = "DBI:$DB_TYPE:database=$DB_NAME;host=$DB_IP;port=$DB_PORT";
my $dbh = DBI->connect($DSN, $DB_USER, $DB_PWD) or die "無法連到資料庫 $DBI::errstr\n";
my $sql = "SELECT * from `unit` where `uid`=? or `uid`=?";
my $sth = $dbh->prepare($sql);
@p=(440,444)
$sth->execute(@p);
DBI::dump_results($sth);

 

逐筆取回資料

接下來要使用$sth提供的方法,取回在記憶體中的資料。

主要介紹下面幾種方法:

  fetchrow_hashref, fetchrow_arrayref, fetchrow_array, fetchall_arrayref, fetchall_hashref

* 上面是屬於STATEMENT HANDLE。另外還有一些方法如selectrow_array, selectrow_arrayref, selectrow_hashref, selectall_arrayref, selectall_array, selectall_hashref, selectcol_arrayref是屬於DATABASE HANDLE,相關的差異在O'Reilly 的「Programming the Perl DBI」專書有介紹[3],簡單的來說 STATEMENT HANDLE是DATABASE HANDLE的子層,透過STATEMENT叫用資料庫更安全。

 

取得一行使用 fetchrow_hashref()

my $ref = $sth->fetchrow_hashref();

fetchrow_hashref的方法是使用參照*的方式取回一行,指標一開始在資料的第一筆,每執行一次取回一行並把指標往下。

如果取不到資料,$ref為undef。你可以印出$sth->err 來看錯誤的原因。

* 如果對參照不熟析的話,可參考 [PERL] 17-參照 這篇。

 

是否有資料?

使用 if 直接來判斷是否有取回一行的資料:

if($ref){ ...執行操作... }

 

取得特定欄位的資料

要取得此行每個欄位的值,可叫用指標方法,例如取回欄位'zone'的資料

my $item = $ref->{'zone'};

這邊要注意的是,雖然Mysql的於資料庫、資料表有分大小寫,但是對於欄位是大小寫不分的(cf: 程式、函數、資料庫命名的風格 )

但是你取資料用的hash鍵值一定要和欄位名稱一致,也就是要分大小寫

 

使用迴圈取回全部欄位

使用迴圈來取回全部的資料,假設我要印出zone和count兩個欄位的資料,使用 while來傾印全部的 $sth資料。

while(my $ref = $sth->fetchrow_hashref()) { 
  print $ref->{'zone'}. " ". $ref->{'count'}. "\n";
}

 

結束取回資料

當已經不需要再從 $sth 物件取得任何資料時,可以叫用 finish 方法,他會釋放記憶體的占用,每次資料取完後務必加上這行。

$sth->finish;

 

關閉資料庫連結

當已經不需要再從資料庫做任何操作時,可以叫用 disconnect 方法,他會釋放占用的埠,每次結束後務必加上這行。

$dbh->disconnect;

 

取回全部使用 fetchall_hashref()

fetchall_hashref 會一次把資料放到變數中,來看以下範例:

my $ref = $sth->fetchall_hashref('uid');
while (($key, $value) = each($ref))
{
     print $key.", ".$value->{'prefix'}. "\n";
}

第1行 把所有的資料倒到 $ref變數中,這是一個雜湊的參照,fetchall_hashref 中代入的參數是這個雜湊的鍵值非常重要,你應該指定資料庫欄位中的「主鍵」或是「唯一鍵」,否則會因覆寫導致資料遺失的問題,此例中指定資料庫欄位uid設為鍵值。

第2-5行 印出鍵值和欄位`prefix`的內容

如此每一個資料項就是 $ref->{'鍵值'}->{'欄位名'},你還是要特別注意,前面提過的欄位名是有分大小寫

結果:

396, 24
1422, 25
482, 25
949, 24
499, 25
1151, 24
1204, 24

如果你不在乎欄位名,或是你已經知道既有的資料順序,那麼就使用array來替你取回資料

fetchrow_arrayref, fetchrow_array, fetchall_arrayref

 

取得一行使用 fetchrow_array()

類於於 fetchrow_hashref,取回一行為一個陣例,是個非常簡單的用法

@arr= $sth->fetchrow_array();

同樣的,也可以使用迴圈讀回全部資料

while(@arr=$sth->fetchrow_array()){
  print "@arr[0]\n";
}

 

取得一行使用 fetchrow_arrayref()

他的別名是 fetch(),直接叫用 $sth->fetch就好

由於他取回的是一個陣列參照,所以要寫成這樣,例如取回所有的第2項

while($arr=$sth->fetchrow_arrayref){
  print $arr->[1]. "\n";
}

 

給定欄位名稱

fetch或 fetchrow_arrayref常會搭配欄位名稱來使用,取回的欄位是不帶名稱的,可以使用 bind_column方法給定每個項目的名稱。

$sth->bind_columns(\$zone, \$ip, \$prefix);
while( $sth->fetchrow_arrayref()){
   print "$key $zone, $ip, $prefix\n";
}

第1行 給定陣例項目值設為變數 $zone, $ip, $prefix,因為是使用參照,所以前面有加一個反斜線'\',千萬別漏掉。

第3行 可以直接取用給定的變數了。

特別要注意的就是取回項目和給定名稱的數量要相同,否則會報警告,例如資料欄位有8個,但只給定3個變數。

DBD::mysql::st bind_columns failed: bind_columns called with 3 values but 8 are needed

 

取得主部使用 fetchall_arrayref()

類似於 fetchall_hashref

陣列也許很大,可以用中刮號[索引1,索引2]指定切片範圍,能以限定取回資料數量

$ref = $sth->fetchall_arrayref;  #全取
$ref = $sth->fetchall_arrayref( [2,4] );  #取3和5項
$ref = $sth->fetchall_arrayref( [2..5] );  #取3到6項
$ref = $sth->fetchall_arrayref( [-3,-1] );  #取倒數3和倒數1項
$ref = $sth->fetchall_arrayref( [1..4], 20); #取2到5項,20筆

 

使用迴圈印出

$ref = $sth->fetchall_arrayref();  #全取
while (($key, $value) = each($ref))
{
   print $key.", ".$value->[4]. "\n";
}

印出其中的鍵值和第5項,因為是陣列,鍵值就是索引 0, 1, 2, ....

如果有切片的話,鍵值以切片後為準

$ref = $sth->fetchall_arrayref([2..4],10);  #全取
while (($key, $value) = each($ref))
{
   print $key.", $value->[0]\n";
}

上面的例子中,切片是第3-5項目,所以新陣列只有3個項目10行

$value->[0]取回的第一個項目是切片的第1個項目,也就是原資料的第3個項目。

 

新增修改或刪除 INSERT/UPDATE/DELETE

無論是新增或修改或刪除,sql語法的產生還是調用 prepare 方法來完成,例如

$sql ="insert into tabel(col1, col2) values (?, ?)";
sth = $dbh->prepare($sql);

還是叫用 execute方法來執行。

sth->execute("value1", value2);

修改和刪除也是一樣。

但是prepare提供陣列的方法bind_param_array, execute_arra可以大量新增或修改資料,目前引用[2]的範例,有興趣的人再前往參考

$dbh->{RaiseError} = 1;        # save having to check each method call
$sth = $dbh->prepare("INSERT INTO staff (first_name, last_name, dept) VALUES(?, ?, ?)");
$sth->bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);
$sth->bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);
$sth->bind_param_array(3, "SALES"); # scalar will be reused for each row
$sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );

簡單說明一下:prepare有三個問號,要準備3個變數,第5行本來也應該準備3個項目,但傳入的是字串,所以會重覆使用,因此新增的三個欄目分別是:

John, Booth, SALES
Mary, Todd, SALES
Tim, Robinson, SALES

 

取回最後異動的id

Mysql新增或修改後,可取回最後異動的id(主鍵),叫用last_insert_id方法

$last = $sth->last_insert_id();

 

寫入空值

要寫入空值的話,只要把值指定為 undef即可,這就是 mysql的null值。

$sth->bind_param(1, undef);

 

參考資料

[1] https://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html

[2] https://metacpan.org/pod/DBI

[3] http://www.cs.unb.ca/~wdu/cs4025f02/DBI-Programming.htm

 

 

上一篇 20- 檔案處理
回到目錄 01-撰寫第一隻PERL程式
下一篇 22-日期和時間

你可能感興趣的文章

PERL一次讀取檔案所有內容的方法($/) perl可以使用slurp mode一次讀完檔案所有內容的方法及一些範例

[PERL] 命令列傳入參數 argv Perl 的外部傳參,是$ARGV[0]、$ARGV[1]、$ARGV[2].....

Perl 的列舉寫法(map list) 使用列舉的寫法來設定陣列,非常的強大

[PERL] 取得字串長度 使用length來取得字串長度

[PERL] 位元運算 Bitwise operation PERL的位元運算整理

[PERL] 09-迴圈 perl的迴圈

[PERL] 04-基本比對 perl的正規表達示基本比對

[PERL] 11- 雜湊的範例 Perl 的幾個雜湊範例

[PERL] 19-模組的安裝和維護 模組的安裝和維護

[PERL] 08-陣列 #2 --操作 更多perl陣列的操作,如拆開、黏合、取出、加入等等

我有話要說


限制:留言最高字數1000字,超過部分會被截掉。請注意:留言不可帶有網址,會被濾掉。 限制:未登入訪客,每則留言間隔需超過10分鐘,每日最多5則留言。

訪客留言

[無留言]

隨機好文

如何在linux下執行java 原生的java應用程式可以使用簡單的方法在console下面寫出來,適合作簡單的應用

PHP for sphinx 函式庫安裝 PECL/sphinx PHP>= 5.2.2 已經能原生支援 sphinx,可是預設的沒有裝,我們得自己裝才能用

[Windows7] 移除IE10及移除IE11 Windows7 不得已的情況要移除IE11或IE10怎麼做?

[PHP] 檢查檔案是否是圖檔 使用getimagesize函數檢查檔案是否是圖檔

設計的工作絕不接受比價 拿買陽春麵的價格想買牛肉麵,寧願倒掉也不賣