Ruby 數(shù)據(jù)庫訪問 - DBI 教程

DBI 應(yīng)用架構(gòu)

DBI 獨(dú)立于任何在后臺(tái)中可用的數(shù)據(jù)庫。無論您使用的是 Oracle、MySQL、Informix，您都可以使用 DBI。下面的架構(gòu)圖清晰地說明了這點(diǎn)。

Ruby DBI 一般的架構(gòu)使用兩個(gè)層：

• 數(shù)據(jù)庫接口（DBI）層。該層是獨(dú)立于數(shù)據(jù)庫，并提供了一系列公共訪問方法，方法的使用不分?jǐn)?shù)據(jù)庫服務(wù)器類型。
• 數(shù)據(jù)庫驅(qū)動(dòng)（DBD）層。該層是依賴于數(shù)據(jù)庫，不同的驅(qū)動(dòng)提供了對(duì)不同的數(shù)據(jù)庫引擎的訪問。MySQL、PostgreSQL、InterBase、Oracle 等分別使用不同的驅(qū)動(dòng)。每個(gè)驅(qū)動(dòng)都負(fù)責(zé)解釋來自 DBI 層的請(qǐng)求，并把這些請(qǐng)求映射為適用于給定類型的數(shù)據(jù)庫服務(wù)器的請(qǐng)求。

先決條件

如果您想要編寫 Ruby 腳本來訪問 MySQL 數(shù)據(jù)庫，您需要先安裝 Ruby MySQL 模塊。

該模塊是一個(gè) DBD，可從 http://www.tmtm.org/en/mysql/ruby/ 上下載。

獲取并安裝 Ruby/DBI

您可以從下面的鏈接下載并安裝 Ruby DBI 模塊：

http://rubyforge.org/projects/ruby-dbi/

在開始安裝之前，請(qǐng)確保您擁有 root 權(quán)限?，F(xiàn)在，請(qǐng)安裝下面的步驟進(jìn)行安裝：

步驟 1

 $ tar zxf dbi-0.2.0.tar.gz

步驟 2

進(jìn)入目錄 dbi-0.2.0，在目錄中使用 setup.rb 腳本進(jìn)行配置。最常用的配置命令是 config 參數(shù)后不跟任何參數(shù)。該命令默認(rèn)配置為安裝所有的驅(qū)動(dòng)。

$ ruby setup.rb config

更具體地，您可以使用 --with 選項(xiàng)來列出了您要使用的特定部分。例如，如果只想配置主要的 DBI 模塊和 MySQL DBD 層驅(qū)動(dòng)，請(qǐng)輸入下面的命令：

$ ruby setup.rb config --with=dbi,dbd_mysql

步驟 3

最后一步是建立驅(qū)動(dòng)器，使用下面命令進(jìn)行安裝：

$ ruby setup.rb setup
$ ruby setup.rb install

數(shù)據(jù)庫連接

假設(shè)我們使用的是 MySQL 數(shù)據(jù)庫，在連接數(shù)據(jù)庫之前，請(qǐng)確保：

• 您已經(jīng)創(chuàng)建了一個(gè)數(shù)據(jù)庫 TESTDB。
• 您已經(jīng)在 TESTDB 中創(chuàng)建了表 EMPLOYEE。
• 該表帶有字段 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。
• 設(shè)置用戶 ID "testuser" 和密碼 "test123" 來訪問 TESTDB
• 已經(jīng)在您的機(jī)器上正確地安裝了 Ruby 模塊 DBI。
• 您已經(jīng)看過 MySQL 教程，理解了 MySQL 基礎(chǔ)操作。

下面是連接 MySQL 數(shù)據(jù)庫 "TESTDB" 的實(shí)例：

    #!/usr/bin/ruby -w

    require "dbi"

    begin
         # 連接到 MySQL 服務(wù)器
         dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                            "testuser", "test123")
         # 獲取服務(wù)器版本字符串，并顯示
         row = dbh.select_one("SELECT VERSION()")
         puts "Server version: " + row[0]
    rescue DBI::DatabaseError => e
         puts "An error occurred"
         puts "Error code:    #{e.err}"
         puts "Error message: #{e.errstr}"
    ensure
         # 斷開與服務(wù)器的連接
         dbh.disconnect if dbh
    end

