Ruby/DBI - 数据库访问

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

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

DBI 可以与以下数据库交互：

ADO (ActiveX 数据对象)
DB2
Frontbase
mSQL
MySQL
ODBC
Oracle
OCI8 (Oracle)
PostgreSQL
代理/服务器
SQLite
SQLRelay

DBI 应用架构

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

Ruby DBI 的通用架构使用两层：

数据库接口 (DBI) 层。此层与数据库无关，并提供了一组通用的访问方法，无论你与之通信的数据库服务器类型如何，这些方法的使用方式都相同。
数据库驱动程序 (DBD) 层。此层依赖于数据库；不同的驱动程序提供对不同数据库引擎的访问。MySQL 有一个驱动程序，PostgreSQL 有另一个驱动程序，InterBase 有另一个驱动程序，Oracle 也有另一个驱动程序，依此类推。每个驱动程序都解释来自 DBI 层的请求，并将它们映射到适合特定类型数据库服务器的请求。

先决条件

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

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

获取和安装 Ruby/DBI

你可以使用 Ruby Gems 包管理器安装 ruby DBI。

gem install 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
   # 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 操作

当你想将记录创建到数据库表中时，需要 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 语句。

记录创建包含以下步骤：

使用 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 操作

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

一旦建立了数据库连接，我们就可以对该数据库进行查询。我们可以使用 do 方法或 prepare 和 execute 方法从数据库表中获取值。

记录获取包含以下步骤：

根据所需条件准备 SQL 查询。这将使用 prepare 方法完成。
执行 SQL 查询以从数据库中选择所有结果。这将使用 execute 方法完成。
逐一获取所有结果并打印这些结果。这将使用 fetch 方法完成。
释放语句句柄。这将使用 finish 方法完成。

以下是查询工资超过 1000 的 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("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

还有更多快捷方法可以从数据库中获取记录。如有兴趣，请阅读获取结果，否则继续下一节。

UPDATE 操作

任何数据库上的 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 操作

当你想从数据库中删除某些记录时，需要 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

执行事务

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

原子性 - 事务要么完成，要么什么也不发生。
一致性 - 事务必须从一致状态开始，并使系统处于一致状态。
隔离性 - 事务的中间结果在当前事务之外不可见。
持久性 - 一旦事务提交，其效果将是持久的，即使在系统故障之后也是如此。

DBI 提供了两种方法来 commit 或 rollback 事务。还有一个名为 transaction 的方法可用于实现事务。有两种简单的实现事务的方法：

方法 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

方法 II

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

如果您对一个或多个更改不满意，并希望完全撤销这些更改，请使用回滚方法。

这是一个调用回滚方法的简单示例。

dbh.rollback

断开数据库连接

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

dbh.disconnect

如果用户使用disconnect方法关闭了数据库连接，则DBI会回滚所有未完成的事务。但是，与其依赖DBI的任何实现细节，不如在您的应用程序中显式调用commit或rollback方法更好。

错误处理

错误来源有很多。一些例子包括执行的SQL语句中的语法错误、连接失败或对已取消或已完成的语句句柄调用fetch方法。

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

这些类的异常对象具有三个名为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
   # disconnect from server
   dbh.disconnect if dbh
end

要获取有关脚本在执行过程中正在执行的操作的调试信息，您可以启用跟踪。为此，您必须首先加载dbi/trace模块，然后调用控制跟踪模式和输出目的地的trace方法：

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

trace(mode, destination)

mode值可以是0（关闭）、1、2或3，destination应为IO对象。默认值分别为2和STDERR。

带有方法的代码块

有些方法会创建句柄。这些方法可以与代码块一起调用。将代码块与方法一起使用的好处是，它们将句柄作为参数提供给代码块，并在块终止时自动清理句柄。以下是一些理解这个概念的例子。

DBI.connect − 此方法生成数据库句柄，建议在块的末尾调用disconnect来断开数据库连接。
dbh.prepare − 此方法生成语句句柄，建议在块的末尾调用finish。在块内，必须调用execute方法来执行语句。
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方法调用这些函数。

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

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) => String 返回数据库的当前状态。
12	dbh.func(:thread_id) => Fixnum 返回当前线程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

打印页面