Ruby/DBI - 資料庫訪問

本章將教你如何使用 Ruby 訪問資料庫。Ruby DBI 模組為 Ruby 指令碼提供了一個與資料庫無關的介面，類似於 Perl DBI 模組。

DBI 代表 Ruby 資料庫獨立介面 (Database Independent Interface for Ruby)，這意味著 DBI 在 Ruby 程式碼和底層資料庫之間提供了一個抽象層，允許你非常輕鬆地切換資料庫實現。它定義了一組方法、變數和約定，這些方法、變數和約定提供了與資料庫一致的介面，而與實際使用的資料庫無關。

DBI 可以與以下資料庫互動：

ADO (ActiveX 資料物件)
DB2
Frontbase
mSQL
MySQL
ODBC
Oracle
OCI8 (Oracle)
PostgreSQL
代理/伺服器
SQLite
SQLRelay

DBI 應用架構

DBI 獨立於後端任何可用的資料庫。無論你使用的是 Oracle、MySQL 還是 Informix 等，都可以使用 DBI。從下面的架構圖可以看出這一點。

Ruby DBI 的通用架構使用兩層：

資料庫介面 (DBI) 層。此層與資料庫無關，並提供了一組通用的訪問方法，無論你與之通訊的資料庫伺服器型別如何，這些方法的使用方式都相同。
資料庫驅動程式 (DBD) 層。此層依賴於資料庫；不同的驅動程式提供對不同資料庫引擎的訪問。MySQL 有一個驅動程式，PostgreSQL 有另一個驅動程式，InterBase 有另一個驅動程式，Oracle 也有另一個驅動程式，依此類推。每個驅動程式都解釋來自 DBI 層的請求，並將它們對映到適合特定型別資料庫伺服器的請求。

先決條件

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

如上所述，此模組充當 DBD，可以從 https://www.tmtm.org/en/mysql/ruby/ 下載。

獲取和安裝 Ruby/DBI

你可以使用 Ruby Gems 包管理器安裝 ruby DBI。

gem install 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。
已設定使用者 ID“testuser”和密碼“test123”以訪問 TESTDB。
Ruby 模組 DBI 已正確安裝在你的機器上。
你已學習過 MySQL 教程，瞭解 MySQL 基礎知識。

以下是連線到 MySQL 資料庫“TESTDB”的示例：

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   # get server version string and display it
   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
   # disconnect from server
   dbh.disconnect if dbh
end

執行此指令碼時，它會在我們的 Linux 機器上產生以下結果。

Server version: 5.0.45

如果與資料來源建立連線，則返回資料庫控制代碼並將其儲存到 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
   # connect to the MySQL server
   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
   # disconnect from server
   dbh.disconnect if dbh
end

使用 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
   # connect to the MySQL server
   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
   # disconnect from server
   dbh.disconnect if dbh
end

如果一次有多個 INSERT，則先準備語句，然後在迴圈中多次執行它，比每次都呼叫 do 更有效。

READ 操作

任何資料庫上的 READ 操作都意味著從資料庫中獲取一些有用的資訊。

一旦建立了資料庫連線，我們就可以對該資料庫進行查詢。我們可以使用 do 方法或 prepare 和 execute 方法從資料庫表中獲取值。

記錄獲取包含以下步驟：

根據所需條件準備 SQL 查詢。這將使用 prepare 方法完成。
執行 SQL 查詢以從資料庫中選擇所有結果。這將使用 execute 方法完成。
逐一獲取所有結果並列印這些結果。這將使用 fetch 方法完成。
釋放語句控制代碼。這將使用 finish 方法完成。

以下是查詢工資超過 1000 的 EMPLOYEE 表中所有記錄的過程。

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   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.finish
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

這將產生以下結果：

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

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

還有更多快捷方法可以從資料庫中獲取記錄。如有興趣，請閱讀獲取結果，否則繼續下一節。

UPDATE 操作

任何資料庫上的 UPDATE 操作都意味著更新資料庫中已存在的一條或多條記錄。以下是更新所有 SEX 為 'M' 的記錄的過程。在這裡，我們將所有男性的 AGE 增加一年。這將需要三個步驟：

根據所需條件準備 SQL 查詢。這將使用 prepare 方法完成。
執行 SQL 查詢以從資料庫中選擇所有結果。這將使用 execute 方法完成。
釋放語句控制代碼。這將使用 finish 方法完成。
如果一切順利，則 commit 此操作，否則可以 rollback 整個事務。

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   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
   # disconnect from server
   dbh.disconnect if dbh
end

DELETE 操作

當你想從資料庫中刪除某些記錄時，需要 DELETE 操作。以下是刪除 EMPLOYEE 表中 AGE 大於 20 的所有記錄的過程。此操作將包含以下步驟。