當(dāng)運(yùn)行這段腳本時(shí)，將會(huì)在 Linux 機(jī)器上產(chǎn)生以下結(jié)果。

    Server version: 5.0.45

如果建立連接時(shí)帶有數(shù)據(jù)源，則返回?cái)?shù)據(jù)庫句柄（Database Handle），并保存到 dbh 中以便后續(xù)使用，否則 dbh 將被設(shè)置為 nil 值，e.err 和 e::errstr 分別返回錯(cuò)誤代碼和錯(cuò)誤字符串。

最后，在退出這段程序之前，請(qǐng)確保關(guān)閉數(shù)據(jù)庫連接，釋放資源。

INSERT 操作

當(dāng)您想要在數(shù)據(jù)庫表中創(chuàng)建記錄時(shí)，需要用到 INSERT 操作。

一旦建立了數(shù)據(jù)庫連接，我們就可以準(zhǔn)備使用 do 方法或 prepare 和 execute 方法創(chuàng)建表或創(chuàng)建插入數(shù)據(jù)表中的記錄。

使用 do 語句

不返回行的語句可通過調(diào)用 do 數(shù)據(jù)庫處理方法。該方法帶有一個(gè)語句字符串參數(shù)，并返回該語句所影響的行數(shù)。

    dbh.do("DROP TABLE IF EXISTS EMPLOYEE")
    dbh.do("CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,
         SEX CHAR(1),
         INCOME FLOAT )" );

同樣地，您可以執(zhí)行 SQL INSERT 語句來創(chuàng)建記錄插入 EMPLOYEE 表中。

    #!/usr/bin/ruby -w

    require "dbi"

    begin
         # 連接到 MySQL 服務(wù)器
         dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                            "testuser", "test123")
         dbh.do( "INSERT INTO EMPLOYEE(FIRST_NAME,
                       LAST_NAME,
                       AGE,
               SEX,
               INCOME)
              VALUES ('Mac', 'Mohan', 20, 'M', 2000)" )
         puts "Record has been created"
         dbh.commit
    rescue DBI::DatabaseError => e
         puts "An error occurred"
         puts "Error code:    #{e.err}"
         puts "Error message: #{e.errstr}"
         dbh.rollback
    ensure
         # 斷開與服務(wù)器的連接
         dbh.disconnect if dbh
    end

使用 prepare 和 execute

您可以使用 DBI 的 prepare 和 execute 方法來執(zhí)行 Ruby 代碼中的 SQL 語句。

創(chuàng)建記錄的步驟如下：

• 準(zhǔn)備帶有 INSERT 語句的 SQL 語句。這將通過使用 prepare 方法來完成。
• 執(zhí)行 SQL 查詢，從數(shù)據(jù)庫中選擇所有的結(jié)果。這將通過使用 execute 方法來完成。
• 釋放語句句柄。這將通過使用 finish API 來完成。
• 如果一切進(jìn)展順利，則 commit 該操作，否則您可以 rollback 完成交易。

下面是使用這兩種方法的語法：

    sth = dbh.prepare(statement)
    sth.execute
       ... zero or more SQL operations ...
    sth.finish

這兩種方法可用于傳 bind 值給 SQL 語句。有時(shí)候被輸入的值可能未事先給出，在這種情況下，則會(huì)用到綁定值。使用問號(hào)（?）替代實(shí)際值，實(shí)際值通過 execute() API 來傳遞。

下面的實(shí)例在 EMPLOYEE 表中創(chuàng)建了兩個(gè)記錄：

    #!/usr/bin/ruby -w

    require "dbi"

    begin
         # 連接到 MySQL 服務(wù)器
         dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                            "testuser", "test123")
         sth = dbh.prepare( "INSERT INTO EMPLOYEE(FIRST_NAME,
                       LAST_NAME,
                       AGE,
               SEX,
               INCOME)
                       VALUES (?, ?, ?, ?, ?)" )
         sth.execute('John', 'Poul', 25, 'M', 2300)
         sth.execute('Zara', 'Ali', 17, 'F', 1000)
         sth.finish
         dbh.commit
         puts "Record has been created"
    rescue DBI::DatabaseError => e
         puts "An error occurred"
         puts "Error code:    #{e.err}"
         puts "Error message: #{e.errstr}"
         dbh.rollback
    ensure
         # 斷開與服務(wù)器的連接
         dbh.disconnect if dbh
    end

如果同時(shí)使用多個(gè) INSERT，那么先準(zhǔn)備一個(gè)語句，然后在一個(gè)循環(huán)中多次執(zhí)行它要比通過循環(huán)每次調(diào)用 do 有效率得多。

READ 操作

對(duì)任何數(shù)據(jù)庫的 READ 操作是指從數(shù)據(jù)庫中獲取有用的信息。

一旦建立了數(shù)據(jù)庫連接，我們就可以準(zhǔn)備查詢數(shù)據(jù)庫。我們可以使用 do 方法或 prepare 和 execute 方法從數(shù)據(jù)庫表中獲取值。

獲取記錄的步驟如下：

• 基于所需的條件準(zhǔn)備 SQL 查詢。這將通過使用 prepare 方法來完成。
• 執(zhí)行 SQL 查詢，從數(shù)據(jù)庫中選擇所有的結(jié)果。這將通過使用 execute 方法來完成。
• 逐一獲取結(jié)果，并輸出這些結(jié)果。這將通過使用 fetch 方法來完成。
• 釋放語句句柄。這將通過使用 finish 方法來完成。

下面的實(shí)例從 EMPLOYEE 表中查詢所有工資（salary）超過 1000 的記錄。

    #!/usr/bin/ruby -w

    require "dbi"

    begin
         # 連接到 MySQL 服務(wù)器
         dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                            "testuser", "test123")
         sth = dbh.prepare("SELECT * FROM EMPLOYEE
                            WHERE INCOME > ?")
         sth.execute(1000)

         sth.fetch do |row|
            printf "First Name: %s, Last Name : %sn", row[0], row[1]
            printf "Age: %d, Sex : %sn", row[2], row[3]
            printf "Salary :%d nn", row[4]
         end
         sth.finish
    rescue DBI::DatabaseError => e
         puts "An error occurred"
         puts "Error code:    #{e.err}"
         puts "Error message: #{e.errstr}"
    ensure
         # 斷開與服務(wù)器的連接
         dbh.disconnect if dbh
    end

這將產(chǎn)生以下結(jié)果：

    First Name: Mac, Last Name : Mohan
    Age: 20, Sex : M
    Salary :2000

    First Name: John, Last Name : Poul
    Age: 25, Sex : M
    Salary :2300

還有很多從數(shù)據(jù)庫獲取記錄的方法，如果您感興趣，可以查看 Ruby DBI Read 操作。

Update 操作

對(duì)任何數(shù)據(jù)庫的 UPDATE 操作是指更新數(shù)據(jù)庫中一個(gè)或多個(gè)已有的記錄。下面的實(shí)例更新 SEX 為 'M' 的所有記錄。在這里，我們將把所有男性的 AGE 增加一歲。這將分為三步：

• 基于所需的條件準(zhǔn)備 SQL 查詢。這將通過使用 prepare 方法來完成。
• 執(zhí)行 SQL 查詢，從數(shù)據(jù)庫中選擇所有的結(jié)果。這將通過使用 execute 方法來完成。
• 釋放語句句柄。這將通過使用 finish 方法來完成。
• 如果一切進(jìn)展順利，則 commit 該操作，否則您可以 rollback 完成交易。

    #!/usr/bin/ruby -w

    require "dbi"

    begin
         # 連接到 MySQL 服務(wù)器
         dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                            "testuser", "test123")
         sth = dbh.prepare("UPDATE EMPLOYEE SET AGE = AGE + 1
                            WHERE SEX = ?")
         sth.execute('M')
         sth.finish
         dbh.commit
    rescue DBI::DatabaseError => e
         puts "An error occurred"
         puts "Error code:    #{e.err}"
         puts "Error message: #{e.errstr}"
         dbh.rollback
    ensure
         # 斷開與服務(wù)器的連接
         dbh.disconnect if dbh
    end

DELETE 操作

當(dāng)您想要從數(shù)據(jù)庫中刪除記錄時(shí)，需要用到 DELETE 操作。下面的實(shí)例從 EMPLOYEE 中刪除 AGE 超過 20 的所有記錄。該操作的步驟如下：

• 基于所需的條件準(zhǔn)備 SQL 查詢。這將通過使用 prepare 方法來完成。
• 執(zhí)行 SQL 查詢，從數(shù)據(jù)庫中刪除所需的記錄。這將通過使用 execute 方法來完成。
• 釋放語句句柄。這將通過使用 finish 方法來完成。
• 如果一切進(jìn)展順利，則 commit 該操作，否則您可以 rollback 完成交易。

    #!/usr/bin/ruby -w

    require "dbi"

    begin
         # 連接到 MySQL 服務(wù)器
         dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                            "testuser", "test123")
         sth = dbh.prepare("DELETE FROM EMPLOYEE
                            WHERE AGE > ?")
         sth.execute(20)
         sth.finish
         dbh.commit
    rescue DBI::DatabaseError => e
         puts "An error occurred"
         puts "Error code:    #{e.err}"
         puts "Error message: #{e.errstr}"
         dbh.rollback
    ensure
         # 斷開與服務(wù)器的連接
         dbh.disconnect if dbh
    end

執(zhí)行事務(wù)

事務(wù)是一種確保交易一致性的機(jī)制。事務(wù)應(yīng)具有下列四種屬性：

• 原子性（Atomicity）：事務(wù)的原子性指的是，事務(wù)中包含的程序作為數(shù)據(jù)庫的邏輯工作單位，它所做的對(duì)數(shù)據(jù)修改操作要么全部執(zhí)行，要么完全不執(zhí)行。
• 一致性（Consistency）：事務(wù)的一致性指的是在一個(gè)事務(wù)執(zhí)行之前和執(zhí)行之后數(shù)據(jù)庫都必須處于一致性狀態(tài)。假如數(shù)據(jù)庫的狀態(tài)滿足所有的完整性約束，就說該數(shù)據(jù)庫是一致的。
• 隔離性（Isolation）：事務(wù)的隔離性指并發(fā)的事務(wù)是相互隔離的，即一個(gè)事務(wù)內(nèi)部的操作及正在操作的數(shù)據(jù)必須封鎖起來，不被其它企圖進(jìn)行修改的事務(wù)看到。
• 持久性（Durability）：事務(wù)的持久性意味著當(dāng)系統(tǒng)或介質(zhì)發(fā)生故障時(shí)，確保已提交事務(wù)的更新不能丟失。即一旦一個(gè)事務(wù)提交，它對(duì)數(shù)據(jù)庫中數(shù)據(jù)的改變應(yīng)該是永久性的，耐得住任何數(shù)據(jù)庫系統(tǒng)故障。持久性通過數(shù)據(jù)庫備份和恢復(fù)來保證。

DBI 提供了兩種執(zhí)行事務(wù)的方法。一種是 commit 或 rollback 方法，用于提交或回滾事務(wù)。還有一種是 transaction 方法，可用于實(shí)現(xiàn)事務(wù)。接下來我們來介紹這兩種簡(jiǎn)單的實(shí)現(xiàn)事務(wù)的方法：

方法 I

第一種方法使用 DBI 的 commit 和 rollback 方法來顯式地提交或取消事務(wù)：

       dbh['AutoCommit'] = false # 設(shè)置自動(dòng)提交為 false.
       begin
         dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
                 WHERE FIRST_NAME = 'John'")
         dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
                 WHERE FIRST_NAME = 'Zara'")
         dbh.commit
       rescue
         puts "transaction failed"
         dbh.rollback
       end
       dbh['AutoCommit'] = true

方法 II

第二種方法使用 transaction 方法。這個(gè)方法相對(duì)簡(jiǎn)單些，因?yàn)樗枰粋€(gè)包含構(gòu)成事務(wù)語句的代碼塊。transaction 方法執(zhí)行塊，然后根據(jù)塊是否執(zhí)行成功，自動(dòng)調(diào)用 commit 或 rollback：

       dbh['AutoCommit'] = false # 設(shè)置自動(dòng)提交為 false
       dbh.transaction do |dbh|
         dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
                 WHERE FIRST_NAME = 'John'")
         dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
                 WHERE FIRST_NAME = 'Zara'")
       end
       dbh['AutoCommit'] = true

