位置:首頁 > 腳本語言 > Ruby基礎教程 > Ruby MySQL DBI實例

Ruby MySQL DBI實例

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

  1. 獲取並安裝Ruby/DBI
  2. 數據庫連接
  3. 插入操作
  4. 讀取操作
  5. 更新操作
  6. 刪除操作
  7. 執行事務
  8. 錯誤處理

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 Architecture

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