PostgreSQL Perl接口

安装

PostgreSQL可以通过使用Perl DBI模块与Perl集成，该模块是Perl编程语言的数据库访问模块。它定义了一组方法、变量和约定，提供了一个标准的数据库接口。

以下是在Linux/Unix机器上安装DBI模块的简单步骤：

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625
$ perl Makefile.PL
$ make
$ make install

如果您需要为DBI安装SQLite驱动程序，则可以按照以下步骤进行安装：

$ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz
$ tar xvfz DBD-Pg-2.19.3.tar.gz
$ cd DBD-Pg-2.19.3
$ perl Makefile.PL
$ make
$ make install

在您开始使用Perl PostgreSQL接口之前，请在您的PostgreSQL安装目录中找到 pg_hba.conf 文件，并添加以下行 –

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

在以下情况下，您可以使用以下命令启动/重新启动Postgres服务器，即在服务器未运行的情况下：

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

DBI接口APIs

以下是重要的DBI例程，可以满足您在Perl程序中使用SQLite数据库的需求。如果您正在寻找更复杂的应用程序，可以查阅Perl DBI官方文档。

序号	API & 描述
1	`DBI→connect($data_source, "userid", "password", \%attr)` 建立到请求的`$data_source`的数据库连接或会话。如果连接成功，返回数据库句柄对象。数据源的形式如下： `DBI:Pg:dbname=$database;host=127.0.0.1;port=5432` Pg是PostgreSQL驱动程序名称，testdb是数据库名称。
2	`$dbh→do($sql)` 此例程准备并执行一条SQL语句。返回受影响的行数或错误时返回undef。返回-1表示不知道、不适用或不可用。这里`$dbh`是通过DBI→connect()调用返回的句柄。
3	`$dbh→prepare($sql)` 此例程为后续由数据库引擎执行的语句做准备，并返回对语句句柄对象的引用。
4	`$sth→execute()` 此例程执行必要的处理以执行准备好的语句。如果发生错误，返回undef。无论受影响的行数是多少，成功的执行始终返回true。这里$sth是通过`$dbh→prepare($sql)`调用返回的语句句柄。
5	`$sth→fetchrow_array()` 此例程取得下一行数据并将其作为包含字段值的列表返回。空字段以列表中的undef值返回。
6	`$DBI::err` 这相当于`$h→err`，`$h`可以是任何句柄类型，比如`$dbh`、`$sth`或`$drh`。它返回上次调用的驱动方法的本机数据库引擎错误代码。
7	`$DBI::errstr` 这相当于`$h→errstr`，`$h`可以是任何句柄类型，比如`$dbh`、`$sth`或`$drh`。它返回上次调用的DBI方法的本机数据库引擎错误消息。
8	`$dbh- >disconnect()` 此例程关闭先前通过DBI→connect()调用打开的数据库连接。

连接到数据库

以下Perl代码展示了如何连接到现有数据库。如果数据库不存在，则会被创建，最后将返回一个数据库对象。

#!/usr/bin/perl

use DBI;
use strict;

my driver  = "Pg"; 
mydatabase = "testdb";
my dsn = "DBI:driver:dbname = database;host = 127.0.0.1;port = 5432";
myuserid = "postgres";
my password = "pass123";
mydbh = DBI->connect(dsn,userid, password, { RaiseError => 1 })   or dieDBI::errstr;

print "Opened database successfully\n";

现在，让我们运行上面给出的程序来打开我们的数据库 testdb ；如果成功打开数据库，它将显示以下信息 –

Open database successfully

创建表

以下Perl程序将用于在先前创建的数据库中创建表 –

#!/usr/bin/perl

use DBI;
use strict;

my driver   = "Pg"; 
mydatabase = "testdb";
my dsn = "DBI:driver:dbname=database;host=127.0.0.1;port=5432";
myuserid = "postgres";
my password = "pass123";
mydbh = DBI->connect(dsn,userid, password, { RaiseError => 1 })
   or dieDBI::errstr;
print "Opened database successfully\n";

my stmt = qq(CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL););
myrv = dbh->do(stmt);
if(rv<0) {
   printDBI::errstr;
} else {
   print "Table created successfully\n";
}
$dbh->disconnect();

当执行上述给定程序时，它将在您的 testdb 中创建 COMPANY 表，并显示以下消息。

Opened database successfully
Table created successfully

插入操作

以下Perl程序展示了如何向我们在上面的示例中创建的COMPANY表中插入记录：

#!/usr/bin/perl

use DBI;
use strict;

