Ruby 資料庫存取 - DBI 教程

PostgreSQL

• ## Proxy/Server

• SQLite

DBI 應用程式架構

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

#資料庫介面（DBI）層。該層是獨立於資料庫，並提供了一系列公共存取方法，方法的使用不分資料庫伺服器類型。

資料庫驅動程式（DBD）層。該層是依賴資料庫，不同的驅動提供了對不同的資料庫引擎的存取。 MySQL、PostgreSQL、InterBase、Oracle 等分別使用不同的驅動程式。每個驅動程式都負責解釋來自 DBI 層的請求，並將這些請求對應為適用於給定類型的資料庫伺服器的請求。

安裝

如果您想要編寫 Ruby 腳本來存取 MySQL 資料庫，您需要先安裝 Ruby MySQL 模組。

# Ubuntu
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient15-dev

# Centos
yum install mysql-devel

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib

MYSQL=/usr/local/mysql/bin
export PATH=$PATH:$MYSQL
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

sudo gem install dbi
sudo gem install mysql
sudo gem install dbd-mysql

% ruby extconf.rb

或者

% ruby extconf.rb --with-mysql-dir=/usr/local/mysql

或者

% ruby extconf.rb --with-mysql-config

% make

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

步驟 1

git clone https://github.com/erikh/ruby-dbi.git

或直接下再 zip 套件並解壓縮。

步驟 2

進入目錄 ruby-dbi-master，在目錄中使用 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
     # 连接到 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 dbh
end

當這段腳本執行時，將會在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.commit
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # 断开与服务器的连接
     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
     # 连接到 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.rollback
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

如果同時使用多個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.finish
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
ensure
     # 断开与服务器的连接
     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

還有很多從資料庫取得記錄的方法，如果您有興趣，可以查看 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.commit
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

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.commit
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

執行交易

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

• 原子性（Atomicity）：事務的原子性指的是，事務中包含的程式作為資料庫的邏輯工作單位，它所做的對資料修改操作要麼全部執行，要麼完全不執行。

• 一致性（Consistency）：交易的一致性指的是在一個交易執行之前和執行之後資料庫都必須處於一致性狀態。假如資料庫的狀態滿足所有的完整性約束，就說該資料庫是一致的。

• 隔離性（Isolation）：交易的隔離性指並發的交易是相互隔離的，即一個交易內部的操作及正在操作的資料必須封鎖起來，不被其它企圖進行修改的事務看到。

• 持久性（Durability）：交易的持久性意味著當系統或媒體發生故障時，確保已提交交易的更新不能遺失。即一旦一個事務提交，它對資料庫中資料的改變應該是永久性的，耐得住任何資料庫系統故障。持久性透過資料庫備份和復原來保證。

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.commit
   rescue
     puts "transaction failed"
     dbh.rollback
   end
   dbh['AutoCommit'] = true

方法II

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

   dbh['AutoCommit'] = false # 设置自动提交为 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 操作

如果您不滿意某個或某幾個更改，您想要完全恢復這些更改，則使用 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.rollback
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

為了獲取腳本執行時有關腳本執行內容的調試信息，您可以啟用跟踪。為此，您必須先下載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 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