PostgreSQL - Perl

PostgreSQL - 现代 Perl 接口

介绍与背景

Perl 可以使用标准的 DBI（数据库独立接口）模块和特定的驱动 DBD::Pg 连接到 PostgreSQL。尽管 Perl 是一种健壮且功能强大的语言，但值得注意的是，对于新的 Web 应用后端，诸如 Python (psycopg)、Node.js (node-postgres)、Go (pgx) 和 Java (JDBC) 等生态系统在行业中目前更为流行。

本指南将演示从 Perl 与 PostgreSQL 交互的现代且安全的方法。

使用 cpanm 安装

推荐使用像 cpanm 这样的 CPAN 客户端来安装 Perl 模块。手动下载和安装已经过时，不推荐使用。

首先，如果您没有 cpanm，请安装它。然后，安装所需的数据库模块：

# 安装 cpanminus (如果您没有)
# curl -L https://cpanmin.us | perl - --sudo App::cpanminus

# 安装 DBI 和 PostgreSQL 驱动模块
sudo cpanm DBI DBD::Pg

安全的数据库交互：占位符

严重安全警告： 永远不要将变量直接嵌入 SQL 字符串中。这会产生一个名为 SQL 注入的漏洞，可能导致数据被窃取或破坏。始终使用 prepare 和 execute 方法配合占位符（?）。

连接到数据库

以下脚本展示了一种现代的连接方式。我们启用了 RaiseError 和 PrintError，以确保任何数据库错误都会自动停止脚本并报告问题。

#!/usr/bin/env perl
use strict;
use warnings;
use v5.10;

use DBI;

my $dsn = "DBI:Pg:dbname=mydb;host=localhost;port=5432";
my $user = "myuser";
my $password = "mypassword";

my $dbh = DBI->connect($dsn, $user, $password, {
    RaiseError => 1,
    PrintError => 0, # RaiseError 已经足够
    AutoCommit => 1, # 大多数情况下推荐
}) or die "Cannot connect to database: $DBI::errstr";

say "Database connection successful.";

$dbh->disconnect();

CRUD 操作示例

让我们演示如何创建表，然后使用安全、现代的实践执行创建（Create）、读取（Read）、更新（Update）和删除（Delete）操作。

1. 创建表

my $create_sql = q{
    CREATE TABLE IF NOT EXISTS employees (
        id      SERIAL PRIMARY KEY,
        name    TEXT NOT NULL,
        role    TEXT NOT NULL,
        salary  INTEGER
    );
};
$dbh->do($create_sql); # 对于不带参数的语句，'do' 是安全的。
say "Table 'employees' is ready.";

2. INSERT 操作（创建）

请注意 ? 占位符的使用，以及将值传递给 execute 方法。

my $insert_sql = "INSERT INTO employees (name, role, salary) VALUES (?, ?, ?)";
my $sth = $dbh->prepare($insert_sql);

$sth->execute('Alice', 'Engineer', 90000);
$sth->execute('Bob', 'Marketing', 65000);

say "Two records inserted successfully.";

3. SELECT 操作（读取）

使用带有 Slice 的 selectall_arrayref 会给我们一个哈希引用数组，这非常易于处理。

my $select_sql = "SELECT id, name, role, salary FROM employees WHERE salary > ?";
my $employees = $dbh->selectall_arrayref($select_sql, { Slice => {} }, 70000);

say "Employees earning more than 70000:";
foreach my $emp (@$employees) {
    say " - ID: $emp->{id}, Name: $emp->{name}, Role: $emp->{role}";
}

4. UPDATE 操作

my $update_sql = "UPDATE employees SET salary = ? WHERE name = ?";
my $rows_affected = $dbh->do($update_sql, undef, 95000, 'Alice');

say "Updated $rows_affected row(s). Alice got a raise!";

5. DELETE 操作

my $delete_sql = "DELETE FROM employees WHERE name = ?";
$rows_affected = $dbh->do($delete_sql, undef, 'Bob');

say "Deleted $rows_affected row(s). Goodbye, Bob.";

请记住，总是在脚本结束时断开连接：$dbh->disconnect();