位置:首頁 > 數據庫 > SQLite基礎教程 > SQLite Perl

SQLite Perl

SQLite3 用Perl使用Perl DBI模塊,這是一個Perl編程語言的數據庫訪問模塊可以集成。它定義了一套方法,變數和約定,提供一個標準的數據庫接口。

下麵是簡單的步驟,在Linux /UNIX的機器上安裝DBI模塊:

$ 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 驅動,那麼它可以安裝如下:

$ 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

以下是DBI重要程序,可以滿足你的工作要求從Perl程序操作SQLite數據庫。如果正在尋找一個更複雜的應用程序,那麼可以看看 Perl DBI 官方文檔。

S.N. API & 描述
1 DBI->connect($data_source, "", "", \%attr)

Establishes a database connection, or session, to the requested $data_source. Returns a database handle object if the connection succeeds.

Datasource has the form like : DBI:SQLite:dbname='test.db' SQLite is SQLite driver name and test.db is the name of SQLite database file. If the filename is given as ':memory:', it will create an in-memory database in RAM that lasts only for the duration of the session.

If filename is actual device file name, then it attempts to open the database file by using its value. If no file by that name exists then a new database file by that name gets created.

You keep second and third paramter as blank strings and last parameter is to pass various attributes as shown below in the example.

2 $dbh->do($sql)

This routine prepares and executes a single SQL statement. Returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available. Here $dbh is a handle returned by DBI->connect() call.

3 $dbh->prepare($sql)

This routine prepares a statement for later execution by the database engine and returns a reference to a statement handle object.

4 $sth->execute()

This routine performs whatever processing is necessary to execute the prepared statement. An undef is returned if an error occurs. A successful execute always returns true regardless of the number of rows affected. Here, $sth is a statement handle returned by $dbh->prepare($sql) call.

5 $sth->fetchrow_array()

This routine fetches the next row of data and returns it as a list containing the field values. Null fields are returned as undef values in the list.

6 $DBI::err

This is equivalent to $h->err, where $h is any of the handle types like $dbh, $sth, or $drh. This returns native database engine error code from the last driver method called.

7 $DBI::errstr

This is equivalent to $h->errstr, where $h is any of the handle types like $dbh, $sth, or $drh. This returns the native database engine error message from the last DBI method called.

8 $dbh->disconnect()

This routine closes a database connection previously opened by a call to DBI->connect().

連接到數據庫

下麵的Perl代碼顯示了如何連接到一個現有的數據庫。如果數據庫不存在,那麼它就會被創建,終於將返回一個數據庫對象。

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite"; 
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
                      or die $DBI::errstr;

print "Opened database successfully
";

現在,讓我們運行上麵的程序在當前目錄中,創建數據庫test.db。按要求可以改變你的路徑。上麵的代碼在sqlite.pl文件並執行它,如下圖所示。如果數據庫創建成功,則它會給下麵的消息:

$ chmod +x sqlite.pl
$ ./sqlite.pl
Open database successfully

創建表

下麵的Perl程序將使用以前創建的數據庫中創建一個表:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully
";

my $stmt = 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 = $dbh->do($stmt);
if($rv < 0){
   print $DBI::errstr;
} else {
   print "Table created successfully
";
}
$dbh->disconnect();

上述程序執行時,它會創建表公司test.db,它會顯示以下消息:

Opened database successfully
Table created successfully

注意:任何操作情況下,看到下麵的錯誤:

DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398

在這種情況下,打開 dbdimp.c 的文件可在DBD-SQLite的安裝,並找出sqlite3_prepare()函數,改變它的第三個參數為-1,而不是0。最後安裝DBD:: SQLite的使用make 和make install 來解決問題。 

INSERT 操作

Perl程序,顯示我們如何能夠創造COMPANY在上麵的例子中創建表中的記錄:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully
";

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;

print "Records created successfully
";
$dbh->disconnect();

上述程序執行時,它會創建COMPANY表中的記錄,並顯示以下兩行:

Opened database successfully
Records created successfully

SELECT 操作

Perl 程序,表明我們如何能夠獲取並顯示COMPANY 表在上麵的例子中創建表的記錄:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully
";

my $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0){
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "
";
      print "NAME = ". $row[1] ."
";
      print "ADDRESS = ". $row[2] ."
";
      print "SALARY =  ". $row[3] ."

";
}
print "Operation done successfully
";
$dbh->disconnect();

當上述程序執行時,它會產生以下結果:

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

UPDATE 操作

Perl代碼顯示如何,我們可以使用UPDATE語句來更新任何記錄,然後獲取並顯示更新的記錄,從COMPANY 表:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully
";

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
   print "Total number of rows updated : $rv
";
}
$stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0){
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "
";
      print "NAME = ". $row[1] ."
";
      print "ADDRESS = ". $row[2] ."
";
      print "SALARY =  ". $row[3] ."

";
}
print "Operation done successfully
";
$dbh->disconnect();

當上述程序執行時,它會產生以下結果:

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

DELETE 操作

Perl代碼表明我們如何能夠使用DELETE語句刪除任何記錄,然後獲取並顯示剩餘的記錄COMPANY 表: 

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully
";

my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
   print "Total number of rows deleted : $rv
";
}
$stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0){
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "
";
      print "NAME = ". $row[1] ."
";
      print "ADDRESS = ". $row[2] ."
";
      print "SALARY =  ". $row[3] ."

";
}
print "Operation done successfully
";
$dbh->disconnect();

當上述程序執行時,它會產生以下結果:

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully