Ruby MySQL DBI实例

本次章节将介绍如何用Ruby来访问数据库。类似的Perl DBI模块为Ruby脚本的Ruby DBI模块提供了一种数据库(这里用MySQL讲解)独立的接口。

获取并安装Ruby/DBI
数据库连接
插入操作
读取操作
更新操作
删除操作
执行事务
错误处理

DBI代表数据库无关的接口为Ruby DBI提供的Ruby代码和底层数据库之间的抽象层，允许你真的很容易切换数据库实现。它定义了一套方法，变量和约定来提供一致的数据库接口，独立于实际使用的数据库。

DBI可以连接以下：

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

DBI应用程序的体系结构

DBI是独立于现有的任何数据库后端。可以使用DBI操作使用Oracle，MySQL或Informix等，以下是架构示意图。

Ruby的DBI一般架构为使用两个层次：

数据库接口（DBI）层。这一层是独立于数据库，并提供了一组共同的访问方法所，使用的相同的方式而不管是什么样的数据库服务器。
（DBD）的数据库驱动程序层。这一层是依赖于数据库，不同的驱动程序提供了访问不同的数据库引擎。驱动程序用于PostgreSQL，MySQL，InterBase 或 Oracle等等。每个驱动程序解释DBI层的请求，并将它们映射到适用于某一特定类型的数据库服务器的请求。

先决条件：

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

此模块作为一个DBD如上所述，可以直接从网上下载 http://www.tmtm.org/en/mysql/ruby/

获取并安装Ruby/DBI：

可以下载并安装Ruby DBI模块从以下位置：

http://rubyforge.org/projects/ruby-dbi/

在开始安装之前，请确保有root权限。执行以下步骤：

步骤 1

$ tar zxf dbi-0.2.0.tar.gz

步骤 2

在distrubution目录dbi-0.2.0 和配置setup.rb的脚本在该目录中。最常用的配置命令看起来像这样，不带参数的配置参数。此命令配置分配默认情况下，安装所有的驱动程序。

$ ruby setup.rb config

更具体地，提供了 --with 选项列出想使用的特定部位分布。例如，只配置主DBI模块和 MySQL DBD-level 驱动程序器，请发出以下命令：

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

步骤 3

最后一步是构建驱动程序并安装它使用下面的命令。

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

数据库连接：

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

已经创建了数据库：TESTDB.
已经创建表 EMPLOYEE 在数据库 TESTDB 中.
这张表有以下几个属性 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 设置为NILL值，e.err的和e::errstr返回错误代码和错误字符串返回，并保存到一个数据库句柄 dbh。最后出来之前，确保关闭数据库连接，并释放资源。

插入操作:

创建的记录到数据库表，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语句。

创建记录需要以下步骤

准备SQL语句使用INSERT语句。这项工作将使用prepare方法。
执行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

如果有多个插入一次，然后先准备语句，然后执行内多次循环效率比通过每次调用循环要高。

读取操作：

任何数据库的读操作是指从数据库中获取一些有用的信息。

一旦我们建立数据库连接，我们已经准备好进入这个数据库进行查询。我们可以使用 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
", row[0], row[1]
        printf "Age: %d, Sex : %s
", row[2], row[3]
        printf "Salary :%d 

", 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

如果有一次多个插入，然后先准备语句，然后执行内多次循环调用。

更新操作：

任何数据库更新操作意味着更新已经在数据库中的一个或多个记录。以下是程序更新所有性别为“M”的记录。在这里我们将所有男性年龄增加一年。这将需要三个步骤

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

删除操作：

想从数据库中删除一些记录，删除操作是必需的。以下是删除员工年龄超过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提供两种方法来提交或回滚事务。还有一个称为事务的方法，该方法可以用来实现事务。有两种简单的方法来实现事务：

方法一：

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

方法二：

第二种方法使用事务方法。这是简单的，因为它需要一个代码块包含语句组成事务。事务处理方法执行块，然后自动调用 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方法。

     dbh.rollback

断开数据库：

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

    dbh.disconnect

如果关闭数据库的连接由用户使用 disconnect 方法，任何未完成的事务DBI将回滚。然而，当然也可将应用程序显式调用commit或rollback。

错误处理:

错误有很多来源。举几个例子执行的SQL语句中的语法错误，连接失败或已经取消或已完成的语句句柄调用获取方法。

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

这些类的异常对象有三个属性，分别为 err, errstr 和 state代表错误号，描述性的错误字符串和一个标准的错误代码。该属性的说明如下：

err: 返回一个整数，如果这是不支持的DBD发生返回 error 或 nil。例如Oracle的DBD返回ORA-XXXX的错误消息为数值的一部分。
errstr: 返回一个字符串，表示发生的错误。
state: 返回SQL状态代码发生错误。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（off），1，2或3，目标应该是一个IO对象。默认值是2和STDERR。

代码块同方法

有些方法创建句柄。这些方法可以在一个代码块中调用。使用代码块以及使用方法的优点是，它们提供的句柄作为它的参数的代码块和块终止时，自动清理的句柄。有几个例子来理解这个概念

DBI.connect : 此方法生成一个数据库句柄时，建议调用断开连接上面的块结束处断开数据库。
dbh.prepare : 这个方法会产生语句句柄并且建议块结束时完成。块内，则必须调用execute方法执行该语句。
dbh.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可能需要一个代码块，语句句柄传递给它，并自动调用块结束完成如下：

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

DBI事务方法还需要一个代码块，这在上面已经描述了。

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

DBI让我们的数据库驱动程序提供了额外的数据库特定的函数，它可以由用户调用Handle对象的任何通过func 方法。

支持驱动程序特有的属性，可以设置或使用[]=[]的方法得到的。

DBD::mysql的实现以下驱动程序特定的功能：

S.N.	方法及描述
1	dbh.func(:createdb, db_name) Creates a new database
2	dbh.func(:dropdb, db_name) Drops a database
3	dbh.func(:reload) Performs a reload operation
4	dbh.func(:shutdown) Shut down the server
5	dbh.func(:insert_id) => Fixnum Returns the most recent AUTO_INCREMENT value for a connection.
6	dbh.func(:client_info) => String Returns MySQL client information in terms of version.
7	dbh.func(:client_version) => Fixnum Returns client information in terms of version. Its 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 stat of the database
12	dbh.func(:thread_id) => Fixnum Return 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