Ruby/DBI - 数据库访问

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

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

DBI 可以与以下接口连接 -

ADO（ActiveX 数据对象）
数据库2
前基地
数据库管理
MySQL
ODBC
甲骨文
OCI8（甲骨文）
PostgreSQL
代理服务器
SQLite
SQL中继

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 操作。

一旦建立了数据库连接，我们就可以使用do方法或准备和执行方法创建表或将记录写入数据库表中。

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

使用准备和执行

您可以使用DBI类的prepare和execute方法通过Ruby代码执行SQL语句。

记录创建采取以下步骤 -

使用 INSERT 语句准备 SQL 语句。这将使用准备方法来完成。
执行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

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

读操作

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

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

记录获取采取以下步骤 -

根据所需条件准备 SQL 查询。这将使用准备方法来完成。
执行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

还有更多从数据库获取记录的快捷方法。如果您有兴趣，请完成“获取结果”，否则请继续下一部分。

更新操作

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

根据所需条件准备 SQL 查询。这将使用准备方法来完成。
执行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 查询。这将使用准备方法来完成。
执行 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

执行交易

事务是一种保证数据一致性的机制。交易应具有以下四个属性 -

Atomics性- 事务要么完成，要么什么也没有发生。
一致性- 事务必须以一致的状态开始，并使系统保持一致的状态。
隔离- 事务的中间结果在当前事务之外不可见。
持久性- 一旦事务被提交，即使在系统发生故障之后，其影响也是持久的。

DBI 提供两种方法来提交或回滚事务。还有一种称为事务的方法可以用来实现事务。有两种简单的方法来实现交易 -

方法一

第一种方法使用 DBI 的提交和回滚方法来显式提交或取消事务 -

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

方法二

第二种方法采用事务方法。这更简单，因为它需要一个包含组成事务的语句的代码块。事务方法执行块，然后根据块是成功还是失败自动调用提交或回滚-

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方法的简单示例。

dbh.commit

回滚操作

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

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

dbh.rollback

断开数据库连接

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

dbh.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 是一个五个字符长的字符串。大多数 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（关闭）、1、2 或 3，并且目标应该是 IO 对象。默认值分别为 2 和 STDERR。

带有方法的代码块

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

DBI.connect - 此方法生成一个数据库句柄，建议在块末尾调用disconnect来断开数据库连接。
dbh.prepare - 此方法生成一个语句句柄，建议在块末尾完成。在块内，您必须调用execute方法来执行语句。
dbh.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事务方法也采用上面已经描述的代码块。

驱动程序特定的函数和属性

DBI 允许数据库驱动程序提供附加的数据库特定函数，用户可以通过任何 Handle 对象的func方法调用这些函数。

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

DBD::Mysql 实现以下特定于驱动程序的功能 -

先生。	功能及说明
1	dbh.func(:createdb, db_name) 创建一个新数据库。
2	dbh.func(:dropdb, db_name) 删除数据库。
3	dbh.func(:重新加载) 执行重新加载操作。
4	dbh.func(:关闭) 关闭服务器。
5	dbh.func(:insert_id) => Fixnum 返回连接的最新 AUTO_INCRMENT 值。
6	dbh.func(:client_info) => 字符串返回 MySQL 客户端版本信息。
7	dbh.func(:client_version) => Fixnum Returns client information in terms of version. It's 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 state of the database.
12	dbh.func(:thread_id) => Fixnum Returns 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