Ruby 數據庫訪問– DBI 教程

本章節將向您講解如何使用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 模塊。

安裝Mysql 開發包

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

# Centos
yum install mysql-devel

Mac OS 系統需要修改~/.bash_profile 或~/.profile 文件，添加如下代碼:

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

或者使用軟連接：

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

使用RubyGems 安裝DBI（推薦）

RubyGems大約創建於2003年11月，從Ruby 1.9版起成為Ruby標準庫的一部分。更多詳情可以查看： Ruby RubyGems

使用gem 安裝dbi 與dbd-mysql：

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

使用源碼安裝（Ruby版本小於1.9的使用此方法）

該模塊是一個DBD，可從http://tmtm.org/downloads/mysql/ruby/上下載。

下載後最新包，解壓進入到目錄，執行以下命令安裝:

% ruby extconf.rb

或者

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

或者

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

然後編譯：

% make

獲取並安裝Ruby/DBI

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

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

在開始安裝之前，請確保您擁有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 實現了下列特定驅動程序的函數：

序號	函數& 描述
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) => Stringb> 返回數據庫的當前狀態。
12	dbh.func(:thread_id) => Fixnum 返回當前線程的ID。

實例

#!/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

上一頁： Ruby正則表達式

下一頁： Ruby DBI Read操作

Ruby 教程

Ruby 高級教程

Ruby 數據庫訪問– DBI 教程

DBI 應用架構

安裝

安裝Mysql 開發包

使用RubyGems 安裝DBI（推薦）

使用源碼安裝（Ruby版本小於1.9的使用此方法）

數據庫連接

INSERT 操作

使用do 語句

使用prepare和execute

READ 操作

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

Update 操作

DELETE 操作

執行事務

方法I

方法II

COMMIT 操作

ROLLBACK 操作

斷開數據庫

處理錯誤

方法的代碼塊

實例1

實例2

實例3

特定驅動程序的函數和屬性

實例

Ruby 教程

Ruby 高級教程

Ruby 數據庫訪問– DBI 教程

DBI 應用架構

安裝

安裝Mysql 開發包

使用RubyGems 安裝DBI（推薦）

使用源碼安裝（Ruby版本小於1.9的使用此方法）

數據庫連接

INSERT 操作

使用do 語句

使用prepare和execute

READ 操作

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

Update 操作

DELETE 操作

執行事務

方法I

方法II

COMMIT 操作

ROLLBACK 操作

斷開數據庫

處理錯誤

方法的代碼塊

實例1

實例2

實例3

特定驅動程序的函數和屬性

實例

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