用Perl DBI连接MySQL数据库

Perl中一个最酷的模块就是Perl数据库接口(Perl Database Interface,简称DBI)。通过提供一系列在内部上能够转化成原始调用函数的功能,DBI模块为很多不同的数据库提供了一个统一的接口。由此,使用Perl就可以很容易地利用数据库和建立动态Web网页。

当前,MySQL是广泛用于Web网站开发的数据库,它是一种免费、开源的SQL操作。下面将讲述到如何实现Perl与MySQL相互通讯的操作,介绍由DBI提供的重要方法,在开发过程中讲述到一个简单的脚本模板。这一操作的前提是假设你的系统已经安装了MySQL和Perl。

下载和安装

开始,下载并安装Perl DBI模块和MySQL DBD。通过在Perl 命令行中运行以下命令即可完成安装过程:

perl> perl -MCPAN -e "install DBI"
perl> perl -MCPAN -e "install DBD::mysql"

注意:你可以手动下载和安装DBIMySQL DBD

现在Perl DBI和MySQL DBD应该安装在你的系统中。

然后,通过在MySQL用户端命令行输入以下命令,以建立一个用于SQL查询的表格。

mysql> CREATE TABLE users (id INT(4) PRIMARY KEY, username VARCHAR(25), country VARCHAR(2));

mysql> INSERT INTO users VALUES (1, ‘john’, ‘IN’), (2, ‘tom’, ‘US’), (3, ‘layla’, ‘US’);

一旦建立表格,继续使用DBI方法建立一个脚本模板。

下面是用Perl连接MySQL的基本框架:

#!/bin/perl
# load module
use DBI;

# connect
my $dbh = DBI->connect("DBI:mysql:database=db2;host=localhost", "Henry", "loveanny", {‘RaiseError’ => 1});

# execute INSERT query
my $rows = $dbh->do("INSERT INTO users (id, username, country) VALUES (4, ‘jay’, ‘CZ’)");
print "$rows row(s) affected ";

# execute SELECT query
my $sth = $dbh->prepare("SELECT username, country FROM users");
$sth->execute();

# iterate through resultset
# print values
while(my $ref = $sth->fetchrow_hashref()) {
    print "User: $ref-> ";
    print "Country: $ref-> ";
    print "———- ";
}

# clean up
$dbh->disconnect();

四个步骤

当使用Perl DBI执行一个SQL 查询时,请遵循四个简单的步骤:

  1. 开始时,通过调用connect() 方法初始化数据库的句柄。connect() 方法接收连接参数并作为字符串,包括数据库类型("mysql"),主机名称("localhost"),以及数据库名称("db2")。数据库名称("Henry")和密码("loveanny")作为第二和第三个变量提供给connect() 方法。
  2. 建立SQL查询字符串,并使用do()prepare()以及execute()方法执行查询语句。do()方法是针对于一次性使用的INSERT,UPDATE或者DELETE查询,但prepare()execute()方法针对的是SELECT查询。使用这些方法得到的对象将有所不同,这取决于查询的类型,查询结果是否成功也是如此。成功的SELECT查询将返回一个结果对象,成功的INSERT/UPDATE/DELETE 查询将返回一些相关的行。而不成功的查询将返回一个错误。
  3. 对于SELECT查询,结果对象将被进一步处理以提取数据。使用一个循环,fetchrow_hashref()方法将返回每一记录作为Perl的信号。
  4. 通过调用disconnect()方法结束会话。

PS: Perl DBI 入门

This document rewrite to Chinese by Jackie Yu. Last modified 20 May 1999
This document Copyright Jeffrey William Baker. Last modified 10 October 1998   

本文是以 Perl DBI Examples 为蓝本,配合 DBMaker 好学易用的特性,以及几个浅显易懂的例子,希望能够一步步地带领使用者学习 Perl DBI modules 存取 DBMaker 资料库的方法。而原作者撰写主要原动力是希望藉由这篇文章的问世,以降低 DBI mailing list 中一再重出现的 FAQ。

读完本文之後,我们就能学到利用 DBI 建立一个完备的资料库程式。 以下就先由 DBI 的基本功能开始,然後再逐步研究改善效能与可靠度技巧。

基本功能
DBI -Database independent interface for Perl.