根據所需條件準備 SQL 查詢。這將使用 prepare 方法完成。
執行 SQL 查詢以從資料庫中刪除所需記錄。這將使用 execute 方法完成。
釋放語句控制代碼。這將使用 finish 方法完成。
如果一切順利，則 commit 此操作，否則可以 rollback 整個事務。

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   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
   # disconnect from server
   dbh.disconnect if dbh
end

執行事務

事務是一種確保資料一致性的機制。事務應具有以下四個屬性：

原子性 - 事務要麼完成，要麼什麼也不發生。
一致性 - 事務必須從一致狀態開始，並使系統處於一致狀態。
隔離性 - 事務的中間結果在當前事務之外不可見。
永續性 - 一旦事務提交，其效果將是持久的，即使在系統故障之後也是如此。

DBI 提供了兩種方法來 commit 或 rollback 事務。還有一個名為 transaction 的方法可用於實現事務。有兩種簡單的實現事務的方法：

方法 I

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

dbh['AutoCommit'] = false # Set auto commit to 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 方法。這更簡單，因為它包含構成事務的語句的程式碼塊。transaction 方法執行該塊，然後根據該塊成功或失敗自動呼叫 commit 或 rollback：

dbh['AutoCommit'] = false # Set auto commit to 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 是一個操作，它向資料庫發出訊號以完成更改，此操作之後，任何更改都無法恢復。

這是一個呼叫 commit 方法的簡單示例。

dbh.commit

ROLLBACK 操作

如果您對一個或多個更改不滿意，並希望完全撤銷這些更改，請使用回滾方法。

這是一個呼叫回滾方法的簡單示例。

dbh.rollback

斷開資料庫連線

要斷開資料庫連線，請使用disconnect API。

dbh.disconnect

如果使用者使用disconnect方法關閉了資料庫連線，則DBI會回滾所有未完成的事務。但是，與其依賴DBI的任何實現細節，不如在您的應用程式中顯式呼叫commit或rollback方法更好。

錯誤處理

錯誤來源有很多。一些例子包括執行的SQL語句中的語法錯誤、連線失敗或對已取消或已完成的語句控制代碼呼叫fetch方法。

如果DBI方法失敗，DBI會引發異常。DBI方法可能會引發幾種型別的異常，但最重要的兩個異常類是DBI::InterfaceError和DBI::DatabaseError。

這些類的異常物件具有三個名為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.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

要獲取有關指令碼在執行過程中正在執行的操作的除錯資訊，您可以啟用跟蹤。為此，您必須首先載入dbi/trace模組，然後呼叫控制跟蹤模式和輸出目的地的trace方法：

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

trace(mode, destination)

mode值可以是0（關閉）、1、2或3，destination應為IO物件。預設值分別為2和STDERR。

帶有方法的程式碼塊

有些方法會建立控制代碼。這些方法可以與程式碼塊一起呼叫。將程式碼塊與方法一起使用的好處是，它們將控制代碼作為引數提供給程式碼塊，並在塊終止時自動清理控制代碼。以下是一些理解這個概念的例子。

DBI.connect − 此方法生成資料庫控制代碼，建議在塊的末尾呼叫disconnect來斷開資料庫連線。
dbh.prepare − 此方法生成語句控制代碼，建議在塊的末尾呼叫finish。在塊內，必須呼叫execute方法來執行語句。
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方法呼叫這些函式。

支援驅動程式特定屬性，可以使用[]=或[]方法設定或獲取這些屬性。

DBD::Mysql實現以下驅動程式特定函式：

序號	函式和描述
1	dbh.func(:createdb, db_name) 建立一個新的資料庫。
2	dbh.func(:dropdb, db_name) 刪除一個數據庫。
3	dbh.func(:reload) 執行重新載入操作。
4	dbh.func(:shutdown) 關閉伺服器。
5	dbh.func(:insert_id) => Fixnum 返回連線的最新AUTO_INCREMENT值。
6	dbh.func(:client_info) => String 返回MySQL客戶端資訊（版本）。
7	dbh.func(:client_version) => Fixnum 返回客戶端資訊（版本）。它類似於:client_info，但返回的是fixnum而不是字串。
8	dbh.func(:host_info) => String 返回主機資訊。
9	dbh.func(:proto_info) => Fixnum 返回用於通訊的協議。
10	dbh.func(:server_info) => String 返回MySQL伺服器資訊（版本）。
11	dbh.func(:stat) => String 返回資料庫的當前狀態。
12	dbh.func(:thread_id) => Fixnum 返回當前執行緒ID。

示例

#!/usr/bin/ruby

require "dbi"
begin
   # connect to the MySQL server
   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

這將產生以下結果：

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

列印頁面