在Ruby程式中串連資料庫的詳細教程_ruby專題

本章節將向您講解如何使用 Ruby 訪問資料庫。Ruby DBI 模組為 Ruby 指令碼提供了類似於 Perl DBI 模組的獨立於資料庫的介面。

DBI 即 Database independent interface，代表了 Ruby 獨立於資料庫的介面。DBI 在 Ruby 代碼與底層資料庫之間提供了一個抽象層，允許您簡單地實現資料庫切換。它定義了一系列方法、變數和規範，提供了一個獨立於資料庫的一致的資料庫介面。

DBI 可與下列進行互動：

•     ADO (ActiveX Data Objects)
•     DB2
•     Frontbase
•     mSQL
•     MySQL
•     ODBC
•     Oracle
•     OCI8 (Oracle)
•     PostgreSQL
•     Proxy/Server
•     SQLite
•     SQLRelay

DBI 應用架構

DBI 獨立於任何在後台中可用的資料庫。無論您使用的是 Oracle、MySQL、Informix，您都可以使用 DBI。下面的架構圖清晰地說明了這點。

Ruby DBI 一般的架構使用兩個層：

•     資料庫介面（DBI）層。該層是獨立於資料庫，並提供了一系列公用存取方法，方法的使用不分資料庫伺服器類型。
•     資料庫驅動（DBD）層。該層是依賴於資料庫，不同的驅動提供了對不同的資料庫引擎的訪問。MySQL、PostgreSQL、InterBase、Oracle 等分別使用不同的驅動。每個驅動都負責解釋來自 DBI 層的請求，並把這些請求映射為適用於給定類型的資料庫伺服器的請求。

先決條件

如果您想要編寫 Ruby 指令碼來訪問 MySQL 資料庫，您需要先安裝 Ruby MySQL 模組。

該模組是一個 DBD，可從 http://www.tmtm.org/en/mysql/ruby/ 上下載。
擷取並安裝 Ruby/DBI

您可以從下面的連結下載並安裝 Ruby DBI 模組：

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

在開始安裝之前，請確保您擁有 root 許可權。現在，請安裝下面的步驟進行安裝：
步驟 1

$ tar zxf dbi-0.2.0.tar.gz

步驟 2

進入目錄 dbi-0.2.0，在目錄中使用 setup.rb 指令碼進行配置。最常用的配置命令是 config 參數後不跟任何參數。該命令預設配置為安裝所有的驅動。

$ ruby setup.rb config

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

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

步驟 3

最後一步是建立磁碟機，使用下面命令進行安裝：

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

資料庫連接

假設我們使用的是 MySQL 資料庫，在串連資料庫之前，請確保：

•     您已經建立了一個資料庫 TESTDB。
•     您已經在 TESTDB 中建立了表 EMPLOYEE。
•     該錶帶有欄位 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。
•     設定使用者識別碼 "testuser" 和密碼 "test123" 來訪問 TESTDB
•     已經在您的機器上正確地安裝了 Ruby 模組 DBI。
•     您已經看過 MySQL 教程，理解了 MySQL 基礎操作。

下面是串連 MySQL 資料庫 "TESTDB" 的執行個體：

#!/usr/bin/ruby -w require "dbi" begin   # 串連到 MySQL 伺服器   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",            "testuser", "test123")   # 擷取伺服器版本字串，並顯示   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   # 斷開與伺服器的串連   dbh.disconnect if dbhend

當運行這段指令碼時，將會在 Linux 機器上產生以下結果。
Server version: 5.0.45

如果建立串連時帶有資料來源，則返回資料庫控制代碼（Database Handle），並儲存到 dbh 中以便後續使用，否則 dbh 將被設定為 nil 值，e.err 和 e::errstr 分別返回錯誤碼和錯誤字串。

最後，在退出這段程式之前，請確保關閉資料庫連接，釋放資源。
INSERT 操作

當您想要在資料庫表中建立記錄時，需要用到 INSERT 操作。

