Ruby/DBI - 数据库访问

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

DBI 代表 Database Independent Interface for 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 的一般架构使用两层 −

数据库接口 (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 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 操作。

一旦建立数据库连接，我们就可以使用 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
如果一切顺利，则提交此操作，否则您可以回滚完整的事务。

以下是使用这两种方法的语法 −

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 更有效。

读取操作

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

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

有更多的捷径可以从数据库中获取记录。如果您有兴趣，请通过 DBI 读取操作获取结果否则继续下一部分。

更新操作

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

根据所需条件准备 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

删除操作

当您想从数据库中删除一些记录时，需要进行 DELETE 操作。以下是从 EMPLOYEE 中删除 AGE 大于 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 提供了两种方法来提交或回滚事务。还有一种称为 transaction 的方法可用于实现事务。实现事务有两种简单的方法 −

方法一

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

方法二

第二种方法使用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 回滚操作

如果您对一项或多项更改不满意，并且想要完全还原这些更改，请使用 rollback 方法。

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

dbh.rollback

断开数据库

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

dbh.disconnect

如果用户使用 disconnect 方法关闭了与数据库的连接，则 DBI 会回滚任何未完成的事务。然而，与其依赖于任何 DBI 的实现细节，您的应用程序最好显式调用提交或回滚。

处理错误

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

如果 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)

mode 值可以是 0（关闭）、1、2 或 3，并且目标应该是 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 可以获取一个代码块，将语句句柄传递给它，并在代码块的末尾自动调用完成，如下所示。

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而不是sting。
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