Perl-DBI-模块-Notes

Perldoc 文档

描述

DBI 只是一个接口.

结构图:

一些惯例:

还有 $dsn（Data Source Name，数据源名称）用于指定连接到数据库时使用的数据源信息。数据源信息通常包括以下信息：

• 数据库类型（例如 mysql、SQLite 等）
• 数据库主机名或 IP 地址
• 数据库端口号
• 数据库名称（也称为“数据库实例”）
• 其他可选参数，例如用户名和密码

$dsn 的格式取决于使用的数据库类型.

连接到 MySQL 数据库的 $dsn 可以采用以下格式:

my $dsn = "DBI:mysql:database=mydb;host=localhost;port=3306";

Perl 会自动关闭 database and statement handle.

一般只在程序开头连接数据库, 并在程序末尾 disconnect.

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

中的 AutoCommit 建议写出.

可以将 SQL 语句放在 prepare 中, 然后用 execute 执行:

$sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");
$sth->execute( $baz );

while ( @row = $sth->fetchrow_array ) {
    print "@row\n";
}

这里的 ? 应该会被传入的参数替换掉.

do() 是 prepare() 和 execute() 的包装.

在 AutoCommit 关闭时, 提交对数据库的改变用:

$dbh->commit;

可以用:

$dbh->rollback;

来 undo 改变.

在完成所有处理后, 用 disconnect 来断开连接:

$dbh->disconnect;

总的接口规则和注意事项 General Interface Rules & Caveats

一般, 多条 SQL 语句不会放在单个 statement handle ($sth) 中.

DBI 获取的数据 fetch 过一次后就没了 (应该是 pop 和 push 这类).

私有的 SQL 函数 (可能是自己写的脚本) 可以用 func() 来调用.

占位符和绑定值 Placeholders and Bind Values

如:

INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?) 

中的 ?, 实际值和占位符的联系就叫做 binding

大部分时候, placeholders 只有做 single scalar values 时才管用, 如:

SELECT name, age FROM ?

则可能失败.

Null values

可以通过传递 undef 来表示传递 Null value.

如:

$sth = $dbh->prepare(qq{
    INSERT INTO people (fullname, age) VALUES (?, ?)
});
$sth->execute("Joe Bloggs", undef);

看到 495 行.

Perl.com Tutorial

参考文章

当向 DBI 查询时, 其将查询传递给合适的 DBD 模块来处理, 当 DBD 得到结果后 ， 其又会传回给 DBI 模块.

结构图:

(DBD::CSV 也是一个数据库)

$sth->fetchrow_array() 应该一次是获取一行数据. 当没有 records 可以获取时, 其返回空列表.

$sth->rows 应该是获取一次查询中的中行数.

if ($sth->rows == 0) {
    print "No names matched `$lastname'.\n\n";
}

DBI->connect 失败时返回 undef.

可以用 DBI->errstr 来返回错误信息.

如:

my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = ?')
               or die "Couldn't prepare statement: " . $dbh->errstr;

$dbh->prepare 失败的返回值也是 undef.

$sth->finish 能够 reinitialize the handle, 然后就可以再次查询. 毕竟如:

my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = ?')
               or die "Couldn't prepare statement: " . $dbh->errstr;

在一次查询中, ? 已经被替换掉了 (但感觉不是这个原因)

注意对查询的缓存:

{  
    my $sth;

    sub age_by_id {
        # Arguments: database handle, person ID number
        my ($dbh, $id) = @_;

        if (! defined $sth) {
            $sth = $dbh->prepare('SELECT age FROM people WHERE id = ?')
            or die "Couldn't prepare statement: " . $dbh->errstr;
        }

        $sth->execute($id) 
                or die "Couldn't execute statement: " . $sth->errstr;

        my ($age) = $sth->fetchrow_array();
        return $age;
    }
}

也就是只 prepare 一次, 然后多次使用, prepare 的开销一般比较大.

或者直接用 prepare_cached, 其会检查这一次查询是不是和上一次一样:

 sub age_by_id {
    # Arguments: database handle, person ID number
    my ($dbh, $id) = @_;
    my $sth = $dbh->prepare_cached('SELECT age FROM people WHERE id = ?')
        or die "Couldn't prepare statement: " . $dbh->errstr;

    $sth->execute($id) 
                or die "Couldn't execute statement: " . $sth->errstr;

    my ($age) = $sth->fetchrow_array();
    return $age;
}

一个和 transaction 相关的示例:

sub new_employee {
    # Arguments: database handle; first and last names of new employee;
    # department ID number for new employee's work assignment
    my ($dbh, $first, $last, $department) = @_;
    my ($insert_handle, $update_handle);

    my $insert_handle = 
    $dbh->prepare_cached('INSERT INTO employees VALUES (?,?,?)'); 
    my $update_handle = 
    $dbh->prepare_cached('UPDATE departments 
                            SET num_members = num_members + 1
                        WHERE id = ?');

    die "Couldn't prepare queries; aborting"
            unless defined $insert_handle && defined $update_handle;

    my $success = 1;
    $success &&= $insert_handle->execute($first, $last, $department);
    $success &&= $update_handle->execute($department);

    my $result = ($success ? $dbh->commit : $dbh->rollback);
    unless ($result) { 
        die "Couldn't finish transaction: " . $dbh->errstr 
    }
    return $success;
}

do 是 prepare, execute 和 finish 这三个操作的缩写, 但其用于没有返回数据的操作, 其返回值为改变的行数, 似乎不会获取数据库中的行. (可能 finish 会刷新获取的行)

书籍 Programming the Perl DBI

网页版

技巧积累

以时间顺序取出数据

需要在插入数据时记录数据的时间戳. 也就是创建表时有一个 TIMESTAMP 字段, 如:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect("dbi:mysql:dbname=test;host=localhost", "user", "password")
        or die "Couldn't connect to database: " . DBI->errstr;

# 创建表
$dbh->do(qq{
    CREATE TABLE IF NOT EXISTS mytable (
        id INT(11) NOT NULL AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL,
        value TEXT NOT NULL,
        ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    )
});

# 插入数据
$dbh->do(qq{
    INSERT INTO mytable (name, value)
    VALUES (?, ?)
}, undef, "data1", "value1");

$dbh->do(qq{
    INSERT INTO mytable (name, value)
    VALUES (?, ?)
}, undef, "data2", "value2");

# 查询数据
my $sth = $dbh->prepare(qq{
    SELECT name, value, ts FROM mytable
    ORDER BY ts ASC
});
$sth->execute();

while (my($name, $value, $ts) = $sth->fetchrow_array()) {
    print "$name: $value ($ts)\n";
}

$sth->finish();
$dbh->disconnect();