my driver   = "Pg"; 
mydatabase = "testdb";
my dsn = "DBI:driver:dbname = database;host = 127.0.0.1;port = 5432";
myuserid = "postgres";
my password = "pass123";
mydbh = DBI->connect(dsn,userid, password, { RaiseError => 1 })
   or dieDBI::errstr;
print "Opened database successfully\n";

my stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (1, 'Paul', 32, 'California', 20000.00 ));
myrv = dbh->do(stmt) or die DBI::errstr;stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
rv =dbh->do(stmt) or dieDBI::errstr;

stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ));rv = dbh->do(stmt) or die DBI::errstr;stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
rv =dbh->do(stmt) or dieDBI::errstr;

print "Records created successfully\n";
$dbh->disconnect();

当执行以上给定的程序时，它将在”COMPANY”表中创建给定的记录，并显示以下两行-

Opened database successfully
Records created successfully

SELECT操作

下面的Perl程序展示了我们如何从上面的示例中创建的COMPANY表中获取和显示记录：

#!/usr/bin/perl

use DBI;
use strict;

my driver   = "Pg"; 
mydatabase = "testdb";
my dsn = "DBI:driver:dbname = database;host = 127.0.0.1;port = 5432";
myuserid = "postgres";
my password = "pass123";
mydbh = DBI->connect(dsn,userid, password, { RaiseError => 1 })
   or dieDBI::errstr;
print "Opened database successfully\n";

my stmt = qq(SELECT id, name, address, salary  from COMPANY;);
mysth = dbh->prepare(stmt );
my rv =sth->execute() or die DBI::errstr;
if(rv < 0) {
   print DBI::errstr;
}
while(my @row =sth->fetchrow_array()) {
      print "ID = ". row[0] . "\n";
      print "NAME = ".row[1] ."\n";
      print "ADDRESS = ". row[2] ."\n";
      print "SALARY =  ".row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

当执行上述给定程序时，它将产生以下结果 –

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

更新操作

以下Perl代码显示了我们如何使用UPDATE语句来更新任何记录，然后从我们的COMPANY表中获取和显示更新后的记录-

#!/usr/bin/perl

use DBI;
use strict;

my driver   = "Pg"; 
mydatabase = "testdb";
my dsn = "DBI:driver:dbname = database;host = 127.0.0.1;port = 5432";
myuserid = "postgres";
my password = "pass123";
mydbh = DBI->connect(dsn,userid, password, { RaiseError => 1 })
   or dieDBI::errstr;
print "Opened database successfully\n";

my stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
myrv = dbh->do(stmt) or die DBI::errstr;
if(rv < 0 ) {
   print DBI::errstr;
}else{
   print "Total number of rows updated :rv\n";
}
stmt = qq(SELECT id, name, address, salary  from COMPANY;);
mysth = dbh->prepare(stmt );
rv =sth->execute() or die DBI::errstr;
if(rv < 0) {
   print DBI::errstr;
}
while(my @row =sth->fetchrow_array()) {
      print "ID = ". row[0] . "\n";
      print "NAME = ".row[1] ."\n";
      print "ADDRESS = ". row[2] ."\n";
      print "SALARY =  ".row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

当上述给定的程序被执行时，它将产生以下结果 –

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

DELETE操作

以下Perl代码展示了我们如何使用DELETE语句删除任意记录，然后从我们的COMPANY表中获取并显示剩余的记录。

#!/usr/bin/perl

use DBI;
use strict;

my driver   = "Pg"; 
mydatabase = "testdb";
my dsn = "DBI:driver:dbname = database;host = 127.0.0.1;port = 5432";
myuserid = "postgres";
my password = "pass123";
mydbh = DBI->connect(dsn,userid, password, { RaiseError => 1 })
   or dieDBI::errstr;
print "Opened database successfully\n";

my stmt = qq(DELETE from COMPANY where ID=2;);
myrv = dbh->do(stmt) or die DBI::errstr;
if(rv < 0 ) {
   print DBI::errstr;
} else{
   print "Total number of rows deleted :rv\n";
}
stmt = qq(SELECT id, name, address, salary  from COMPANY;);
mysth = dbh->prepare(stmt );
rv =sth->execute() or die DBI::errstr;
if(rv < 0) {
   print DBI::errstr;
}
while(my @row =sth->fetchrow_array()) {
      print "ID = ". row[0] . "\n";
      print "NAME = ".row[1] ."\n";
      print "ADDRESS = ". row[2] ."\n";
      print "SALARY =  ".row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

当执行以上给定的程序时，它将产生以下结果−

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully