Perl模块推荐14_DBI

摘要

键值对的数据结构,HASH首当其中,能够非常方便的根据键找到其对应的值。然后hash是存放在内存中的,当键值对超过100000条,不可避免就会出现out of memory。这时数据库就可以充当硬盘版的hash,拜托内存的限制。

hash 版的键值对的查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

#!/usr/bin/perl -w

use strict;
#contact perlcoder weixin
my %hash;
while(<DATA>)
{
if($_=~/\S+/)
{

}
else
{
next;
}
chomp;
my ($key)=$_=~/>(\S+)/;
my $value=<DATA>;
chomp($value);
$hash{$key}=$value;
}

print $hash{'Gh_A04G0739'},"\n";


__DATA__
>Gh_A12G2559
ATGGCTACTTTCTTTGGCTATTTTAC
>Gh_A12G2554
ATGGCGGGAACTATCCAATCCCTAAT
>Gh_A04G0755
ATGGCCTCCGATCAGACCCTTTTTCA
>Gh_A04G0739
ATGGAGAAAGCAAAACCTGAAGCACC
>Gh_A04G0738
ATGGCGCAGGTTTTAGACGACGCTGA
>Gh_A07G1346
ATGGTTCATTGTCTGCCAAAGGTTTC
>Gh_A07G1331
ATGGCAGACAAGGATTCTTCAAGGCC
>Gh_A07G1334
ATGACGACGCCAACTCGAGATGCAAT
>Gh_A07G1335
ATGGCCGCAACTAGATTCCTCTCTCA
>Gh_A08G1510
ATGGCTACTGCACCGATAAAGTCTCA
>Gh_A08G1433
ATGGGTAAAACACCTACTGGCAAGGA

perl 数据库

DBI 中有多种数据库引擎,包括。这里以SQLite为例进行介绍,

SQLite模块定义的“数据库”是存在于单个文件中的,把单个文件仿真为一个数据库。
SQLite模块定义的“数据库”是存在于单个文件中的,把单个文件仿真为一个数据库。
SQLite定义的数据库里面没有数据类型这个概念。不管你在创建一个表的时候指定的是什么数据类型,以后你可以在其中放入任何类型的数值(包括字符型,数字型,日期型,二进制对象/blob)。。实际上,创建表的时候你甚至可以不指定数据类型。 CREATE TABLE people ( id, name, age); 这和perl非常像。

  • step1 创建数据库和表格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
#!/usr/bin/perl -w


use strict;
use DBI;

my @drivers=DBI->available_drivers;

#print join("-",@drivers);

#[create] and connect a database

my $db='FASTA.db';
my $dbh=DBI->connect("DBI:SQLite:$db") or die "can't connect FASTA\n";
print "Opened database successfully\n";
#create table




my $command = qq(CREATE TABLE fasta2016
(ID CHAR(50) PRIMARY KEY NOT NULL,
CONTENT TEXT NOT NULL
););



my $rv = $dbh->do($command);

if($rv < 0)
{
print $DBI::errstr;
} else
{
print "Table created successfully\n";
}
$dbh->disconnect();

  • step2 填充数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
#!/usr/bin/perl -w


use strict;
use DBI;

my @drivers=DBI->available_drivers;

#print join("-",@drivers);

#[create] and connect a database

my $db='FASTA.db';
my $dbh=DBI->connect("DBI:SQLite:$db") or die "can't connect FASTA\n";

#INSERT Table
my $sth=$dbh->prepare("INSERT INTO fasta2016(ID,CONTENT)
VALUES(?,?)");

#open FH,"test.fasta";
#open FH,"NBI_Gossypium_hirsutum_v1.1.fasta";
my $id=0;
while(<DATA>)
{
$id++;
print $id,"\n";
if($_=~/\S+/)
{

}
else
{
next;
}
chomp;
my ($key)=$_=~/>(\S+)/;
my $value=<DATA>;
print "$key\n";
$sth->execute($key,$value);



}



$dbh->disconnect();

__DATA__
>Gh_A07G1331
ATGGCAGACAAGGATTCTTCAAGGCC
>Gh_A07G1334
ATGACGACGCCAACTCGAGATGCAAT
>Gh_A07G1335
ATGGCCGCAACTAGATTCCTCTCTCA
>Gh_A08G1510
ATGGCTACTGCACCGATAAAGTCTCA
>Gh_A08G1433
ATGGGTAAAACACCTACTGGCAAGGA
  • step3 查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

#!/usr/bin/perl -w

use Data::Dumper;
use strict;
use DBI;

my @drivers=DBI->available_drivers;

#print join("-",@drivers);

#[create] and connect a database

my $db='FASTA.db';
my $dbh=DBI->connect("DBI:SQLite:$db") or die "can't connect FASTA\n";


my $sth;
#$sth=$dbh->prepare("select * FROM fasta2016 ") or die "can not prepare the statement\n";
my $id='Gh_A12G2554';
#$sth->execute($id);
#$sth->execute();
my @results;

#while(@results=$sth->fetchrow_array()){ print $results[0],"\n";}

$sth=$dbh->prepare("select ID,CONTENT FROM fasta2016 where id = ?;") or die "cannot prepare";

$sth->execute($id);


@results=$sth->fetchrow_array();

print "aaa\n";
print join "\n",@results,"AAA\n";




$dbh->disconnect();

参考博客
  1. gunnerlzx http://blog.chinaunix.net/uid-20459533-id-1944446.html