COMMIT 操作

Commit 是一種標(biāo)識(shí)數(shù)據(jù)庫已完成更改的操作，在這個(gè)操作后，所有的更改都不可恢復(fù)。

下面是一個(gè)調(diào)用 commit 方法的簡(jiǎn)單實(shí)例。

         dbh.commit

ROLLBACK 操作

如果您不滿意某個(gè)或某幾個(gè)更改，您想要完全恢復(fù)這些更改，則使用 rollback 方法。

下面是一個(gè)調(diào)用 rollback 方法的簡(jiǎn)單實(shí)例。

         dbh.rollback

如需斷開數(shù)據(jù)庫連接，請(qǐng)使用 disconnect API。

        dbh.disconnect

斷開數(shù)據(jù)庫

如果用戶通過 disconnect 方法關(guān)閉了數(shù)據(jù)庫連接，DBI 會(huì)回滾所有未完成的事務(wù)。但是，不需要依賴于任何 DBI 的實(shí)現(xiàn)細(xì)節(jié)，您的應(yīng)用程序就能很好地顯式調(diào)用 commit 或 rollback。

處理錯(cuò)誤

有許多不同的錯(cuò)誤來源。比如在執(zhí)行 SQL 語句時(shí)的語法錯(cuò)誤，或者是連接失敗，又或者是對(duì)一個(gè)已經(jīng)取消的或完成的語句句柄調(diào)用 fetch 方法。

如果某個(gè) DBI 方法失敗，DBI 會(huì)拋出異常。DBI 方法會(huì)拋出任何類型的異常，但是最重要的兩種異常類是 DBI::InterfaceError 和 DBI::DatabaseError。

這些類的 Exception 對(duì)象有 err、errstr 和 state 三種屬性，分表代表了錯(cuò)誤號(hào)、一個(gè)描述性的錯(cuò)誤字符串和一個(gè)標(biāo)準(zhǔn)的錯(cuò)誤代碼。屬性具體說明如下：

• err：返回所發(fā)生的錯(cuò)誤的整數(shù)表示法，如果 DBD 不支持則返回 nil。例如，Oracle DBD 返回 ORA-XXXX 錯(cuò)誤消息的數(shù)字部分。
• errstr：返回所發(fā)生的錯(cuò)誤的字符串表示法。
• state：返回所發(fā)生的錯(cuò)誤的 SQLSTATE 代碼。SQLSTATE 是五字符長(zhǎng)度的字符串。大多數(shù)的 DBD 并不支持它，所以會(huì)返回 nil。

在上面的實(shí)例中您已經(jīng)看過下面的代碼：

    rescue DBI::DatabaseError => e
         puts "An error occurred"
         puts "Error code:    #{e.err}"
         puts "Error message: #{e.errstr}"
         dbh.rollback
    ensure
         # 斷開與服務(wù)器的連接
         dbh.disconnect if dbh
    end

為了獲取腳本執(zhí)行時(shí)有關(guān)腳本執(zhí)行內(nèi)容的調(diào)試信息，您可以啟用跟蹤。為此，您必須首先下載 dbi/trace 模塊，然后調(diào)用控制跟蹤模式和輸出目的地的 trace 方法：

    require "dbi/trace"
    ..............

    trace(mode, destination)

