
平台论坛博客文库

› 论坛 › 程序设计 › Perl › perl多线程&DBI【解决】

perl多线程&DBI【解决】 [复制链接]

mousimin

白手起家

论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2013-05-17 16:41 |只看该作者 |倒序浏览

本帖最后由 mousimin 于 2013-05-20 20:33 编辑

#经测试确实是threads模块的问题，已经换多进程实现，并将普通方法、多进程一块附上，请大家多指教#
我有一个文件，大约10927行，想先将文件split为11个文件（1000行一个文件），然后用11个线程去解析这11个文件，每个线程去逐行解析文件，去数据库查询相应的信息，最后写入到另外11个文件中。
但是每个线程只能解析一行数据就退出线程了，麻烦大侠们看下！代码和打印日志如下：

#!/bin/env perl
use strict;
use DBI;
use DBIs;
use Env qw(HOME);
use TimeOpt;
use FileHandle;
use threads;
use threads::shared;
#####################################################
#初始化打印日志句柄
#####################################################
my $dsn_config;
my $file_time = TimeOpt::CFtime("YYYYMMDDhhmmss");
my $log_file = "$HOME/trace/CIRCUIT_ADD/CIRCUIT_ADD_${file_time}.log";
system("mkdir -p $HOME/trace/CIRCUIT_ADD");
my $fh = new FileHandle(">>$log_file");
#####################################################
#读取.mapi.ini配置文件
#####################################################
my $mapi = DBIs::Read_MapIniFile();
#print Dumper $mapi;
$dsn_config->{'COOKDB'}->{'db_type'} = $mapi->{'COOKDB'}->{'CONN_DBD'};
$dsn_config->{'COOKDB'}->{'db_dsn'} = $mapi->{'COOKDB'}->{'CONN_DBN'};
$dsn_config->{'COOKDB'}->{'db_user'} = $mapi->{'COOKDB'}->{'CONN_UID'};
$dsn_config->{'COOKDB'}->{'db_pwd'} = $mapi->{'COOKDB'}->{'CONN_PWD'};
$dsn_config->{'COOKDB'}->{'db_driver'} = $mapi->{'COOKDB'}->{'CONN_DBD'};
system("split -l 1000 circuit.unl split");
my @files:shared;
my @all_threads;
@files = `ls split*`;
my $num = @files;
for(my $i=0;$i<11;$i++)
{
my $t = threads->new(\&parser);
print "there are $#files+1 files and now make the $i+1 st thread $t...\n";
push @all_threads,$t;
}
foreach my $thread(@all_threads)
{
if($thread->is_joinable())
{
print "join thread $thread...\n";
$thread->join();
}
}
exit 0;
sub test
{
my $file =shift @files;
print "$file\n";
}
sub parser
{
my ($dbh_kpidb,$db_msg,$data_hash,$no,$file);
my $file =shift @files;
my $rel = connect_db(\$dbh_kpidb,$dsn_config->{'COOKDB'},\$db_msg);
$file =~ s/\n$//;
print ("$file---$db_msg\n");
my $r_hd = new FileHandle("</opt/BOCO.DAL/ZHJKNRM/personal/musimin/circuit/$file");
my $w_hd = new FileHandle(">/opt/BOCO.DAL/ZHJKNRM/personal/musimin/circuit/out_$file");
while(my $row = <$r_hd>)
{
my (@sql,$trace,$a_node_name,$z_node_name);
my @row = split ("\t",$row);
$no += 1;
if($row[6] =~ /(.*)\/.*/)
{
$a_node_name = $1
}else
{
$a_node_name ='11111111111';
}
if($row[7] =~ /(.*)\/.*/)
{
$z_node_name = $1
}else
{
$z_node_name ='11111111111';
}
$sql[0] = "select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%$a_node_name%'";
$sql[1] = "select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%$z_node_name%'";
if($row[8] =~ /^(\S+)\s*GSM900/)
{
$sql[2] = "select max(int_id),max(userlabel) from objects where object_class = 201 and userlabel like 'G-%$1%'";
}elsif($row[8] =~ /^(\S+)\s*GSM1800/)
{
$sql[2] = "select max(int_id),max(userlabel) from objects where object_class = 201 and userlabel like 'D-%$1%'";
}elsif($row[8] =~ /^(\S+)\s*WCDMA/)
{
$sql[2] = "select max(int_id),max(userlabel) from objects where object_class = 9201 and userlabel like '%$1%'";
}else
{
$sql[2] = "select max(int_id),max(userlabel) from objects where object_class = 9201 and userlabel like '%$row[8]%'";
}
my $n = 0;
foreach my $sql(@sql)
{
$n += 1;
my $sth_check = exec_sql(\$dbh_kpidb,$sql,\$db_msg);
print ("$file---$db_msg\n");
my @rows = $sth_check->fetchrow_array;
if(defined $trace)
{
$trace .= "\t$rows[0]\t$rows[1]";
}else
{
$trace = "$rows[0]\t$rows[1]";
}
}
print $w_hd "$no\t$trace\n";
if($no =~ /00$/)
{
print "No. $no line is writing into circuit_add.unl\n";
}
#next;
}
$r_hd->close;
$w_hd->close;
disconnect_db(\$dbh_kpidb,'nrmdb',\$db_msg);
print ("$file---$db_msg\n");
}
sub connect_db{
my $subdbh=shift;
my $config=shift;
my $err_msg = shift;
my ($sub_dbd,$sub_dbn,$sub_uid,$sub_pwd,$odbc_dsn);
my $db_driver;
$sub_dbd=$config->{'db_type'};
$sub_dbn=$config->{'db_dsn'};
$sub_uid=$config->{'db_user'};
$sub_pwd=$config->{'db_pwd'};
$odbc_dsn=$config->{'odbc_dsn'};
$db_driver=$config->{'db_driver'};
if(!$sub_dbd or !$sub_dbn){
print ( "no enough info about db_type and db_dsn connect_db \n");
return -1;
}
my $col_dsn;
if($odbc_dsn ne ''){
$col_dsn=$odbc_dsn;
}
else{
$col_dsn="dbi:$sub_dbd:$sub_dbn";
}
if($subdbh=DBI->connect($col_dsn,$sub_uid,$sub_pwd,{RaiseError => 0,PrintError => 0, AutoCommit => 1 })){
$err_msg = "Connect Database $col_dsn success\n";
}
else{
my $i = 0;
while($i < 3)
{
$i++;
if($subdbh=DBI->connect($col_dsn,$sub_uid,$sub_pwd,{RaiseError => 0,PrintError => 0, AutoCommit => 1 })){
last;
}
else
{
print ( "Err:Connect Database $col_dsn $sub_uid/*** for $i times \n\tErr Info:$DBI::errstr\n\n");
print ( "Try once more after 5 second...\n");
sleep(5);
}
}
if($subdbh)
{
$err_msg = "Connect Database $col_dsn success\n";
}
else
{
$err_msg = "Try Connect $col_dsn $sub_uid/*** $i times fail!\n";
return -1;
}
}
if(uc($db_driver) eq 'INFORMIX'){
$subdbh->do("set isolation to dirty read ;");
$subdbh->do("set lock mode to wait 600 ;");
}
if(uc(($db_driver)) eq 'ORACLE'){
$subdbh->do("ALTER SESSION SET NLS_DATE_FORMAT = \'YYYY-MM-DD HH24:MI:SS\'");
}
return 1;
}
sub disconnect_db{
my $dbh=shift;
my $db_name=shift;
my $err_msg = shift;
$dbh->disconnect();
$err_msg = "Sucess disconnect $db_name\n";
}
sub exec_sql{
my $ex_dbh = shift;
my $ex_sql = shift;
my $err_msg = shift;
my @para = @_;
my $ex_sth;
$err_msg = "Will exec SQL :\n$ex_sql\n";
if(!($ex_sth = $ex_dbh->prepare($ex_sql)))
{
$err_msg .= "$DBI::errstr\n";
return -1;
}
if(!($ex_sth->execute(@para)))
{
$err_msg .= "$DBI::errstr\n";
return -1;
}
return \$ex_sth;
}
#####################################################
#打印日志函数
#####################################################
sub print_log
{
my $msg = shift;
my $file_time = TimeOpt::CFtime("YYYY-MM-DD hh:mm:ss");
print $fh "$file_time\n----------------------\n$msg\n\n";
print "$file_time\n----------------------\n$msg\n\n";
}

