当前位置: 首页 > 科技观察

PostgreSQL连接Perl

时间:2023-03-21 11:26:33 科技观察

安装PostgreSQL可以使用Perl使用PerlDBI模块,它是Perl编程语言的数据库访问模块集成。它定义了一组方法、变量和约定,提供了一个标准的数据库接口。以下是在Linux/Unix机器上安装DBI模块的简单步骤:$wgethttp://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz$tarxvfzDBI-1.625.tar.gz$cdDBI-1.625$perlMakefile.PL$make$makeinstall如果需要安装DBI的SQLite驱动,可以这样安装:$wgethttp://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz$tarxvfzDBD-Pg-2.19.3.tar.gz$cdDBD-Pg-2.19.3$perlMakefile.PL$make$makeinstall在StartPerlPostgreSQL界面,你需要在PostgreSQL安装目录下找到pg_hba.conf文件,添加如下行:#IPv4localconnections:hostallall127.0.0.1/32md5如果Postgres服务器没有运行,可以使用以下命令启动/重启:[root@host]#servicepostgresqlrestartStoppingpostgresqlservice:[OK]Startingpostgresqlservice:[OK]DBI接口API下面是重要的DBI例程,您可以根据需要使用Perl程序操作SQLite数据库。对于更复杂的应用程序,请查看官方PerlDBI文档。连接到数据库下面的Perl代码显示了如何连接到现有数据库。如果数据库不存在,则创建它,最后返回一个数据库对象。#!/usr/bin/perluseDBI;usestrict;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})ordie$DBI::errstr;print"打开数据库成功\n";现在让我们运行上面的程序来打开我们的数据库testdb,如果数据库成功打开,那么它会给出如下信息:/usr/bin/perluseDBI;usestrict;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})ordie$DBI::errstr;print"成功打开数据库\n";my$stmt=qq(CREATETABLECOMPANY(IDINTPRIMARYKEYNOTNULL,NAMETEXTNOTNULL,AGEINTNOTNULL,ADDRESSCHAR(50),SALARYREAL););my$rv??=$dbh->do($stmt);if($rv<0){print$DBI::errstr;}else{print"Tablecreatedsuccessfully\n";}$dbh->disconnect();执行上述程序时,会在数据库testdb中创建COMPANY表company,并显示如下信息:OpeneddatabasesuccessfullyTablecreatedsuccessfullyINSERTOperatingaPerlprogramshowingwecreaterecordsintheaboveexample:#!/usr/bin/perluseDBI;usestrict;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})ordie$DBI::errstr;print"打开数据库成功\n";my$stmt=qq(INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)VALUES(1,'Paul',32,'California',20000.00));my$rv??=$dbh->do($stmt)ordie$DBI::errstr;$stmt=qq(INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)VALUES(2,'Allen',25,'Texas',15000.00));$rv=$dbh->do($stmt)ordie$DBI::errstr;$stmt=qq(INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)VALUES(3,'Teddy',23,'Norway',20000.00));$rv=$dbh->do($stmt)ordie$DBI::errstr;$stmt=qq(INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)VALUES(4,'Mark',25,'Rich-Mond',65000.00););$rv=$dbh->do($stmt)ordie$DBI::errstr;print"Recordscreatedsuccessfully\n";$dbh->disconnect();上面程序执行时,会创建COMPANY表记录,显示如下两行:OpeneddatabasesuccessfullyRecordscreatedsuccessfullySELECT运行Perl程序,说明我们如何获取并显示上例创建的COMPANY表中的记录:#!/usr/bin/perluseDBI;usestrict;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})ordie$DBI::errstr;print"打开数据库成功\n";my$stmt=qq(SELECTid,name,address,salaryfromCOMPANY;);my$sth=$dbh->prepare($stmt);my$rv??=$sth->execute()ordie$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";}p??rint"Operationdonesuccessfully\n";$dbh->disconnect();当上面的程序执行时,会产生如下结果:OpeneddatabasesuccessfullyID=1NAME=PaulADDRESS=CaliforniaSALARY=20000ID=2NAME=AllenADDRESS=TexasSALARY=15000ID=3NAME=TeddyADDRESS=NorwaySALARY=20000ID=4NAME=MarkADDRESS=Rich-MondSALARY=65000Operationdonesuccessfully#p#UPDATE操作Perl代码显示了我们如何使用UPDATE语句更新任何记录,然后从中获取并显示更新公司表记录:#!/usr/bin/perluseDBI;usestrict;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})ordie$DBI::errstr;print"Openeddatabasesuccessfully\n";my$stmt=qq(UPDATECOMPANYsetSALARY=25000.00whereID=1;);my$rv??=$dbh->do($stmt)或死$DBI::errstr;if($rv<0){print$DBI::errstr;}else{print"Totalnumberofrowsupdated:$rv\n";}$stmt=qq(SELECTid,name,address,salaryfromCOMPANY;);my$sth=$dbh->prepare($stmt);$rv=$sth->execute()ordie$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";}p??rint"Operationdonesuccessfully\n";$dbh->disconnect();when上述程序执行时,它会产生以下结果:OpeneddatabasesuccessfullyTotalnumberofrowsupdated:1ID=1NAME=PaulADDRESS=CaliforniaSALARY=25000ID=2NAME=AllenADDRESS=TexasSALARY=15000ID=3NAME=TeddyADDRESS=NorwaySALARY=20000ID=4NAME=MarkADDRESS=Rich-MondSALARY=65000OperationdonesuccessfullyDELETE操作Perl代码展示了我们如何使用DELETE语句删除任何记录,然后获取COMPANY表并显示剩余记录:#!/usr/bin/perluseDBI;usestrict;my$driver="Pg";my$database="测试数据库";;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})ordie$DBI::errstr;print"Openeddatabasesuccessfully\n";my$stmt=qq(DELETEfromCOMPANYwhereID=2;);my$rv??=$dbh->do($stmt)ordie$DBI::errstr;if($rv<0){print$DBI::errstr;}else{print"Totalnumberofrowsdeleted:$rv\n";}$stmt=qq(SELECTid,name,address,salaryfromCOMPANY;);my$sth=$dbh->prepare($stmt);$rv=$sth->execute()ordie$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";}p??rint"Operationdonesuccessfully\n";$dbh->disconnect();当上记程序执行时,它会产生以下结果:OpeneddatabasesuccessfullyTotalnumberofrowsdeleted:1ID=1NAME=PaulADDRESS=CaliforniaSALARY=25000ID=3NAME=TeddyADDRESS=NorwaySALARY=20000ID=4NAME=MarkADDRESS=Rich-MondSALARY=65000Operationdonesuccessfully原文链接: