2010-05-11

Perl + SQLite3

• [專案] 實作 Perl + SQLite
  • 其他參考連結
  • [參考] ​SQLite examples with Bash, Perl and Python (2007-03-01)
  • 環境: Debian 5.0.4 , amd64 核心

    jazz@drbl:~$ lsb_release -a
    No LSB modules are available.
    Distributor ID: Debian
    Description:    Debian GNU/Linux 5.0.4 (lenny)
    Release:        5.0.4
    Codename:       lenny
    jazz@drbl:~$ uname -a
    Linux drbl 2.6.26-lustre-amd64 #1 SMP Sun Jun 28 18:28:02 CST 2009 x86_64 GNU/Linux
    

  • 相關套件: ​sqlite3,​libsqlite3-0,​libdbd-sqlite3-perl

    jazz@drbl:~$ sudo apt-get install sqlite3 libdbd-sqlite3-perl libsqlite3-0
    jazz@drbl:~$ dpkg -l | grep sqlite
    ii  libdbd-sqlite3-perl              1.14-3                         Perl DBI driver with a self-contained RDBMS
    ii  libsqlite3-0                     3.5.9-6                        SQLite 3 shared library
    ii  sqlite3                          3.5.9-6                        A command line interface for SQLite 3
    

    • 使用範例程式建立 test.db

      jazz@drbl:~$ vi perlsqlite3.pl
      jazz@drbl:~$ chmod a+x perlsqlite3.pl
      jazz@drbl:~$ ./perlsqlite3.pl
      jazz@drbl:~$ file test.db
      test.db: SQLite 3.x database
      jazz@drbl:~$ sqlite3 test.db "select * from n"
      1|john|smith
      

      #!/usr/bin/perl -w
      use DBI;
      use strict;
      my $db = DBI->connect("dbi:SQLite:test.db", "", "",
      {RaiseError => 1, AutoCommit => 1});
      
      $db->do("CREATE TABLE n (id INTEGER PRIMARY KEY, f TEXT, l TEXT)");
      $db->do("INSERT INTO n VALUES (NULL, 'john', 'smith')");
      my $all = $db->selectall_arrayref("SELECT * FROM n");
      
      foreach my $row (@$all) {
      my ($id, $first, $last) = @$row;
      print "$id|$first|$last";
      }