Perl DBI 是 Perl 程式语言存取资料库时的标准应用程式介面 (API)。 DBI 中定义了一组函数、变数和一致性的资料库介面, 可满足与特定资料库无关的特性。

值得一提的,DBI 只是一个程式介面,利用一组十分轻巧的层面, 将你的应用程式与一个以上的资料库驱动程式紧密地结合在一起, DBI 的架构主要只是提供标准的介面,目的是为了方便使用者下达一些简单的指令, 就可以轻易地让那些驱动程式发挥作用
首先该做的事就是建构以及安装 DBI, 详细的步骤可参考 DBI INSTALL 文件中有说明. 然後再来就是建构出所需的资料库驱动程式,或是参考 DBD. 在每个 DBD 套件中都应该会有建构程序。 与其他 Perl 模组相较之下, 安装 DBI/DBD 步骤算是相当容易
localhost:~/DBI-1.08$ perl Makefile.PL && make
        && make test && make install
在 DBI 与 DBD 安装完成之後, 您可以执行下列指令阅读更多的资讯:
localhost:~$ perldoc DBI

建立连线
每一种资料库都有不同的连线方式, 若是想知道特殊用法, 请务必阅读 DBD 所提供的说明文件。 下面的例子是连线到 DBMaker 的基本方法.
use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:DBMaker:dbsample’,
                        ‘jerry’,
                        ‘jerrypassword’,
                      );
上述的 connect 函数用到了三个参数: 资料来源名称 (data source name, DSN), 使用者名称和密码。 其中 DSN 的格式为 dbi:DriverName:instance. 不过我们要如何知道连线成不成功呢? 首先可以查看 connect 的传回值,true 代表成功,false 就是代表失败。 其次,当有错误发生时, DBI 会把错误讯息存放在 package variable $DBI::errstr 之中。
use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:DBMaker:dbsample’,
                        ‘jerry’,
                        ‘jerrypassword’,
                      )
          || die "Database connection not made: $DBI::errstr";
$dbh->disconnect();
结束资料库连线请使用 disconnect() 函数, 正确地使用可避免错误讯息 "Database handle destroyed without explicit disconnect" 的发生.

选项
在连线资料库时 connect() 这个方法可以接受 hash 型态的选项,常用的选项包括了: AutoCommit,设为 true 表示资料库交易自动确认; RaiseError,告诉 DBI 在错误发生时触发例外 croak $DBI::errstr 而不只是仅传回错误代码;PrintError,让 DBI 以警告方式 warn $DBI::errstr 传回错误讯息.
在下一段程式中,是希望进行采交易处理的用法,设定成 AutoCommit off,RaiseError on,而让 PrintError 使用内定值 on.
use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:DBMaker:dbsample’,
                        ‘jerry’,
                        ‘jerrypassword’,
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      )
          || die "Database connection not made: $DBI::errstr";
$dbh->disconnect();
有一点要特别注意,如果资料库本身不支援交易处理的功能时,设定 AutoCommit off 会接收到错误发生的传回值.

下达 SQL
现在开始可以对我们的资料库做一些有意义的事了. 下达至资料库的 SQL 述分成两类. 一是查询指令,预期会有数笔资料传回,例如 SELECT,这一类的指令我们会使用 prepare 方法. 另一类的指令,如 CREATE 和 DELETE,我们会使用 do 方法. 我们先看看後者如何使用.
这一段程式示在资料库中建立一个员工资料表格的方法.
use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:DBMaker:dbsample’,
                        ‘jerry’,
                        ‘jerrypassword’,
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      )
          || die "Database connection not made: $DBI::errstr";
my $sql = qq{ CREATE TABLE employees ( id INTEGER NOT NULL,
                                       name VARCHAR(128),
                                       title VARCHAR(128),
                                       phone CHAR(10)
                                     ) };
$dbh->do( $sql );
$dbh->commit();
$dbh->disconnect();

进阶功能
我们已经学会了连线资料库,错误侦测,以及下达简单的 SQL 述的方法. 是该学一些更有用的程式语法的时候了.
SELECT 述
在 SQL 的述中,最常使用的指令莫过於 SELECT 述. 为了使用 SELECT,我们应该先 prepare 这一段述,然後进行 execute 动作. 在下面的程式片段中,我们都会使用 statement handle $sth 有存取 SELECT 的结果.
use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:DBMaker:dbsample’,
                        ‘jerry’,
                        ‘jeerrypassword’,
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      )
          || die "Database connection not made: $DBI::errstr";
