Ruby 简明教程

Ruby/DBI - Database Access

本章教你如何使用 Ruby 访问数据库。Ruby DBI 模块为 Ruby 脚本提供了一个与 Perl DBI 模块类似的数据库独立接口。

DBI 代表 Ruby 的数据库独立接口，这意味着 DBI 在 Ruby 代码和底层数据库之间提供了一个抽象层，让你可以非常轻松地切换数据库实现。它定义了一组方法、变量和约定，它们提供了一个一致的数据库接口，与实际使用的数据库无关。

DBI 可以与以下内容交互 −

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

Architecture of a DBI Application

DBI 独立于后端可用的任何数据库。无论你使用的是 Oracle、MySQL 还是 Informix 等，都可以使用 DBI。这从以下架构图中可以看出。

Ruby DBI 的一般架构使用两层 −

数据库接口 (DBI) 层。该层与数据库无关，并提供了一组通用访问方法，无论使用哪种类型的数据库服务器，使用方式都相同。
数据库驱动程序 (DBD) 层。该层依赖于数据库；不同的驱动程序提供对不同数据库引擎的访问。有一个适用于 MySQL 的驱动程序，另一个适用于 PostgreSQL，另一个适用于 InterBase，另一个适用于 Oracle，以此类推。每个驱动程序都会解释来自 DBI 层的请求，并将其映射到适用于给定类型的数据库服务器的请求。

Prerequisites

如果你想要编写 Ruby 脚本来访问 MySQL 数据库，则需要安装 Ruby MySQL 模块。

如上所述，该模块充当 DBD，并且可以从 https://www.tmtm.org/en/mysql/ruby/ 下载

Obtaining and Installing Ruby/DBI

您可以使用 Ruby Gems 软件包管理器安装 Ruby DBI：

gem install dbi

在开始此安装之前，请确保您拥有 root 权限。现在，按照以下步骤操作：

Step 1

$ tar zxf dbi-0.2.0.tar.gz

Step 2

进入发行版目录 dbi-0.2.0 nd，并使用该目录中的 setup.rb 脚本对其进行配置。最通用的配置命令如下所示，config 参数后不跟任何参数。此命令将发行版配置为默认安装所有驱动程序。

$ ruby setup.rb config

要更具体，请提供一个 --with 选项，列出您想要使用的发行版的特定部分。例如，要仅配置主 DBI 模块和 MySQL DBD 级别驱动程序，请发出以下命令：

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

Step 3

最后一步是使用以下命令构建驱动程序并将其安装：

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

Database Connection

假设我们要使用 MySQL 数据库，在连接到数据库之前确保以下内容：

您已经创建了一个 TESTDB 数据库。
您已经在 TESTDB 中创建了 EMPLOYEE。
该表具有字段 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。
设置了用户 ID“testuser”和密码“test123”来访问 TESTDB。
Ruby Module 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 中以供进一步使用，否则 dbh 将设置为 nil 值，而 e.err 和 e::errstr 将分别返回错误代码和错误字符串。

最后，在退出之前，请确保已关闭数据库连接且已释放资源。

INSERT Operation

当您想要将记录创建到数据库表中时，需要 INSERT 操作。

一旦建立了数据库连接，我们就可以使用 do 方法或 prepare 和 execute 方法在数据库表中创建表或记录。

Using do Statement

可以通过调用 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

Using prepare and 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
   # 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

如果一次有多个 INSERT，那么先准备一条语句，然后在循环中多次执行它比每次通过循环来调用 do 更有效率。

READ Operation

在任何数据库上进行 READ 操作意味着从数据库中获取一些有用的信息。

一旦建立我们的数据库连接，我们就可以对该数据库进行查询。我们可以使用 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\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
   # 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

还有更简单的快捷方式来从数据库中获取记录。如果感兴趣，请浏览 Fetching the Result ，否则继续下一部分。

Update Operation

对任何数据库上的 UPDATE 操作意味着更新数据库中已有的一个或多个记录。以下是在 SEX 为 'M' 的情况下更新所有记录的过程。在这里，我们将所有男性的 AGE 增加一年。这需要三个步骤 −

基于所需条件准备 SQL 查询。将使用 prepare 方法进行此操作。
执行 SQL 查询以从数据库中选择所有结果。将使用 execute 方法进行此操作。
释放语句句柄。此操作将使用 finish 方法进行。
如果一切正常，则 commit 此操作，否则可以 rollback 完整的交易。

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

DELETE Operation

