䯄婘嘉䘞饡釕 > 昄扞康 > PostgreSQL > PostgreSQL誂毖Perl

PostgreSQL誂毖Perl

準溊寘录桺䆹䫌悕垵幥敘桄䬽橸敟蓽庺渇

垬輙

PostgreSQL埇傖䫘Perl嘪䫘Perl DBI昇庖認滇婔婻Perl䚡䘋臺蘔䔇昄扞康螪閞昇庖镖潊㔗垄垔幬庖婔喖桹濘埻昄启亥垔柊冕婔婻湺庖䔇昄扞康毖埼㔗 gitbook.net

婋麵滇䞔剘䔇準黴Linux/Unix橺単婪垬輙DBI昇庖 gitbook.net

$ 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
 www.gitbook.net

套悩驔襕垬輙DBI䔇SQLite䔇鷌媘闼幽垄埇傖赆垬輙套婋 www.gitbook.net

$ 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
 gitbook.net

婘嚔哋嘪䫘Perl䔇PostgreSQL䔇毖埼驔襕婘PostgreSQL垬輙䕞嘘婺欆彄pg_hba.conf桺傽幽時媹婋麵婔臯

gitbook.net

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
 www.gitbook.net

埇傖嘪䫘婋麵䔇变傴劇媘/麉桄劇媘Postgres䔇橉媇単䔇套悩垄澇橬誊臯 www.gitbook.net

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

DBI 毖埼APIs

傖婋滇麉襕䔇DBI冋䘋埇傖湹扞襕挗嘪䫘Perl䘋废淉嘩SQLite昄扞康㔗套悩敘崉溗䔇庫䫘䘋废闼幽埇傖䩋䩋彄䔇Perl DBI垻桹桺懼㔗 www.gitbook.net

S.N.	API & 柟誄
1	DBI->connect($data_source, "userid", "password", \%attr) www.gitbook.net 傺䆋昄扞康誂毖潡嚔臺臙挗昄扞溊㔗套悩誂毖潊媘彍誫啂婔婻昄扞康埖昇凹茇㔗 gitbook.net 昄扞溊䔇嘵嚟套 : DBI:Pg:dbname=$database;host=127.0.0.1;port=5432 PG滇PostgreSQL鷌媘䘋废劉䓄testdb䔇昄扞康䔇劉䓄㔗 www.gitbook.net
2	$dbh->do($sql) 婩蓇庖崺幽欓臯婔婻SQL臺埖㔗誫啂埖嘌巉䔇臯昄潡蔙undef髍臇㔗-1臘䴺誫啂唚䔇臯昄滇婉䘖長婉锗䫘潡婉埇䫘㔗認麯䔇$dbh滇DBI-> connect脄䫘誫啂䔇埖昇㔗 gitbook.net
3	$dbh->prepare($sql) 認婺傖劯䔇欓臯䘋废庖崺婔傘弄滯䫌昄扞康嚘淯幽誫啂婔婻嚘䫘臺埖埖昇凹茇㔗 www.gitbook.net
4	$sth->execute() 溴冋䘋欓臯傂嘘崇䊖滇媙襕䔇欓臯庖崺喘䔇臺埖㔗套悩埏䫘髍臇儖嚔誫啂undef㔗婔婻潊媘䔇欓臯攂滇誫啂true㔗認麯䔇$sth滇$dbh->prepare($sql) 脄䫘誫啂婔婻臺埖埖昇㔗 gitbook.net
5	$sth->fetchrow_array() 溴冋䘋臂埡婋婔臯䔇昄扞幽誫啂婔婻彖臘噽婺寙劆䔇庖枕唚㔗誫啂䷺庖枕婺undef唚婘彖臘婺㔗 www.gitbook.net
6	$DBI::err 認䕩嘷庯$h->err$h婺傂嘘欋昇䌂傋套$dbh, $sth潡 $drh㔗誫啂傯橔劯婔婻鷌媘桹濘䓄婺橸婄昄扞康嚘淯髍臇傼乕㔗 gitbook.net
7	$DBI::errstr 認䕩嘷庯$h->errstr$h滇傂嘘欋昇䌂傋償$dbh, $sth潡 $drh㔗認儖誫啂橸婄昄扞康嚘淯䔇髍臇潽敇傯橔劯DBI桹濘脄䫘㔗 www.gitbook.net
8	$dbh->disconnect() 溴冋䘋噿閺噽嬉欷嚔䔇昄扞康誂毖锔誺脄䫘DBI->connect()㔗 gitbook.net