mode 的值可以是 0（off）、1、2 或 3，destination 的值應(yīng)該是一個(gè) IO 對(duì)象。默認(rèn)值分別是 2 和 STDERR。

方法的代碼塊

有一些創(chuàng)建句柄的方法。這些方法通過代碼塊調(diào)用。使用帶有方法的代碼塊的優(yōu)點(diǎn)是，它們?yōu)榇a塊提供了句柄作為參數(shù)，當(dāng)塊終止時(shí)會(huì)自動(dòng)清除句柄。下面是一些實(shí)例，有助于理解這個(gè)概念。

• DBI.connect ：該方法生成一個(gè)數(shù)據(jù)庫句柄，建議在塊的末尾調(diào)用 disconnect 來斷開數(shù)據(jù)庫。
• dbh.prepare ：該方法生成一個(gè)語句句柄，建議在塊的末尾調(diào)用 finish。在塊內(nèi)，您必須調(diào)用 execute 方法來執(zhí)行語句。
• dbh.execute ：該方法與 dbh.prepare 類似，但是 dbh.execute 不需要在塊內(nèi)調(diào)用 execute 方法。語句句柄會(huì)自動(dòng)執(zhí)行。

實(shí)例 1

DBI.connect 可帶有一個(gè)代碼塊，向它傳遞數(shù)據(jù)庫句柄，且會(huì)在塊的末尾自動(dòng)斷開句柄。

    dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                       "testuser", "test123") do |dbh|

實(shí)例 2

dbh.prepare 可帶有一個(gè)代碼塊，向它傳遞語句句柄，且會(huì)在塊的末尾自動(dòng)調(diào)用 finish。

    dbh.prepare("SHOW DATABASES") do |sth|
           sth.execute
           puts "Databases: " + sth.fetch_all.join(", ")
    end

實(shí)例 3

dbh.execute 可帶有一個(gè)代碼塊，向它傳遞語句句柄，且會(huì)在塊的末尾自動(dòng)調(diào)用 finish。

    dbh.execute("SHOW DATABASES") do |sth|
       puts "Databases: " + sth.fetch_all.join(", ")
    end

DBI transaction 方法也可帶有一個(gè)代碼塊，這在上面的章節(jié)中已經(jīng)講解過了。

特定驅(qū)動(dòng)程序的函數(shù)和屬性

DBI 讓數(shù)據(jù)庫驅(qū)動(dòng)程序提供了額外的特定數(shù)據(jù)庫的函數(shù)，這些函數(shù)可被用戶通過任何 Handle 對(duì)象的 func 方法進(jìn)行調(diào)用。

使用 []= or [] 方法可以設(shè)置或獲取特定驅(qū)動(dòng)程序的屬性。

DBD::Mysql 實(shí)現(xiàn)了下列特定驅(qū)動(dòng)程序的函數(shù)：

實(shí)例

    #!/usr/bin/ruby

    require "dbi"
    begin
       # 連接到 MySQL 服務(wù)器
       dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                            "testuser", "test123")
       puts dbh.func(:client_info)
       puts dbh.func(:client_version)
       puts dbh.func(:host_info)
       puts dbh.func(:proto_info)
       puts dbh.func(:server_info)
       puts dbh.func(:thread_id)
       puts dbh.func(:stat)
    rescue DBI::DatabaseError => e
       puts "An error occurred"
       puts "Error code:    #{e.err}"
       puts "Error message: #{e.errstr}"
    ensure
       dbh.disconnect if dbh
    end

這將產(chǎn)生以下結(jié)果：

    5.0.45
    50045
    Localhost via UNIX socket
    10
    5.0.45
    150621
    Uptime: 384981  Threads: 1  Questions: 1101078  Slow queries: 4
    Opens: 324  Flush tables: 1  Open tables: 64
    Queries per second avg: 2.860