Ruby 數據庫訪問 - DBI 教程 · W3School Ruby 教程

# 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 架構](https://box.kancloud.cn/2015-12-13_566d0631a3e10.jpg) Ruby DBI 一般的架構使用兩個層： * 數據庫接口（DBI）層。該層是獨立于數據庫，并提供了一系列公共訪問方法，方法的使用不分數據庫服務器類型。 * 數據庫驅動（DBD）層。該層是依賴于數據庫，不同的驅動提供了對不同的數據庫引擎的訪問。MySQL、PostgreSQL、InterBase、Oracle 等分別使用不同的驅動。每個驅動都負責解釋來自 DBI 層的請求，并把這些請求映射為適用于給定類型的數據庫服務器的請求。 ## 先決條件如果您想要編寫 Ruby 腳本來訪問 MySQL 數據庫，您需要先安裝 Ruby MySQL 模塊。該模塊是一個 DBD，可從 [http://www.tmtm.org/en/mysql/ruby/](//www.tmtm.org/en/mysql/ruby/) 上下載。 ## 獲取并安裝 Ruby/DBI 您可以從下面的鏈接下載并安裝 Ruby DBI 模塊： [http://rubyforge.org/projects/ruby-dbi/](//rubyforge.org/projects/ruby-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 # 連接到 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 操作**](ruby-dbi-read.html "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 實現了下列特定驅動程序的函數： | 函數 | 描述 | | --- | --- | | **dbh.func(:createdb, db_name)** | 創建一個新的數據庫。 | | **dbh.func(:dropdb, db_name)** | 刪除一個數據庫。 | | **dbh.func(:reload)** | 執行重新加載操作。 | | **dbh.func(:shutdown)** | 關閉服務器。 | | **dbh.func(:insert_id) => Fixnum** | 返回該連接的最近 AUTO_INCREMENT 值。 | | **dbh.func(:client_info) => String** | 根據版本返回 MySQL 客戶端信息。 | | **dbh.func(:client_version) => Fixnum** | 根據版本返回客戶端信息。這與 :client_info 類似，但是它會返回一個 fixnum，而不是返回字符串。 | | **dbh.func(:host_info) => String** | 返回主機信息。 | | **dbh.func(:proto_info) => Fixnum** | 返回用于通信的協議。 | | **dbh.func(:server_info) => String** | 根據版本返回 MySQL 服務器端信息。 | | **dbh.func(:stat) => Stringb> 返回數據庫的當前狀態。** | | | **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 ```