复制代码

程序输出如下：

there are 10+1 files and now make the 0+1 st thread threads=SCALAR(0x34668b8)...
there are 9+1 files and now make the 1+1 st thread threads=SCALAR(0x34668e8)...
there are 8+1 files and now make the 2+1 st thread threads=SCALAR(0x3466918)...
splitaa---Connect Database dbi:Oracle:UNICOMJS success
splitaa---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F4400-利源路%'
splitab---Connect Database dbi:Oracle:UNICOMJS success
splitab---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F3248-华显%'
there are 7+1 files and now make the 3+1 st thread threads=SCALAR(0x3466948)...
splitac---Connect Database dbi:Oracle:UNICOMJS success
splitac---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F4119-东井村%'
there are 6+1 files and now make the 4+1 st thread threads=SCALAR(0x3466978)...
splitad---Connect Database dbi:Oracle:UNICOMJS success
splitaa---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%11111111111%'
splitab---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F3138-河西九楼%'
splitad---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%H1678-南师大体育馆%'
there are 5+1 files and now make the 5+1 st thread threads=SCALAR(0x34669a8)...
splitae---Connect Database dbi:Oracle:UNICOMJS success
splitae---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F5033-双林%'
there are 4+1 files and now make the 6+1 st thread threads=SCALAR(0x34669d8)...
splitaa---Will exec SQL :
select max(int_id),max(userlabel) from objects where object_class = 9201 and userlabel like '%利源路 3g%'
splitab---Will exec SQL :
select max(int_id),max(userlabel) from objects where object_class = 201 and userlabel like 'G-%华显%'
splitac---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F4939-河西九楼(二)%'
splitaf---Connect Database dbi:Oracle:UNICOMJS success
splitad---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%H1000-河西六楼%'
there are 3+1 files and now make the 7+1 st thread threads=SCALAR(0x3466a08)...
splitaf---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F5043-大花%'
splitac---Will exec SQL :
select max(int_id),max(userlabel) from objects where object_class = 201 and userlabel like 'G-%东井村%'
splitag---Connect Database dbi:Oracle:UNICOMJS success
there are 2+1 files and now make the 8+1 st thread threads=SCALAR(0x3466a38)...
splitae---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F4611-浦口新生圩%'
splitag---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%H1970-狮树%'
splitad---Will exec SQL :
select max(int_id),max(userlabel) from objects where object_class = 9201 and userlabel like '%南师大体育馆%'
splitah---Connect Database dbi:Oracle:UNICOMJS success
splitah---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F4122-安怀村2%'
there are 1+1 files and now make the 9+1 st thread threads=SCALAR(0x3466a68)...
splitae---Will exec SQL :
select max(int_id),max(userlabel) from objects where object_class = 9201 and userlabel like '%双林%'
splitaf---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F4007-新生圩%'
splitai---Connect Database dbi:Oracle:UNICOMJS success
splitai---Will exec SQL :
select max(int_id),max(userlabel) from objects_trans where object_class in (2001,2008) and userlabel like '%F4576-百家湖国际花园1%'