Ruby MySQL DBI實例

本次章節將介紹如何用Ruby來訪問數據庫。類似的Perl DBI模塊為Ruby腳本的Ruby DBI模塊提供了一種數據庫(這裡用MySQL講解)獨立的接口。

獲取並安裝Ruby/DBI
數據庫連接
插入操作
讀取操作
更新操作
刪除操作
執行事務
錯誤處理

DBI代表數據庫無關的接口為Ruby DBI提供的Ruby代碼和底層數據庫之間的抽象層，允許你真的很容易切換數據庫實現。它定義了一套方法，變量和約定來提供一致的數據庫接口，獨立於實際使用的數據庫。

DBI可以連接以下：

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

DBI應用程序的體係結構

DBI是獨立於現有的任何數據庫後端。可以使用DBI操作使用Oracle，MySQL或Informix等，以下是架構示意圖。

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

數據庫接口（DBI）層。這一層是獨立於數據庫，並提供了一組共同的訪問方法所，使用的相同的方式而不管是什麼樣的數據庫服務器。
（DBD）的數據庫驅動程序層。這一層是依賴於數據庫，不同的驅動程序提供了訪問不同的數據庫引擎。驅動程序用於PostgreSQL，MySQL，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

在distrubution目錄dbi-0.2.0 和配置setup.rb的腳本在該目錄中。最常用的配置命令看起來像這樣，不帶參數的配置參數。此命令配置分配默認情況下，安裝所有的驅動程序。

$ ruby setup.rb config

更具體地，提供了 --with 選項列出想使用的特定部位分布。例如，隻配置主DBI模塊和 MySQL DBD-level 驅動程序器，請發出以下命令：

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

步驟 3

最後一步是構建驅動程序並安裝它使用下麵的命令。

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

數據庫連接：

假設我們要使用MySQL數據庫。連接到數據庫之前，請確保以下：

已經創建了數據庫：TESTDB.
已經創建表 EMPLOYEE 在數據庫 TESTDB 中.
這張表有以下幾個屬性 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 設置為NILL值，e.err的和e::errstr返回錯誤代碼和錯誤字符串返回，並保存到一個數據庫句柄 dbh。最後出來之前，確保關閉數據庫連接，並釋放資源。

插入操作:

創建的記錄到數據庫表，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語句。

創建記錄需要以下步驟

準備SQL語句使用INSERT語句。這項工作將使用prepare方法。
執行SQL查詢從數據庫中選擇所有的結果。這項工作將使用execute方法。
釋放語句句柄。這項工作使用 finish API
如果一切運行正常，提交此操作，否則可以回滾完整的事務。

以下是使用這兩個方法的語法：

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

這兩種方法可以用來傳遞值綁定到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

如果有多個插入一次，然後先準備語句，然後執行內多次循環效率比通過每次調用循環要高。

讀取操作：

任何數據庫的讀操作是指從數據庫中獲取一些有用的信息。

一旦我們建立數據庫連接，我們已經準備好進入這個數據庫進行查詢。我們可以使用 do 方法或prepare 和 execute 方法來從數據庫表中的取值。

記錄讀取需要以下步驟

準備SQL查詢所需的條件的基礎上。這項工作將使用prepare方法。
執行SQL查詢從數據庫中選擇所有的結果。這項工作將使用execute方法。
獲取所有的結果逐一打印這些結果。這項工作將可以使用fetch方法。
釋放語句句柄。這項工作將 finish 方法。

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

#!/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
", row[0], row[1]
        printf "Age: %d, Sex : %s
", row[2], row[3]
        printf "Salary :%d 

", 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

如果有一次多個插入，然後先準備語句，然後執行內多次循環調用。

更新操作：

任何數據庫更新操作意味著更新已經在數據庫中的一個或多個記錄。以下是程序更新所有性彆為“M”的記錄。在這裡我們將所有男性年齡增加一年。這將需要三個步驟

根據所需條件Prearing SQL查詢。這項工作將使用prepare方法。
執行SQL查詢從數據庫中選擇所有的結果。這項工作將使用execute方法。
釋放語句句柄。這項工作使用 finish 方法。
如果一切運行正常，然後提交此操作，否則可以回滾整個事務。

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

刪除操作：

想從數據庫中刪除一些記錄，刪除操作是必需的。以下是刪除員工年齡超過20所有記錄。此操作將采取以下步驟。

