perl连接字符串_「实战」助力数据库开发之接口篇 - Perl 连接 Greenplum

Perl 是一种功能丰富的计算机编程语言。借取了 C、sed、awk、shell 脚本语言以及很多其他程序语言的特性，其中最重要的特性是它内部集成了正则表达式的功能。它就像 C 一样强大，像 awk、sed 等脚本描述语言一样方便，被 Perl 语言爱好者称之为“一种拥有各种语言功能的梦幻脚本言”、“Unix中的王牌工具”。

Perl 语言连接 Greenplum 也是通过底层 C 接口 libpq 进行交互，如果我们要使用 Perl 做数据库交互，首先我们需要安装 Perl 的数据库标准模块 Perl DBI，然后再安装相应的 DBD 驱动，这里连接 Greenplum 采用的是 DBD::pg ( https://metacpan.org/pod/DBD::Pg ) 模块。

安装

这里采用较为简单的 RPM 安装方式。

rpm -ivh postgresql-libs-9.2.24-1.el7_5.x86_64.rpmrpm -ivh perl-DBI-1.627-4.el7.x86_64.rpmrpm -ivh perl-version-0.99.07-3.el7.x86_64.rpmrpm -ivh perl-DBD-Pg-2.19.3-4.el7.x86_64.rpm

Perl 连接 Greenplum

连接整体比较简单，DBI 驱动已经将连接方式抽象为易于理解的格式，下面通过几个例子来展示，读者可以关注如何配置连接方式、如何创建连接、如何增删改查等操作。

示例

–、建表

该示例主要演示连接字符串定义，数据库连接打开关闭几在数据库中创建表。

#!/usr/bin/perluse DBI;use strict;my $driver   = "Pg";my $database = "postgres";my $dsn = "DBI:$driver:dbname=$database;host=172.16.142.192;port=5432";my $userid = "gpadmin";my $password = "gpadmin";my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })                      or die $DBI::errstr;print "Opened database successfully";my $stmt = qq(CREATE TABLE TC(ID INT, NAME TEXT););my $rv = $dbh->do($stmt);if($rv < 0){   print $DBI::errstr;} else {   print "Table created successfully";}$dbh->disconnect();

执行结果如下：

[root@gp1 ~]# ./create.plOpened database successfullyNOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.Table created successfully

二、插入数据

#!/usr/bin/perluse DBI;use strict;my $driver   = "Pg";my $database = "postgres";my $dsn = "DBI:$driver:dbname=$database;host=172.16.142.192;port=5432";my $userid = "gpadmin";my $password = "gpadmin";my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })                      or die $DBI::errstr;print "Opened database successfully";my $stmt = qq(INSERT INTO TC (ID,NAME)      VALUES (1, 'Chris'));my $rv = $dbh->do($stmt) or die $DBI::errstr;$stmt = qq(INSERT INTO TC (ID,NAME)      VALUES (2, 'Jenny'));$rv = $dbh->do($stmt) or die $DBI::errstr;print "Records created successfully";$dbh->disconnect();

执行结果如下：

[root@gp1 ~]# vim insert.pl[root@gp1 ~]# chmod +x insert.pl[root@gp1 ~]# ./insert.plOpened database successfullyRecords created successfully

三、查询数据

!/usr/bin/perluse DBI;use strict;my $driver   = "Pg";my $database = "postgres";my $dsn = "DBI:$driver:dbname=$database;host=172.16.142.192;port=5432";my $userid = "gpadmin";my $password = "gpadmin";my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })                      or die $DBI::errstr;print "Opened database successfully";my $stmt = qq(SELECT id, name from TC;);my $sth = $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] . "";      print "NAME = ". $row[1] ."";}print "Operation done successfully";$dbh->disconnect();

执行结果如下：

[gpadmin@gp1 ~]$ vim select.pl[gpadmin@gp1 ~]$ chmod +x select.pl[gpadmin@gp1 ~]$ ./select.plOpened database successfullyID = 2NAME = JennyID = 1NAME = ChrisOperation done successfully

有关修改和删除的操作，基本与上面例子一致，只需要将代码中 qq() 中的内容替换为 DELETE 和 UPDATE 语句即可。

End～