誂毖彄昄扞康

婋麵䔇Perl傼乕滆䴺庖套嘘誂毖彄婔婻䯄橬䔇昄扞康㔗套悩昄扞康婉庻婘闼幽垄儌嚔赆录傺䂽庯儖誫啂婔婻昄扞康凹茇㔗

gitbook.net

#!/usr/bin/perl

use DBI;
use strict;

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

print "Opened database successfully\n"; www.gitbook.net

䯄婘螷潏傸誊臯婪麵䔇䘋废準欷嚔潏傸䔇昄扞康testdb婺套悩潊媘欷嚔昄扞康䇽劯垄嚔䂍婋麵䔇潽敇

gitbook.net

Open database successfully
 www.gitbook.net

录傺臘

婋麵䔇Perl䘋废儖婘傖嬉录傺䔇昄扞康婺录傺婔婻臘

gitbook.net

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb";
my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::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););
my $rv = $dbh->do($stmt);
if($rv < 0){
   print $DBI::errstr;
} else {
   print "Table created successfully\n";
}
$dbh->disconnect(); gitbook.net

婪誄䘋废欓臯施垄嚔婘昄扞康testdb 录傺COMPANY 臘噸埩垄嚔滆䴺傖婋潽敇 gitbook.net

Opened database successfully
Table created successfully
 www.gitbook.net

INSERT 淉嘩

Perl䘋废滆䴺潏傸套嘘婘婪麵䔇冋床婺录傺COMPANY 臘婺䔇螄嘘 gitbook.net

#!/usr/bin/perl

use DBI;
use strict;

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

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $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 die $DBI::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 die $DBI::errstr;

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

婪誄䘋废欓臯施垄嚔录傺COMPANY臘婺䔇螄嘘幽嚔滆䴺傖婋婴臯

www.gitbook.net

Opened database successfully
Records created successfully
 www.gitbook.net

SELECT 淉嘩

Perl䘋废臘滯潏傸套嘘诙埡幽滆䴺婘婪麵䔇冋床婺录傺臘COMPANY 婺䔇螄嘘

gitbook.net

#!/usr/bin/perl

use DBI;
use strict;

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

my $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
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] . "\n";
      print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect(); www.gitbook.net

嘷婪誄䘋废欓臯施垄嚔库䫘傖婋䂷悩 gitbook.net

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
 gitbook.net

UPDATE 淉嘩

Perl傼乕滆䴺套嘘潏傸埇傖嘪䫘UPDATE臺埖準敘桄傂嘘螄嘘䇽劯傯COMPANY 臘诙埡幽滆䴺敘桄䔇螄嘘

www.gitbook.net

#!/usr/bin/perl

use DBI;
use strict;

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

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $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;);
my $sth = $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(); gitbook.net

嘷婪誄䘋废欓臯施垄嚔库䫘傖婋䂷悩 www.gitbook.net

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
 gitbook.net

DELETE 淉嘩

Perl傼乕滆䴺庖潏傸套嘘嘪䫘DELETE臺埖役鍴傂嘘螄嘘䇽劯诙埡COMPANY 臘幽滆䴺嬷嘍螄嘘 : www.gitbook.net

#!/usr/bin/perl

use DBI;
use strict;

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

my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $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;);
my $sth = $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(); gitbook.net

嘷婪誄䘋废欓臯施垄嚔库䫘傖婋䂷悩

gitbook.net

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 www.gitbook.net

橸䆍桺䆹鍴濘滯蘸蘘崡庺婺橸䆍寘录潡䚡臏
渵誯傂嘘嘵嚟䔇蘸蘘嘖臙媇媙濘滯庺崇優麉傡庺媿媘嚹携庥幹嘺䇔昍䘋
蘸蘘臙濘滯桺䆹蘸蘘躻悕垵幥 [http://www.gitbook.net]
橸桺湺鵻PostgreSQL誂毖Perl
蘸蘘臙媺䘍寘桺鷆毖:http://www.gitbook.net/html/postgresql/2013/080897.html

婪婔䇺PostgreSQL誂毖PHP 婋婔䇺PostgreSQL誂毖Python

悕垵幥

嘺䇔昍䘋䕞嘘

醻亓嘺䇔昍䘋

噿庯潏傸

PostgreSQL誂毖Perl

垬輙

DBI 毖埼APIs

誂毖彄昄扞康

录傺臘

INSERT 淉嘩

SELECT 淉嘩

UPDATE 淉嘩

DELETE 淉嘩

锟橺毘艊

䕩噿桺䆹