使用perl DBI模块操作mysql数据库的方法

发布时间:2020-09-05编辑:脚本学堂
首先,熟悉下几个常用的mysql语法:
登录:mysql -u <usrname> -p<psword>
显示数据库:show databases;
使用数据库:use <database name>;
显示表单:show tables;

首先,熟悉下几个常用的mysql语法:
登录:mysql -u <usrname> -p<psword>
显示数据库:show databases;
使用数据库:use <database name>;
显示表单:show tables;
把数据载入表单:load data infile local .....(具体参照mysql manual);
对表单做index(索引),相当于perl中的HASH,便于快速读取取数据:create index on table <tablename>(<row1>,<row2>,...);
获取数据:select * from <tablename> where <条件>;

再来看一下perl中DBI的简单语法:do,prepare,execute,fetch;
下面的程序做了下面几件事情
Connect To a database
Create a table
Load data into the table
Create index on you target row

例子:
 

复制代码 代码如下:

#!/usr/bin/perl -w
use strict;
use DBI;      #这句就不用说了
my ($tissue,$cpg_forw,$cpg_rev,$chg_forw,$chg_rev,$chh_forw,$chh_rev)=@ARGV;
my ($driver,$dsn,$root,$psword)=("mysql","database=$tissue","root","123456");

#这句当让就是连接数据库,数据库在上面已部分中操作(create database <databasename>;)
my $dbh=DBI->connect("dbi:$driver:$dsn",$root,$psword) or die "$DBI::errstr";
#这句就是建立你的数据对应的表单,注意字符型的使用
   $dbh->do(qq(create table CpG_OT(chrom VARCHAR(11),pos1 INT,pos2 INT,depth INT,meth_lev float)));
#这句就是将你的数据载入表单中
   $dbh->do(qq(load data local infile '$cpg_forw' into table CpG_OT));
#这句就是建立索引,假设不建立索引,在使用select语句的时候,mysql就会行读数据,这样就和perl中行读没区别,而建立索引之后就不用行读,直接可以利用你建立index的列直接锁定你所要提取的行的信息(原理见mysql manual)
   $dbh->do(qq(CREATE INDEX CpG_OT_index ON CpG_OT(chrom,pos1)));

当我们终于建立好了表单以及index之后,我们就可以处理我们的数据了;
这部分主要包括下面程序中的语句,这些语句都是最基础的数据,但是对我们生物信息的部分我感觉已经足够了。
下面我用红色字体标示的就是主要的DBI语句。
下面这个程序蓝色部分是我使用(``)调用了R,这句比system或者exec的好处就是可以将R中结果返回给我们声明的变量接着我们就可以使用正则表达式来提取R帮我们计算的结果了,这个程序中我是用来做spearman rank correlation分析的。
例子:
 

复制代码 代码如下:

#!/usr/bin/perl -w
use strict;use DBI;
die "n",usage(),"n" unless @ARGV==5;
my ($tissue,$forw,$rev,$pos,$out)=@ARGV;
my ($driver,$dsn,$usr,$pswd)=("mysql","database=$tissue","root","123456");
#第一步当然就是连接数据库了;
my $dbh=DBI->connect("dbi:$driver:$dsn",$usr,$pswd) or die "$DBI::errstrn";
open OUT,"+>$out" or die;
open POS,$pos or die;
my %promoter;my %prom_exp;my %termina;my %ter_exp;my %intragenic;my %intra_exp;my $flag=1;my %hash;
while(my $line=<POS>){
    print "$flag have been donen" if $flag00==0;$flag++;
    next if $line!~/gene/;
    chomp $line;
    my ($chr,$ele,$stt,$end,$strand,$name,$rpkm)=(split(/s+/,$line))[0,1,2,3,4,5,6];
  
    $chr="chr".$chr;
    foreach($forw,$rev){
        #下面两句就是准备读取哪些行,然后执行
        my $row=$dbh->prepare(qq(select * from $_ where chrom="$chr" AND pos1>=$stt-1999 AND pos1<=$end+1999));
           $row->execute();
#下面三句就是要拿出数据了,首先声明你几个变量来存你的每行中各列的数据,然后bind_colomns,最后fetch,具体的含义可以参考一些perlDBI的书籍
        my ($chrom,$tem_pos1,$tem_pos2,$depth,$lev)=(0,0,0,0,0);
           $row->bind_columns($chrom,$tem_pos1,$tem_pos2,$depth,$lev);
        while($row->fetch()){
            $hash{"$chromt$tem_pos1"}=$lev;
        }
    }
    if($strand eq "+"){
        &cal_forw($name,$chr,$stt,$end,$strand,$rpkm);
    }else{
        &cal_rev($name,$chr,$stt,$end,$strand,$rpkm);
    }
    %hash=();
}