一旦建立了資料庫連接，我們就可以準備使用 do 方法或 prepare 和 execute 方法建立表或建立插入資料表中的記錄。
使用 do 語句

不返回行的語句可通過調用 do 資料庫處理方法。該方法帶有一個語句字串參數，並返回該語句所影響的行數。

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 )" );

同樣地，您可以執行 SQL INSERT 語句來建立記錄插入 EMPLOYEE 表中。

#!/usr/bin/ruby -w require "dbi" begin   # 串連到 MySQL 伺服器   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.commitrescue DBI::DatabaseError => e   puts "An error occurred"   puts "Error code:  #{e.err}"   puts "Error message: #{e.errstr}"   dbh.rollbackensure   # 斷開與伺服器的串連   dbh.disconnect if dbhend

使用 prepare 和 execute

您可以使用 DBI 的 prepare 和 execute 方法來執行 Ruby 代碼中的 SQL 陳述式。

建立記錄的步驟如下：

    準備帶有 INSERT 語句的 SQL 陳述式。這將通過使用 prepare 方法來完成。
    執行 SQL 查詢，從資料庫中選擇所有的結果。這將通過使用 execute 方法來完成。
    釋放語句控制代碼。這將通過使用 finish API 來完成。
    如果一切進展順利，則 commit 該操作，否則您可以 rollback 完成交易。

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

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

這兩種方法可用於傳 bind 值給 SQL 陳述式。有時候被輸入的值可能未事先給出，在這種情況下，則會用到綁定值。使用問號（?）替代實際值，實際值通過 execute() API 來傳遞。

下面的執行個體在 EMPLOYEE 表中建立了兩個記錄：

#!/usr/bin/ruby -w require "dbi" begin   # 串連到 MySQL 伺服器   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.rollbackensure   # 斷開與伺服器的串連   dbh.disconnect if dbhend

如果同時使用多個 INSERT，那麼先準備一個語句，然後在一個迴圈中多次執行它要比通過迴圈每次調用 do 有效率得多。
READ 操作

對任何資料庫的 READ 操作是指從資料庫中擷取有用的資訊。

一旦建立了資料庫連接，我們就可以準備查詢資料庫。我們可以使用 do 方法或 prepare 和 execute 方法從資料庫表中擷取值。

擷取記錄的步驟如下：

•     基於所需的條件準備 SQL 查詢。這將通過使用 prepare 方法來完成。
•     執行 SQL 查詢，從資料庫中選擇所有的結果。這將通過使用 execute 方法來完成。
•     逐一擷取結果，並輸出這些結果。這將通過使用 fetch 方法來完成。
•     釋放語句控制代碼。這將通過使用 finish 方法來完成。

下面的執行個體從 EMPLOYEE 表中查詢所有工資（salary）超過 1000 的記錄。

#!/usr/bin/ruby -w require "dbi" begin   # 串連到 MySQL 伺服器   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 : %s\n", row[0], row[1]    printf "Age: %d, Sex : %s\n", row[2], row[3]    printf "Salary :%d \n\n", row[4]   end   sth.finishrescue DBI::DatabaseError => e   puts "An error occurred"   puts "Error code:  #{e.err}"   puts "Error message: #{e.errstr}"ensure   # 斷開與伺服器的串連   dbh.disconnect if dbhend

這將產生以下結果：

First Name: Mac, Last Name : MohanAge: 20, Sex : MSalary :2000 First Name: John, Last Name : PoulAge: 25, Sex : MSalary :2300

還有很多從資料庫擷取記錄的方法，如果您感興趣，可以查看 Ruby DBI Read 操作。
Update 操作

對任何資料庫的 UPDATE 操作是指更新資料庫中一個或多個已有的記錄。下面的執行個體更新 SEX 為 'M' 的所有記錄。在這裡，我們將把所有男性的 AGE 增加一歲。這將分為三步：