my $sql = qq{ SELECT * FROM employees };
my $sth = $dbh->prepare( $sql );
$sth->execute();
$dbh->disconnect();
上述程式要求 DBMaker 资料库为查询指令先准备好执行计画,之後再执行该查询指令. 到目前为止还没有任何一笔记录传回. 稍後我们会使用 bind_columns 的技术以取得资料库输出的记录. bind_columns 分别将每个输出栏位结到一个 scalar reference. 一旦呼叫到 fetch 时,这些 scalars 就会填入这资料库传回的值.
use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:DBMaker:dbsample’,
                        ‘jerry’,
                        ‘jerrypassword’,
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      )
          || die "Database connection not made: $DBI::errstr";
my $sql = qq{ SELECT id, name, title, phone FROM employees };
my $sth = $dbh->prepare( $sql );
$sth->execute();
my( $id, $name, $title, $phone );
$sth->bind_columns( undef, $id, $name, $title, $phone );
while( $sth->fetch() ) {
print "$name, $title, $phone
";
}
$sth->finish();
$dbh->disconnect();
这倒是一个列印出全公司电话连络簿的好方法,尤其是 WHERE 区块的使用让我们轻易地取出所有的资料输出! 下面会利用到 bind_param 先将 SQL 述做一次 prepare,就能够高速地执行许多次.
use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:DBMaker:dbsample’,
                        ‘jerry’,
                        ‘jerrypassword’,
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      )
          || die "Database connection not made: $DBI::errstr";
my @names = ( "Larry%", "Tim%", "Randal%", "Doug%" );
my $sql = qq{ SELECT id, name, title, phone FROM employees WHERE name LIKE ? };
my $sth = $dbh->prepare( $sql );
for( @names ) {
$sth->bind_param( 1, $_);
$sth->execute();
my( $id, $name, $title, $phone );
$sth->bind_columns( undef, $id, $name, $title, $phone );
while( $sth->fetch() ) {
    print "$name, $title, $phone
";
}
}
$sth->finish();
$dbh->disconnect();

高等技巧
交易机制
到目前为止,我们的动作都还不需要使用到交易机制的功能,不过当下达 UPDATE 或是 DELETE 述时,我们就会希望使用到它了. 根据 DBI 文件指出,如果想作一套稳固的交易机制,就应该配合 eval{…} 区块来拦截错误的发生,最後再使用 commit 或 rollback 来完成整个交易程序. 这就是我们以下所要谈的内容.
本程式示保四笔记录写入资料库的方法.
use strict;
use DBI qw(:sql_types);
my $dbh = DBI->connect( ‘dbi:DBMaker:dbsample’,
                        ‘jerry’,
                        ‘jerrypassword’,
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      )
          || die "Database connection not made: $DBI::errstr";
my @records = (
                [ 0, "Larry Wall",      "Perl Author", "555-0101" ],
                [ 1, "Tim Bunce",       "DBI Author",   "555-0202" ],
                [ 2, "Randal Schwartz", "Guy at Large", "555-0303" ],
                [ 3, "Doug MacEachern", "Apache Man",   "555-0404" ]
              );
my $sql = qq{ INSERT INTO employees VALUES ( ?, ?, ?, ? ) };
my $sth = $dbh->prepare( $sql );
for( @records ) {
eval {
    $sth->bind_param( 1, @$_->[0]);
    $sth->bind_param( 2, @$_->[1]);
    $sth->bind_param( 3, @$_->[2]);
    $sth->bind_param( 4, @$_->[3]);
    $sth->execute();
    $dbh->commit();
};
if( $@ ) {
    warn "Database error: $DBI::errstr
";
    $dbh->rollback(); #just die if rollback is failing
}
}
$sth->finish();
$dbh->disconnect();

注意事项
关於 finish 的呼叫在我们的例子并不是必要的. 这个函数适用於 statement handle 完成时所使用。

永远要加上 use strict。 仔细阅读 Perl DBI manual 以深入解 DBI 架构。

其他资讯
DBI – A Database Interface Module for Perl 5
DBI at Hermetica
DBI mailing list information
DBI mailing list archives
Persistent connections with mod_perl

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>