perlDBI程序进行数据的采集.docx

最近在写perl程序进行数据的采集，perl教程在网上少的可怜呐，至少我找到的资料是很少的。在连接数据库方面下面这个博客写的还是很清晰的，所以就转过来了。源文地址：/like_zhz/article/details/5441946DBI和DBD的不同关系模型：#可移植的DBI方法：connect 建立到一个数据库服务器的连接disconnect 断开数据库服务器的连接prepare 准备执行一个SQL语句execute 执行准备好的语句do 准备并执行一个SQL语句quote 加引号于要插入的字符串或BLOB值fetchrow_array 作为一个字段数组取出下一行fetchrow_arrayref 作为一个字段的引用数组取出下一行fetchrow_hashref 作为一个哈希表的引用取出下一行fetchall_arrayref 作为一个字段数组取出所有数据finish 完成一条语句并且让系统释放资源rows 返回受影响的行数data_sources 返回可在localhost上得到的数据库的数组ChopBlanks 控制fetchrow_*方法是否剥去空格NUM_OF_PARAMS 在准备的语句中的占位(placeholder-参数)的数目NULLABLE 其列可以是NULLtrace 执行调试跟踪#$dbh 数据库句柄$sth 语句句柄$rc 返回代码(经常是一个状态)$rv 返回值(经常是一个行数)#connect($data_source, $username, $password)使用connect方法使得一个数据库连接到数据源。$data_source值应该以DBI:driver_name:开始。以DBD:mysql驱动程序使用connect的例子：$dbh = DBI-connect(DBI:mysql:$database, $user, $password);$dbh = DBI-connect(DBI:mysql:$database:$hostname,$user, $password);$dbh = DBI-connect(DBI:mysql:$database:$hostname:$port,$user, $password);disconnectdisconnect方法从数据库断开数据库句柄。它一般就在你从程序退出之前被调用。范例：$rc = $dbh-disconnect;prepare($statement)准备一条由数据库引擎执行的SQL语句并且返回语句句柄($sth)，你可以使用它调用execute方法。一般地你借助于prepare和execute来处理SELECT语句(和类SELECT语句，例如SHOW、DESCRIBE和EXPLAIN)。范例：$sth = $dbh-prepare($statement)or die Cant prepare $statement: $dbh-errstr/n;executeexecute方法执行一个准备好的语句。对非SELECT语句，execute返回受影响的行数。如果没有行受影响，execute返回0E0，Perl将它视作零而不是真。对于SELECT语句，execute只是在数据库中启动SQL查询；你需要使用在下面描述的fetch_*方法之一检索数据。范例：$rv = $sth-executeor die cant execute the query: $sth-errstr;do($statement)do方法准备并且执行一条SQL语句并且返回受影响的行数。如果没有行受到影响，do返回0E0，Perl将它视为零而不是真。这个方法通常用于事先无法准备好(由于驱动程序的限制)或不需要执行多次(插入、删除等等)的非SELECT语句。范例：$rv = $dbh-do($statement)or die Cant execute $statement: $dbh- errstr/n;quote($string)quote方法被用来“转义”包含在string中的任何特殊字符并增加所需的外部的引号。范例：$sql = $dbh-quote($string)fetchrow_array这个方法取下一行数据并且作为一个字段值数组返回它。范例：while(row = $sth-fetchrow_array) print qw($row0/t$row1/t$row2/n);fetchrow_arrayref这个方法取下一行数据并且作为一个对一个字段值数组的引用返回它。范例：while($row_ref = $sth-fetchrow_arrayref) print qw($row_ref-0/t$row_ref-1/t$row_ref-2/n);fetchrow_hashref这个方法取一行数据并且返回包含字段名/值对的一个哈希表的一个引用。这个方法不如使用上述数组引用那样有效。范例：while($hash_ref = $sth-fetchrow_hashref) print qw($hash_ref-firstname/t$hash_ref-lastname/t/$hash_ref- title/n);fetchall_arrayref这个方法被用来获得从SQL语句被返回的所有数据(行)。它返回一个数组的引用，该数组包含对每行的数组的引用。你用一个嵌套循环来存取或打印数据。范例：my $table = $sth-fetchall_arrayrefor die $sth-errstr/n;my($i, $j);for $i ( 0 . $#$table ) for $j ( 0 . $#$table-$i ) print $table-$i$j/t;print /n;finish便名没有更多的数据将从这个语句句柄取出。你调用这个方法释放语句句柄和任何与它相关的系统资源。范例：$rc = $sth-finish;rows返回由最后一条命令改变(更新、删除等)的行数。这通常用在非SELECT的execute语句之后。范例：$rv = $sth-rows;NULLABLE返回一个对一个布尔值数组的引用；对数组的每个成员，一个TRUE值表示该列可以包含NULL值。范例：$null_possible = $sth-NULLABLE;NUM_OF_FIELDS这个属性表明由一条SELECT或SHOW FIELDS语句返回的字段数目。你可以用它检查一条语句是否返回了结果：一个零值表明一个象INSERT、DELETE或UPDATE的非SELECT语句。范例：$nr_of_fields = $sth-NUM_OF_FIELDS;data_sources($driver_name)这个方法返回一个数组，它包含在主机localhost上的MySQL服务器可得到的数据库名。范例：dbs = DBI-data_sources(mysql);ChopBlanks这个属性确定fetchrow_*方法是否将去掉返回值的头和尾的空白。范例：$sth-ChopBlanks =1;trace($trace_level)trace($trace_level, $trace_filename)trace方法开启或关闭跟踪。当作为一个DBI类方法调用时，它影响对所有句柄的跟踪。当作为一个数据库或语句句柄方法调用时，它影响对给定句柄的跟踪(和句柄的未来子孙)。设置$trace_level为2以提供详细的踪迹信息，设置$trace_level为0以关闭跟踪。踪迹输出缺省地输出到标准错误输出。如果指定$trace_filename，文件以添加模式打开并且所有跟踪的句柄的手被写入该文件。范例：DBI-trace(2); # trace everythingDBI-trace(2,/tmp/dbi.out); # trace everything to /tmp/dbi.out$dth-trace(2); # trace this database handle$sth-trace(2); # trace this statement handle你也可以通过设置DBI_TRACE环境变量开启DBI跟踪。将它设置为等价于调用DBI-(value)的数字值，将它设置为等价于调用DBI-(2,value)的路径名。以上是原文内容。- 看了一上午perl，然后用了一下午的时间写了下面的采集(用的时间比较多，呵呵)，代码没有抽象，高手轻拍。这个采集百万数据从informix到oracle不到10秒钟。#!/usr/bin/perlusestrict;useDBI;useTime:localtime;useData:Dumper;useTime:Local;useNet:FTP;my($para);if(ARGV!=1)$para=2;print$para=$paran;else$para=$ARGV0;print$para=$paran;if($paraconnect(DBI:ODBC:npmdb,informix,*,RaiseError=0,PrintError=0);my$gisdb_dbh=DBI-connect(DBI:Oracle:gisdb,gis,*,RaiseError=1,AutoCommit=0);if(!$npmdb_dbh|!$gisdb_dbh)print数据库联接失败n;else$npmdb_dbh-do(setisolationtodirtyread);my$sel_sql=selecta.first_result,a.ne_id,NVL(CSTRAFFIC_CONV11,0),NVL(CSTRAFFIC_CONV22,0),NVL(CSTRAFFIC_CONV55,0),NVL(TSNBRASSNBRUUL,0),NVL(TSNBRASSNBRUDL,0),NVL(BRUUL,0),NVL(BRUDL,0),NVL(TDDMAXTCP,0),NVL(TDDMEANTCP,0),NVL(NBRERRBLOCKSRECEIVEDCS_CONV55,0),NVL(NBRBLOCKSRECEIVEDCS_CONV55,0),NVL(NBRERRBLOCKSRECEIVEDPS,0),NVL(NBRBLOCKSRECEIVEDPS,0),NVL(SUCCMACDESTAB,0),NVL(SUCCRBESTAB,0),NVL(ATTMACDESTAB,0),NVL(ATTRBESTAB,0),NVL(a.ATTRABASSIGNESTABCS_CONV11,0)+NVL(a.ATTRABASSIGNESTABCS_CONV22,0),NVL(a.SUCCRABASSIGNESTABCS_CONV11,0)+NVL(a.SUCCRABASSIGNESTABCS_CONV22,0),NVL(a.ATTCONNESTAB_1,0)+NVL(a.ATTCONNESTAB_6,0),NVL(a.SUCCCONNESTAB_1,0)+NVL(a.SUCCCONNESTAB_6,0),round(NVL(SFB_DIVFLOAT_1(NVL(a.SUCCCONNESTAB_1,0)+NVL(a.SUCCCONNESTAB_6,0),NVL(a.ATTCONNESTAB_1,0)+NVL(a.ATTCONNESTAB_6,0),0,0)*SFB_DIVFLOAT_1(NVL(a.SUCCRABASSIGNESTABCS_CONV11,0)+NVL(a.SUCCRABASSIGNESTABCS_CONV22,0),NVL(a.ATTRABASSIGNESTABCS_CONV11,0)+NVL(a.ATTRABASSIGNESTABCS_CONV22,0),0,0)*100,0),2),NVL(a.ATTRABASSIGNESTABCS_CONV55,0),NVL(a.SUCCRABASSIGNESTABCS_CONV55,0),round(NVL(SFB_DIVFLOAT_1(NVL(a.SUCCCONNESTAB_1,0)+NVL(a.SUCCCONNESTAB_6,0),NVL(a.ATTCONNESTAB_1,0)+NVL(a.ATTCONNESTAB_6,0),0,0)*SFB_DIVFLOAT_1(NVL(a.SUCCRABASSIGNESTABCS_CONV55,0),NVL(a.ATTRABASSIGNESTABCS_CONV55,0),0,0)*100,0),2),NVL(a.ATTCONNESTAB,0),NVL(a.SUCCCONNESTAB,0),NVL(a.ATTRABASSIGNESTABPS,0),NVL(a.SUCCRABASSIGNESTABPS,0),round(NVL(SFB_DIVFLOAT_1(a.SUCCRABASSIGNESTABPS,a.ATTRABASSIGNESTABPS,0,0),0)*NVL(SFB_DIVFLOAT_1(a.SUCCCONNESTAB,a.ATTCONNESTAB,0,0),0)*100,2),NVL(a.NBRRNCRELCSRAB_CONV11,0)+NVL(a.NBRRNCRELCSRAB_CONV22,0),NVL(a.NBRRABCSRELIUCONN_CONV11,0)+NVL(a.NBRRABCSRELIUCONN_CONV22,0),round(SFB_DIVFLOAT_1(NVL(a.NBRRNCRELCSRAB_CONV11,0)+NVL(a.NBRRNCRELCSRAB_CONV22,0)+NVL(a.NBRRABCSRELIUCONN_CONV11,0)+NVL(a.NBRRABCSRELIUCONN_CONV22,0),NVL(a.SUCCRABASSIGNESTABCS_CONV11,0)+NVL(a.SUCCRABASSIGNESTABCS_CONV22,0),0,0)*100,2),NVL(a.NBRRNCRELCSRAB_CONV55,0),NVL(a.NBRRABCSRELIUCONN_CONV55,0),round(SFB_DIVFLOAT_1(NVL(a.NBRRNCRELCSRAB_CONV55,0)+NVL(a.NBRRABCSRELIUCONN_CONV55,0),NVL(SUCCRABASSIGNESTABCS_CONV55,0),0,0)*100,2),NVL(a.NBRRNCRELPSRAB,0)-NVL(a.REL_REQ_PS_16,0)-NVL(a.REL_REQ_PS_40,0),NVL(a.NBRRABPSRELIUCONN,0),round(SFB_DIVFLOAT_1(NVL(a.NBRRNCRELPSRAB,0)+NVL(a.NBRRABPSRELIUCONN,0)-NVL(a.REL_REQ_PS_16,0)-NVL(a.REL_REQ_PS_40,0)-NVL(a.RAB_PS_REL_IU_CONN_16,0)-NVL(a.RAB_PS_REL_IU_CONN_40,0),a.SUCCRABASSIGNESTABPS,0,0)*100,2),NVL(a.NBRBLOCKSRECEIVEDCS_CONV,0),NVL(a.NBRERRBLOCKSRECEIVEDCS,0),round(NVL(SFB_DIVFLOAT_1(a.NBRERRBLOCKSRECEIVEDCS_CONV55,a.NBRBLOCKSRECEIVEDCS_CONV55,0,0)*100,0),2),round(NVL(SFB_DIVFLOAT_1(a.NBRERRBLOCKSRECEIVEDPS,a.NBRBLOCKSRECEIVEDPS,0,0)*100,0),2),NVL(b.ATTOUTCS,0),NVL(b.FAILOUTCS,0),round(SFB_DIVFLOAT_1(NVL(b.ATTOUTCS,0)-NVL(b.FAILOUTCS,0),NVL(b.ATTOUTCS,0),0,0)*100,2),NVL(b.ATTOUTPSUTRAN,0),NVL(b.FAILOUTPSUTRAN,0),round(SFB_DIVFLOAT_1(NVL(b.ATTOUTPSUTRAN,0)-NVL(b.FAILOUTPSUTRAN,0),NVL(b.ATTOUTPSUTRAN,0),0,0)*100,2)fromtpc_utrancell_nea,TPC_UTRANCELL_HO_NEb,TPC_UTRANCELL_HSPA_NEcwherea.first_result=currentyeartohour-$paraunitshour|:00:00anda.first_result=b.first_resultanda.first_result=c.first_resultanda.ne_id=b.ne_idanda.ne_id=c.ne_id;print$sel_sqln;my$rsite=$npmdb_dbh-prepare($sel_sql);$rsite-execute();my$ref_data=$rsite-fetchall_arrayref();$rsite-finish;$gisdb_dbh-do(deletefromBTS_PM_TDwherefirst_resultdo(deletefromBTS_PM_TDwherefirst_result=to_date(to_char(sysdate-$para/24),YYYY-MM-DDHH24),SYYYY-MM-DDHH24:MI:SS);my$MM=0;foreachmy$row($ref_data)mydata=$row;my$dataLen=data;my$gisInc=insertintoBTS_PM_TDvalues(to_date($data0,SYYYY-MM-DDHH24:MI:SS),$data1,; for(my$HH=2;$HHdo($gisInc);$MM+;print$MMn;$gisdb_dbh-do(commit);$npmdb_dbh-disconnect();$gisdb_dbh-disconnect();exit(0);Perl DBI实例2008-05-23 16:24:35|分类：perl|举报|字号订阅此文档的目的是给使用Perl DBI模块访问数据库的开发人员提供一些实列。也为了减轻Perl DBI邮件列表的负担。通过此文档，我们将建立更加友好的DBI程序来访问Oracle数据库。我们将从DBI基础知识开始，然后介绍一些关于提高性能跟稳定性的概念。基础知识：我们要做的第一件事情是安装DBI模块。这在 DBI INSTALL 文档中有介绍。接着我们需要安装数据库驱动，或者称为DBD。其安装简介也在每个DBD的包中有介绍。如大多数Perl模块一样，安装DBI/DBD是很容易的：localhost:/opt/src/perl_modules/DBI-1.13$ perl Makefile.PL & make & make test & make install装完DBI 跟 DBD，你可以这样得到更多的信息：localhost:$ perldoc DBI连接数据库：连接不同的数据库需要不同的方法。为得到详尽的信息，请阅读来在DBD的说明文档。 下面的例子适用于连接Oracle：use strict;use DBI;my $dbh = DBI-connect( dbi:Oracle:orcl, jeffrey, jeffspassword, );上面的连接字符串(DSN)有三个参数：数据源，用户名和密码。DSN（数据源名称) 形式是这样的：dbi:驱动名称:实列 。但是我们怎么知道连接是成功还是失败了呢？首先，如果connect方法连接成功，将返回一个true，否则返回false。其次，DBI将在包变量$DBI:errstr设置一个错误消息串。use strict;use DBI;my $dbh = DBI-connect( dbi:Oracle:orcl, jeffrey, jeffspassword, ) | die Database connection not made: $DBI:errstr;$dbh-disconnect();使用disconnect()方法将避免出现“Database handle destroyed without explicit disconnect”这样的错误。选项：connect()方法可以带一个哈希选项。常用的哈希选项包括：AutoCommit,当它为true时，事务处理将被自动提交。RaiseError,它在DBI遇到错误时调用croak $DBI:errstr。PrintError，它将调用DBI的warn $DBI:errstr。在这个程序中，我们想使用事务处理，因此我们关闭AutoCommit，开启RaiseError，PrintError用其默认设置。use strict;use DBI;my $dbh = DBI-connect( dbi:Oracle:orcl, jeffrey, jeffspassword, RaiseError = 1, AutoCommit = 0 ) | die Database connection not made: $DBI:errstr;$dbh-disconnect();注意的是，对于不支持事务处理的数据库而言，设置AutoCommit为false将导致严重错误。发出SQL：现在我们打算在数据库上作些有用的事。有两种得到数据库SQL声明的方法。对于查询，将发挥行记录，比如SELECT，我们将使用prepare 方法对于其他查询，比如CREATE 跟 DELETE，我们将使用 do 方法。让我们先来看一下后者，等一下下再看前一种情况。这个程序将在数据库中建立一个雇员表 ：use strict;use DBI;my $dbh = DBI-connect( dbi:Oracle:orcl, jeffrey, jeffspassword, RaiseError = 1, AutoCommit = 0 ) | die Database connection not made: $DBI:errstr;my $sql = qq CREATE TABLE employees ( id INTEGER NOT NULL, name VARCHAR2(128), title VARCHAR2(128), phone CHAR(8) ) ;$dbh-do( $sql );$dbh-disconnect();中级：我们已经看到了怎样连接数据库，怎样发现错误以及发出简单SQL声明了。现在让我们继续前进看看更有用的代码吧。SELECT 声明：SELECT大概是SQL中最常用使用的了。为了用SELECT声明，我们首先将prepare(准备)声明，然后execute(执行)它。下列代码中，$sth 变量是声明句柄，我们将利用它来访问SELECT的结果。use strict;use DBI;my $dbh = DBI-connect( dbi:Oracle:orcl, jeffrey, jeffspassword, RaiseError = 1, AutoCommit = 0 ) | die Database connection not made: $DBI:errstr;my $sql = qq SELECT * FROM employees ;my $sth = $dbh-prepare( $sql );$sth-execute();$dbh-disconnect(); 以上清单将使数据库安排一个执行声明的计划，然后执行声明。实际上它对返回结果行没有进行任何操作。在接下来的代码清单中，我们将使用bind_columns从数据库中取出记录。bind_columns方法绑定每个数据库行和对应的一个标量引用，然后当fetch方法被调用时，这些变量将装载从数据库里取来的值。use strict;use DBI;my $dbh = DBI-connect( dbi:Oracle:orcl, jeffrey, jeffspassword, RaiseError = 1, AutoCommit = 0 ) | die Database connection not made: $DBI:errstr;my $sql = qq SELECT id, name, title, phone FROM employees ;my $sth = $dbh-prepare( $sql );$sth-execute();my( $id, $name, $title, $phone );$sth-bind_columns( undef, $id, $name, $title, $phone );while( $sth-fetch() ) print $name, $title, $phonen;$sth-finish();$dbh-disconnect();这是个打印公司电话薄的好程序。但是WHERE字句怎么处理呢？我们将使用 bind_param方法一次准备一个SQL声明，执行将非常快。use strict;use DBI qw(:sql_types);my $dbh = DBI-connect( dbi:Oracle:orcl, jeffrey, jeffspassword, RaiseError = 1, AutoCommit = 0 ) | die Database connection not made: $DBI:errstr;my names = ( Larry%, Tim%, Randal%, Doug% );my $sql = qq SELECT id, name, title, phone FROM employees WHERE name LIKE ? ;my $sth = $dbh-prepare( $sql );for( names ) $sth-bind_param( 1, $_, SQL_VARCHAR ); $sth-execute(); my( $id, $name, $title, $phone ); $sth-bind_columns( undef, $id, $name, $title, $phone ); while( $sth-fetch() ) print $name, $title, $phonen; $sth-finish();$dbh-disconnect();高级：事务处理： 截止到现在，我们还没有用事务处理来做任何事情。但是如果我们想发出UPDATE 或者 DELETE声明，我们将会使用事务处理。通过参考DBI文档，用DBI执行健壮的事务处理的最好方法是使用eval. 块去捕获错误。然后使用commit() 或者 rollback()来结束事务处理。这就是我们下面的代码清单所做的事情。程序往数据库里装载四条记录。use strict;use DBI qw(:sql_types);my $dbh = DBI-connect( dbi:Oracle:orcl, jeffrey, jeffspassword, RaiseError = 1, AutoCommit = 0 ) | die Database connection not made: $DBI:errstr;my records = ( 0, Larry Wall, Perl Author, 555-0101 , 1, Tim Bunce, DBI Author, 555-0202 , 2, Randal Schwartz, Guy at Large, 555-0303 , 3, Doug MacEachern, Apache Man, 555-0404 );my $sql = qq INSERT INTO employees VALUES ( ?, ?, ?, ? ) ;my $sth = $dbh-prepare( $sql );for( records ) eval $sth-bind_param( 1, $_-0, SQL_INTEGER ); $sth-bind_param( 2, $_-1, SQL_VARCHAR ); $sth-bind_param( 3, $_-2, SQL_VARCHAR ); $sth-bind_param( 4, $_-3, SQL_VARCHAR ); $sth-execute();