当您想从数据库中删除某些记录时，需要 DELETE 操作。以下是删除 EMPLOYEE 表中 AGE 大于 20 的所有记录的过程。此操作将执行以下步骤。

基于所需条件准备 SQL 查询。将使用 prepare 方法进行此操作。
执行 SQL 查询以从数据库中删除所需的记录。将使用 execute 方法进行此操作。
释放语句句柄。此操作将使用 finish 方法进行。
如果一切正常，则 commit 此操作，否则可以 rollback 完整的交易。

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

Performing Transactions

事务是一种确保数据一致性的机制。事务应当有以下四种属性 −

Atomicity − 事务要么完成，要么根本不会发生。
Consistency - 交易必须以一致的状态开始，且使系统进入一致的状态。
Isolation − 事务的中间结果在当前事务外不可见。
Durability − 一旦提交事务，其效果将持久，即使在系统故障后也是如此。

DBI 提供两种方法来提交或回滚交易。还有一个名为事务的方法，可用于实施事务。有两种简单方法来实施事务 -

Approach I

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

Approach II

第二种方法使用事务方法。这更简单，因为它采用包含构成事务的语句的代码块。事务方法执行代码块，然后根据代码块是否成功或失败，自动调用 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 Operation

提交是一项操作，它向数据库发出一个绿色信号，以最终确定所做的更改，并且在此操作之后，任何更改都将无法还原。

下面是一个调用 commit 方法的简单示例。

dbh.commit

ROLLBACK Operation

如果你对一项或多项更改不满意，并且希望完全撤销这些更改，那么请使用 rollback 方法。

下面是一个调用 rollback 方法的简单示例。

dbh.rollback

Disconnecting Database

要断开数据库连接，请使用断开连接 API。

dbh.disconnect

如果用户使用断开连接方法关闭与数据库的连接，DBI 会回滚所有未完成的事务。但是，与其依赖于 DBI 的任何实施细节，你的应用程序最好明确调用 commit 或 rollback。

Handling Errors

有许多错误来源。一些示例是已执行 SQL 语句中的语法错误、连接故障或对已取消或完成的语句句柄调用获取方法。

如果 DBI 方法失败，DBI 会引发异常。DBI 方法可能会引发任何几种类型的异常，但最重要的两个异常类是 DBI::InterfaceError 和 DBI::DatabaseError。

这些类的异常对象有三个属性，分别名为 err、errstr 和 state，表示错误号、描述性错误字符串和标准错误代码。这些属性的解释如下 -

err - 返回发生的错误的整数表示，如果不被 DBD 支持，则返回 nil。例如，Oracle DBD 会返回 ORA-XXXX 错误消息的数字部分。
errstr - 返回发生的错误的字符串表示。
state - 返回发生的错误的 SQLSTATE 代码。SQLSTATE 是一个长度为 5 个字符的字符串。大多数 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 模块，然后调用控制跟踪模式和输出目的地的 trace 方法 -

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

trace(mode, destination)

模式值可以是 0（关闭）、1、2 或 3，目标应该是 IO 对象。默认值分别是 2 和 STDERR。

Code Blocks with Methods

有一些方法可以创建句柄。这些方法可以通过代码块来调用。使用代码块和方法一起的优点是，它们将句柄作为其参数传递给代码块，并在代码块终止时自动清除句柄。以下有几个理解这个概念的示例。

DBI.connect - 此方法生成一个数据库句柄，建议在代码块的末尾调用断开连接以断开数据库连接。
dbh.prepare − 此方法将生成一个语句句柄，建议在代码块结束时结束该语句句柄。在该代码块中，您必须调用执行方法才能执行此语句。
dbh.execute − 此方法类似，但我们不需要在该代码块中调用执行方法。系统会自动执行该语句句柄。

Example 1

DBI.connect 可以获取代码块，向数据库句柄传递代码块，然后在代码块的末尾自动断开与句柄的连接，如下所示。

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

Example 2

dbh.prepare 可以获取代码块，向语句句柄传递代码块，然后在代码块的末尾自动调用完成方法，如下所示。

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

Example 3

dbh.execute 可以获取代码块，向语句句柄传递代码块，然后在代码块的末尾自动调用完成方法，如下所示 −

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

DBI 事务方法还会获取上面描述的代码块。

Driver-specific Functions and Attributes

使用 DBI，数据库驱动程序可以提供其他特定于数据库的功能，用户可以通过任何句柄对象的 func 方法调用这些函数。

支持特定于驱动程序的属性，可以使用 []= 或 [] 方法设置或获取这些属性。

Example

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