•     基於所需的條件準備 SQL 查詢。這將通過使用 prepare 方法來完成。
•     執行 SQL 查詢，從資料庫中選擇所有的結果。這將通過使用 execute 方法來完成。
•     釋放語句控制代碼。這將通過使用 finish 方法來完成。

如果一切進展順利，則 commit 該操作，否則您可以 rollback 完成交易。

#!/usr/bin/ruby -w require "dbi" begin   # 串連到 MySQL 伺服器   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.commitrescue DBI::DatabaseError => e   puts "An error occurred"   puts "Error code:  #{e.err}"   puts "Error message: #{e.errstr}"   dbh.rollbackensure   # 斷開與伺服器的串連   dbh.disconnect if dbhend

DELETE 操作

當您想要從資料庫中刪除記錄時，需要用到 DELETE 操作。下面的執行個體從 EMPLOYEE 中刪除 AGE 超過 20 的所有記錄。該操作的步驟如下：

•     基於所需的條件準備 SQL 查詢。這將通過使用 prepare 方法來完成。
•     執行 SQL 查詢，從資料庫中刪除所需的記錄。這將通過使用 execute 方法來完成。
•     釋放語句控制代碼。這將通過使用 finish 方法來完成。
•     如果一切進展順利，則 commit 該操作，否則您可以 rollback 完成交易。

#!/usr/bin/ruby -w require "dbi" begin   # 串連到 MySQL 伺服器   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",            "testuser", "test123")   sth = dbh.prepare("DELETE FROM EMPLOYEE            WHERE AGE > ?")   sth.execute(20)   sth.finish   dbh.commitrescue DBI::DatabaseError => e   puts "An error occurred"   puts "Error code:  #{e.err}"   puts "Error message: #{e.errstr}"   dbh.rollbackensure   # 斷開與伺服器的串連   dbh.disconnect if dbhend

執行事務

事務是一種確保交易一致性的機制。事務應具有下列四種屬性：

•     原子性（Atomicity）：事務的原子性指的是，事務中包含的程式作為資料庫的邏輯工作單位，它所做的對資料修改操作要麼全部執行，要麼完全不執行。
•     一致性（Consistency）：事務的一致性指的是在一個事務執行之前和執行之後資料庫都必須處於一致性狀態。假如資料庫的狀態滿足所有的完整性條件約束，就說該資料庫是一致的。
•     隔離性（Isolation）：事務的隔離性指並發的事務是相互隔離的，即一個事務內部的操作及正在操作的資料必須封鎖起來，不被其它企圖進行修改的事務看到。
•     持久性（Durability）：事務的持久性意味著當系統或介質發生故障時，確保已提交事務的更新不能丟失。即一旦一個事務提交，它對資料庫中資料的改變應該是永久性的，耐得住任何資料庫系統故障。持久性通過Database Backup和恢複來保證。

DBI 提供了兩種執行事務的方法。一種是 commit 或 rollback 方法，用於提交或復原事務。還有一種是 transaction 方法，可用於實現事務。接下來我們來介紹這兩種簡單的實現事務的方法：
方法 I

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

dbh['AutoCommit'] = false # 設定自動認可為 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.commitrescue puts "transaction failed" dbh.rollbackenddbh['AutoCommit'] = true

方法 II

第二種方法使用 transaction 方法。這個方法相對簡單些，因為它需要一個包含構成事務語句的代碼塊。transaction 方法執行塊，然後根據塊是否執行成功，自動調用 commit 或 rollback：

dbh['AutoCommit'] = false # 設定自動認可為 falsedbh.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'")enddbh['AutoCommit'] = true

COMMIT 操作

Commit 是一種標識資料庫已完成更改的操作，在這個操作後，所有的更改都不可恢複。

下面是一個調用 commit 方法的簡單一實例。

dbh.commit

ROLLBACK 操作

如果您不滿意某個或某幾個更改，您想要完全恢複這些更改，則使用 rollback 方法。

下面是一個調用 rollback 方法的簡單一實例。

dbh.rollback

斷開資料庫

如需斷開資料庫連接，請使用 disconnect API。

