SQLitePerl

安装

    SQLite3可使用Perl DBI模块与Perl进行集成。Perl DBI 模块是Perl编程语言的数据库访问模块。它定义了一组提供标准库接口的方法、变量以及规则。

    下面显示了在Linux/UNIX机器上安装DBI模块的简单步骤&＃xff1a;

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625
$ perl Makefile.PL
$ make
$ make install

   如果你需要为DBI安装SQLite驱动程序&＃xff0c;那么可按照以下步骤进行安装&＃xff1a;

$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz
$ tar xvfz DBD-SQLite-1.11.tar.gz
$ cd DBD-SQLite-1.11
$ perl Makefile.PL
$ make
$ make install

DBI 接口 API

连接数据库

#!/usr/bin/perluse DBI;
use strict;my $driver &＃61; "SQLite";
my $database &＃61; "test.db";
my $dsn &＃61; "DBI:$driver:dbname&＃61;$database";
my $userid &＃61; "";
my $password &＃61; "";
my $dbh &＃61; DBI->connect($dsn, $userid, $password, { RaiseError &＃61;> 1 }) or die $DBI::errstr;print "Opened database successfully\n";

创建表

#!/usr/bin/perluse DBI;
use strict;my $driver &＃61; "SQLite";
my $database &＃61; "test.db";
my $dsn &＃61; "DBI:$driver:dbname&＃61;$database";
my $userid &＃61; "";
my $password &＃61; "";
my $dbh &＃61; DBI->connect($dsn, $userid, $password, { RaiseError &＃61;> 1 })or die $DBI::errstr;
print "Opened database successfully\n";my $stmt &＃61; qq(CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL););
my $rv &＃61; $dbh->do($stmt);
if($rv <0){print $DBI::errstr;
} else {print "Table created successfully\n";
}
$dbh->disconnect();

INSERT 操作

#!/usr/bin/perluse DBI;
use strict;my $driver &＃61; "SQLite";
my $database &＃61; "test.db";
my $dsn &＃61; "DBI:$driver:dbname&＃61;$database";
my $userid &＃61; "";
my $password &＃61; "";
my $dbh &＃61; DBI->connect($dsn, $userid, $password, { RaiseError &＃61;> 1 })or die $DBI::errstr;
print "Opened database successfully\n";my $stmt &＃61; qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, &＃39;Paul&＃39;, 32, &＃39;California&＃39;, 20000.00 ));
my $rv &＃61; $dbh->do($stmt) or die $DBI::errstr;$stmt &＃61; qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (2, &＃39;Allen&＃39;, 25, &＃39;Texas&＃39;, 15000.00 ));
$rv &＃61; $dbh->do($stmt) or die $DBI::errstr;$stmt &＃61; qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (3, &＃39;Teddy&＃39;, 23, &＃39;Norway&＃39;, 20000.00 ));
$rv &＃61; $dbh->do($stmt) or die $DBI::errstr;$stmt &＃61; qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (4, &＃39;Mark&＃39;, 25, &＃39;Rich-Mond &＃39;, 65000.00 ););
$rv &＃61; $dbh->do($stmt) or die $DBI::errstr;print "Records created successfully\n";
$dbh->disconnect();

SELECT 操作

#!/usr/bin/perluse DBI;
use strict;my $driver &＃61; "SQLite";
my $database &＃61; "test.db";
my $dsn &＃61; "DBI:$driver:dbname&＃61;$database";
my $userid &＃61; "";
my $password &＃61; "";
my $dbh &＃61; DBI->connect($dsn, $userid, $password, { RaiseError &＃61;> 1 })or die $DBI::errstr;
print "Opened database successfully\n";my $stmt &＃61; qq(SELECT id, name, address, salary from COMPANY;);
my $sth &＃61; $dbh->prepare( $stmt );
my $rv &＃61; $sth->execute() or die $DBI::errstr;
if($rv <0){print $DBI::errstr;
}
while(my &＃64;row &＃61; $sth->fetchrow_array()) {print "ID &＃61; ". $row[0] . "\n";print "NAME &＃61; ". $row[1] ."\n";print "ADDRESS &＃61; ". $row[2] ."\n";print "SALARY &＃61; ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

UPDATE 操作

#!/usr/bin/perluse DBI;
use strict;my $driver &＃61; "SQLite";
my $database &＃61; "test.db";
my $dsn &＃61; "DBI:$driver:dbname&＃61;$database";
my $userid &＃61; "";
my $password &＃61; "";
my $dbh &＃61; DBI->connect($dsn, $userid, $password, { RaiseError &＃61;> 1 })or die $DBI::errstr;
print "Opened database successfully\n";my $stmt &＃61; qq(UPDATE COMPANY set SALARY &＃61; 25000.00 where ID&＃61;1;);
my $rv &＃61; $dbh->do($stmt) or die $DBI::errstr;
if( $rv <0 ){print $DBI::errstr;
}else{print "Total number of rows updated : $rv\n";
}
$stmt &＃61; qq(SELECT id, name, address, salary from COMPANY;);
my $sth &＃61; $dbh->prepare( $stmt );
$rv &＃61; $sth->execute() or die $DBI::errstr;
if($rv <0){print $DBI::errstr;
}
while(my &＃64;row &＃61; $sth->fetchrow_array()) {print "ID &＃61; ". $row[0] . "\n";print "NAME &＃61; ". $row[1] ."\n";print "ADDRESS &＃61; ". $row[2] ."\n";print "SALARY &＃61; ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

DELETE 操作

#!/usr/bin/perluse DBI;
use strict;my $driver &＃61; "SQLite";
my $database &＃61; "test.db";
my $dsn &＃61; "DBI:$driver:dbname&＃61;$database";
my $userid &＃61; "";
my $password &＃61; "";
my $dbh &＃61; DBI->connect($dsn, $userid, $password, { RaiseError &＃61;> 1 })or die $DBI::errstr;
print "Opened database successfully\n";my $stmt &＃61; qq(DELETE from COMPANY where ID&＃61;2;);
my $rv &＃61; $dbh->do($stmt) or die $DBI::errstr;
if( $rv <0 ){print $DBI::errstr;
}else{print "Total number of rows deleted : $rv\n";
}
$stmt &＃61; qq(SELECT id, name, address, salary from COMPANY;);
my $sth &＃61; $dbh->prepare( $stmt );
$rv &＃61; $sth->execute() or die $DBI::errstr;
if($rv <0){print $DBI::errstr;
}
while(my &＃64;row &＃61; $sth->fetchrow_array()) {print "ID &＃61; ". $row[0] . "\n";print "NAME &＃61; ". $row[1] ."\n";print "ADDRESS &＃61; ". $row[2] ."\n";print "SALARY &＃61; ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();