foreach(sort{$a<=>$b}keys %promoter){
    open R,"+>$forw.R";
    my $meth=join(',',@{$promoter{$_}});
    my $exp=join(',',@{$prom_exp{$_}});
    print R "meth<-c($meth)nexp<-c($exp)ncor.test(meth,exp,method='spearman')";
    my $report=`R --vanilla --slave <$forw.R`;
    my @tem=split(/n/,$report);
    my ($p_value)=$tem[-5]=~/p-values*[><=]s*(.+)/;
    $tem[-1]=~s/s//g;
    my $correla=$tem[-1];
    my $real=0 if $p_value>0.001;
       $real=$correla if $p_value<=0.001;
    print OUT "$_t$p_valuet$correlat$realn";
}
foreach(sort{$a<=>$b}keys %intragenic){
    open R,"+>$forw.R";
    my $meth=join(',',@{$intragenic{$_}});
    my $exp =join(',',@{$intra_exp{$_}});
    print R "meth<-c($meth)nexp<-c($exp)ncor.test(meth,exp,method='spearman')";
    my $report=`R --vanilla --slave <$forw.R`;
    my @tem=split(/n/,$report);
    my ($p_value)=$tem[-5]=~/p-values*[><=]s*(.+)/;
    $tem[-1]=~s/s//g;
    my $correla=$tem[-1];
    my $real=0 if $p_value>0.001;
       $real=$correla if $p_value<=0.001;
    print OUT "$_t$p_valuet$correlat$realn";
}
foreach(sort{$a<=>$b}keys %termina){
    open R,"+>$forw.R";
    my $meth=join(',',@{$termina{$_}});
    my $exp =join(',',@{$ter_exp{$_}});
    print R "meth<-c($meth)nexp<-c($exp)ncor.test(meth,exp,method='spearman')";
    my $report=`R --vanilla --slave <$forw.R`;
    my @tem=split(/n/,$report);
    my ($p_value)=$tem[-5]=~/p-values*[><=]s*(.+)/;
    $tem[-1]=~s/s//g;
    my $correla=$tem[-1];
    my $real=0 if $p_value>0.001;
       $real=$correla if $p_value<=0.001;
    print OUT "$_t$p_valuet$correlat$realn";
}
sub cal_forw{
    my ($name,$chr,$stt,$end,$strand,$rpkm)=@_;
    my ($meth,$report)=(0,0);
        #promoter
        for(my $i=-20;$i<=-1;++$i){
            for(my $j=1;$j<=100;++$j){
                my $key_part=$stt+$i*100+$j;
                if(exists $hash{"$chrt$key_part"}){
                    $meth+=$hash{"$chrt$key_part"};
                    $report++;
                }
            }
            if($report !=0){
                push (@{$promoter{$i}},$meth/$report);
                push (@{$prom_exp{$i}},$rpkm);
            }
        }
        #3' termination
        ($meth,$report)=(0,0);
        for(my $i=0;$i<20;++$i){
            for(my $j=1;$j<=100;++$j){
                my $key_part=$end-$i*100+$j;
                if(exists $hash{"$chrt$key_part"}){
                    $meth+=$hash{"$chrt$key_part"};
                    $report++;
                }
            }
            if($report !=0){
                push (@{$termina{$i+1}},$meth/$report);
                push (@{$ter_exp{$i+1}},$rpkm);
            }
        }
        #gene body
        ($meth,$report)=(0,0);my $uni=($end-$stt)/100;
        for(my $i=0;$i<=99;++$i){
            for(my $j=int ($stt+$i*$uni);$j<=int($stt+($i+1)*$uni);++$j){
                if(exists $hash{"$chrt$j"}){
                        $meth+=$hash{"$chrt$j"};
                        $report++;
                }
            }
            if($report !=0){
                push(@{$intragenic{$i+1}},$meth/$report);
                push(@{$intra_exp{$i+1}},$rpkm);
            }
        }
}

sub cal_rev{
    my ($name,$chr,$stt,$end,$strand,$rpkm)=@_;
    my ($meth,$report)=(0,0);
        #promoter
        for(my $i=-19;$i<=0;++$i){
            for(my $j=1;$j<=100;++$j){
                my $key_part=$end-$i*100+$j;
                if(exists $hash{"$chrt$key_part"}){
                    $meth+=$hash{"$chrt$key_part"};
                    $report++;
                }
            }
            if($report !=0){
                push (@{$promoter{$i-1}},$meth/$report);
                push (@{$prom_exp{$i-1}},$rpkm);
            }
        }
        #3' termination
        ($meth,$report)=(0,0);
        for(my $i=1;$i<=20;++$i){
            for(my $j=1;$j<=100;++$j){
                my $key_part=$stt-$i*100+$j;
                if(exists $hash{"$chrt$key_part"}){
                    $meth+=$hash{"$chrt$key_part"};
                    $report++;
                }
            }
            if($report !=0){
                push (@{$termina{$i}},$meth/$report);
                push (@{$ter_exp{$i}},$rpkm);
            }
        }
        #gene body
        ($meth,$report)=(0,0);my $uni=($end-$stt)/100;
        for(my $i=0;$i<=99;++$i){
            for(my $j=int ($stt+$i*$uni);$j<=int($stt+($i+1)*$uni);++$j){
                if(exists $hash{"$chrt$j"}){
                        $meth+=$hash{"$chrt$j"};
                        $report++;
                }
            }
            if($report !=0){
                push(@{$intragenic{100-$i}},$meth/$report);
                push(@{$intra_exp{100-$i}},$rpkm);
            }
        }
}
sub usage{
    my $die=<<DIE;
    perl *.pl <Tissue> <Forword> <Reverse> <Gene with expression Rank> <OUTPUT>  FGS
    To get the correlation between each bins and methylation
DIE
}

小结:mysql对于处理千万条目级别以上的场景,问题在于索引的负担,增加新的索引、持续插入新数据时的速度叫慢等,即mysql的慢查询。
如果遇到这样的情况,则需要考虑分表了。