SQLite – Perl
安裝
SQLite3 可使用Perl DBI 模塊與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 官方文檔。
序号 | API & 描述 |
---|---|
1 | DBI->connect($data_source, "", "", \%attr) 建立一个到被请求的 $data_source 的数据库连接或者 session。如果连接成功,则返回一个数据库处理对象。 数据源形式如下所示:DBI:SQLite:dbname='test.db'。其中,SQLite 是 SQLite 驱动程序名称,test.db 是 SQLite 数据库文件的名称。如果文件名filename赋值为':memory:',那么它将会在 RAM 中创建一个内存数据库,这只会在 session 的有效时间内持续。 如果文件名 filename 为实际的设备文件名称,那么它将使用这个参数值尝试打开数据库文件。如果该名称的文件不存在,那么将创建一个新的命名为该名称的数据库文件。 您可以保留第二个和第三个参数为空白字符串,最后一个参数用于传递各种属性,详见下面的实例讲解。 |
2 | $dbh->do($sql) 该例程准备并执行一个简单的 SQL 语句。返回受影响的行数,如果发生错误则返回 undef。返回值 -1 意味着行数未知,或不适用 ,或不可用。在这里,$dbh 是由 DBI->connect() 调用返回的处理。 |
3 | $dbh->prepare($sql) 该例程为数据库引擎后续执行准备一个语句,并返回一个语句处理对象。 |
4 | $sth->execute() 该例程执行任何执行预准备的语句需要的处理。如果发生错误则返回 undef。如果成功执行,则无论受影响的行数是多少,总是返回 true。在这里,$sth 是由 $dbh->prepare($sql) 调用返回的语句处理。 |
5 | $sth->fetchrow_array() 该例程获取下一行数据,并以包含各字段值的列表形式返回。在该列表中,Null 字段将作为 undef 值返回。 |
6 | $DBI::err 这相当于 $h->err。其中,$h 是任何的处理类型,比如 $dbh、$sth 或 $drh。该程序返回最后调用的驱动程序(driver)方法的数据库引擎错误代码。 |
7 | $DBI::errstr 这相当于 $h->errstr。其中,$h 是任何的处理类型,比如 $dbh、$sth 或 $drh。该程序返回最后调用的 DBI 方法的数据库引擎错误消息。 |
8 | $dbh->disconnect() 该例程关闭之前调用 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\n";
現在,讓我們來運行上面的程序,在當前目錄中創建我們的數據庫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\n"; 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\n"; } $dbh->disconnect();
上述程序執行時,它會在test.db中創建COMPANY表,並顯示下面所示的消息:
Opened database successfully Table created successfully
注意:如果您在任何操作中遇到了下面的錯誤: in case you see following error in any of the operation:
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398
在這種情況下,您已經在DBD-SQLite安裝中打開了可用的dbdimp.c文件,找到sqlite3_prepare()函數,並把它的第三個參數0改為-1。 最後使用make和make install安裝DBD::SQLite,即可解決問題。 in this case you will have open dbdimp.c file available in DBD-SQLite installation and find outsqlite3_prepare() function and change its third argument to -1 instead of 0. Finally install DBD::SQLite using makeand domake installto resolve the problem.
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\n"; 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\n"; $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\n"; 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] . "\n"; print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n"; print "SALARY = ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $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\n"; 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\n"; } $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] . "\n"; print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n"; print "SALARY = ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $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\n"; 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\n"; } $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] . "\n"; print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n"; print "SALARY = ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $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