準備SQL查詢所需的條件的基礎上。這項工作將使用prepare方法。
執行SQL查詢從數據庫中刪除所需的記錄。這項工作將使用execute方法。
釋放語句句柄。這項工作使用 finish 方法。
如果一切運行正常，那麼提交此操作，否則你可以回滾整個事務。

#!/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提供兩種方法來提交或回滾事務。還有一個稱為事務的方法，該方法可以用來實現事務。有兩種簡單的方法來實現事務：

方法一：

第一種方法使用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

方法二：

第二種方法使用事務方法。這是簡單的，因為它需要一個代碼塊包含語句組成事務。事務處理方法執行塊，然後自動調用 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方法。

下麵是一個簡單的例子來調用rollback方法。

     dbh.rollback

斷開數據庫：

要斷開數據庫連接，使用斷開API。

    dbh.disconnect

如果關閉數據庫的連接由用戶使用 disconnect 方法，任何未完成的事務DBI將回滾。然而，當然也可將應用程序顯式調用commit或rollback。

錯誤處理:

錯誤有很多來源。舉幾個例子執行的SQL語句中的語法錯誤，連接失敗或已經取消或已完成的語句句柄調用獲取方法。

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

這些類的異常對象有三個屬性，分彆為 err, errstr 和 state代表錯誤號，描述性的錯誤字符串和一個標準的錯誤代碼。該屬性的說明如下：

err: 返回一個整數，如果這是不支持的DBD發生返回 error 或 nil。例如Oracle的DBD返回ORA-XXXX的錯誤消息為數值的一部分。
errstr: 返回一個字符串，表示發生的錯誤。
state: 返回SQL狀態代碼發生錯誤。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 模塊和控製跟蹤模式和輸出目標的跟蹤方法，然後調用：

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

trace(mode, destination)

該模式的值可以是0（off），1，2或3，目標應該是一個IO對象。默認值是2和STDERR。

代碼塊同方法

有些方法創建句柄。這些方法可以在一個代碼塊中調用。使用代碼塊以及使用方法的優點是，它們提供的句柄作為它的參數的代碼塊和塊終止時，自動清理的句柄。有幾個例子來理解這個概念

DBI.connect : 此方法生成一個數據庫句柄時，建議調用斷開連接上麵的塊結束處斷開數據庫。
dbh.prepare : 這個方法會產生語句句柄並且建議塊結束時完成。塊內，則必須調用execute方法執行該語句。
dbh.execute : 此方法是類似的，除了我們不需要調用塊內執行。它會自動執行語句句柄。

實例 1:

DBI.connect可能需要一個代碼塊，數據庫句柄傳遞給它，並自動斷開句柄塊結束。

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

實例 2:

dbh.prepare可能需要一個代碼塊，語句句柄傳遞給它，並自動調用塊結束完成如下。

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

實例 3:

dbh.execute可能需要一個代碼塊，語句句柄傳遞給它，並自動調用塊結束完成如下：

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

DBI事務方法還需要一個代碼塊，這在上麵已經描述了。

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

DBI讓我們的數據庫驅動程序提供了額外的數據庫特定的函數，它可以由用戶調用Handle對象的任何通過func 方法。

支持驅動程序特有的屬性，可以設置或使用[]=[]的方法得到的。

DBD::mysql的實現以下驅動程序特定的功能：

S.N.	方法及描述
1	dbh.func(:createdb, db_name) Creates a new database
2	dbh.func(:dropdb, db_name) Drops a database
3	dbh.func(:reload) Performs a reload operation
4	dbh.func(:shutdown) Shut down the server
5	dbh.func(:insert_id) => Fixnum Returns the most recent AUTO_INCREMENT value for a connection.
6	dbh.func(:client_info) => String Returns MySQL client information in terms of version.
7	dbh.func(:client_version) => Fixnum Returns client information in terms of version. Its similar to :client_info but it return a fixnum instead of sting.
8	dbh.func(:host_info) => String Returns host information
9	dbh.func(:proto_info) => Fixnum Returns protocol being used for the communication
10	dbh.func(:server_info) => String Returns MySQL server information in terms of version.
11	dbh.func(:stat) => String Returns current stat of the database
12	dbh.func(:thread_id) => Fixnum Return current thread 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