dbh.disconnect

如果使用者通過 disconnect 方法關閉了資料庫連接，DBI 會復原所有未完成的事務。但是，不需要依賴於任何 DBI 的實現細節，您的應用程式就能很好地顯式調用 commit 或 rollback。
處理錯誤

有許多不同的錯誤來源。比如在執行 SQL 陳述式時的語法錯誤，或者是串連失敗，又或者是對一個已經取消的或完成的語句控制代碼調用 fetch 方法。

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

這些類的 Exception 對象有 err、errstr 和 state 三種屬性，分表代表了錯誤號碼、一個描述性的錯誤字串和一個標準的錯誤碼。屬性具體說明如下：

•     err：返回所發生的錯誤的整數標記法，如果 DBD 不支援則返回 nil。例如，Oracle DBD 返回 ORA-XXXX 錯誤訊息的數字部分。
•     errstr：返回所發生的錯誤的字串標記法。
•     state：返回所發生的錯誤的 SQLSTATE 代碼。SQLSTATE 是五字元長度的字串。大多數的 DBD 並不支援它，所以會返回 nil。

在上面的執行個體中您已經看過下面的代碼：

rescue DBI::DatabaseError => e   puts "An error occurred"   puts "Error code:  #{e.err}"   puts "Error message: #{e.errstr}"   dbh.rollbackensure   # 斷開與伺服器的串連   dbh.disconnect if dbhend

為了擷取指令碼執行時有關指令碼執行內容的調試資訊，您可以啟用跟蹤。為此，您必須首先下載 dbi/trace 模組，然後調用控制跟蹤模式和輸出目的地的 trace 方法：

複製代碼 代碼如下:

require "dbi/trace"
..............
 
trace(mode, destination)

mode 的值可以是 0（off）、1、2 或 3，destination 的值應該是一個 IO 對象。預設值分別是 2 和 STDERR。
方法的代碼塊

有一些建立控制代碼的方法。這些方法通過代碼塊調用。使用帶有方法的代碼塊的優點是，它們為代碼塊提供了控制代碼作為參數，當塊終止時會自動清除控制代碼。下面是一些執行個體，有助於理解這個概念。

•     DBI.connect ：該方法產生一個資料庫控制代碼，建議在塊的末尾調用 disconnect 來斷開資料庫。
•     dbh.prepare ：該方法產生一個語句控制代碼，建議在塊的末尾調用 finish。在塊內，您必須調用 execute 方法來執行語句。
•     dbh.execute ：該方法與 dbh.prepare 類似，但是 dbh.execute 不需要在塊內調用 execute 方法。語句控制代碼會自動執行。

執行個體 1

DBI.connect 可帶有一個代碼塊，向它傳遞資料庫控制代碼，且會在塊的末尾自動斷開控制代碼。
dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                   "testuser", "test123") do |dbh|
執行個體 2

dbh.prepare 可帶有一個代碼塊，向它即時通行陳述式控制代碼，且會在塊的末尾自動調用 finish。

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

執行個體 3

dbh.execute 可帶有一個代碼塊，向它即時通行陳述式控制代碼，且會在塊的末尾自動調用 finish。

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

DBI transaction 方法也可帶有一個代碼塊，這在上面的章節中已經講解過了。
特定驅動程式的函數和屬性

DBI 讓資料庫驅動程式提供了額外的特定資料庫的函數，這些函數可被使用者通過任何 Handle 對象的 func 方法進行調用。

使用 []= or [] 方法可以設定或擷取特定驅動程式的屬性。

DBD::Mysql 實現了下列特定驅動程式的函數：

執行個體

#!/usr/bin/ruby require "dbi"begin  # 串連到 MySQL 伺服器  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 dbhend

這將產生以下結果：

5.0.4550045Localhost via UNIX socket105.0.45150621Uptime: 384981 Threads: 1 Questions: 1101078 Slow queries: 4 \Opens: 324 Flush tables: 1 Open tables: 64 \Queries per second avg: 2.860