ruby DBI模块

1.安装

gem install dbi

gem install dbd-mysql

手动安装

http://blog.csdn.net/ruby_cn/article/details/174293

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

下载ruby-dbi

tar zxf dbi-0.1.1.tar.gz
ruby setup.rb config
ruby setup.rb config --with=dbi,dbd_mysql
ruby setup.rb setup
ruby setup.rb install                 //运行install需要root权限。

 

一个简单的DBI脚本
simple:

require "dbi"
begin
  # connect to the MySQL server
  dbh = DBI.connect("dbi:Mysql:database:localhost", "user", "password")
  # get server version string and display it
  sth=dbh.execute("select * from case_message")
  while row = sth.fetch_hash do
    puts "#{row['caseid']}"
  end
  sth.finish
end

 

安装完ruby DBI模块之后，你就可以在你的Ruby程序中访问MYSQL数据库了。假设我们的数据库在本机运行，即localhost，数据库名为test，通过一个用户名为testuser，密码是testpass的用户访问。我们可以用root登陆到mysql程序，然后执行下列命令建立这样的一个用户：
mysql> GRANT ALL ON test.* TO 'testuser'@'localhost' IDENTIFIED BY 'testpass';
如果test数据库不存在，用下面的命令创建它：
mysql> CREATE DATABASE test;
如果你想用不同的数据库，服务器，用户和密码的话，只需要将例子里对应的值换成你自己的就行了。
下面这个脚本， simple.rb, 是一个很短的DBI程序，它先连接的数据库，然后查询了数据库的版本，并显示出来，然后断开连接。你可以从“资源”里提供的链接下载这段代码，或者把它拷贝到文本编辑器中：

 

# simple.rb - simple MySQL script using Ruby DBI module
require "dbi"
begin
  # connect to the MySQL server
  dbh = DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass")
  # 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

simple.rb 展现了DBI最基本的一些概念，下面的讨论将会讲述他是如何工作的，然后更后面还要讲述DBI的其他一些方面。
simple.rb以一行require 开始，把DBI模块引入近来；没有这一行的话，DBI方法将会出错，后面的代码包括在一个 begin/rescue/ensure 结构中:
• begin 部分处理了所有的数据库请求。
• rescue 部分用来处理出错信息，它将获取出错信息，并显示出来。
• ensure 块确保程序不管出错与否，最后都将关闭数据库连接。
方法connect 用来和数据库服务器建立一个连接，并返回这个连接。第一个参数是数据源名（data source name DSN），它指定了驱动名称（Mysql用于MySql服务器），默认得数据库名和服务器的机器名，第二、三个参数是用户和密码。还有其他的DSN写法， 将在后面“再论连接数据库”中说明。
simple.rb 用数据库句柄（database handle）调用方法select_one, 这个方法向服务器发送一个查询语句，并且将结果集的第一行作为数组返回给调用者。“SELECT VERSION() ”返回返回单个值，所以版本信息将存在row[0]中，这是这个数组的第一个也是唯一一个元素。运行这个程序，结果像这样：
% ruby simple.rb
Server version: 4.0.13-log
如果出错，会导致抛出异常，异常可能各种各样，但多数都属于数据库错误，多为DatabaseError 异常，这种异常对象包括err和errstr属性，err是错误编号，errstr是错误消息。simple.rb得到这些异常的值并打印它们，但是忽略了其他的异常，这时候如果出现了其他异常，则将会抛给ruby执行环境。
simple.rb 用disconnect方法来断开与数据库的连接，这在ensure里执行，这样就使得不管出错与否，数据库连接都会被断开。

处理查询

Ruby DBI 提供了很多方法用来执行查询语句。这里将讨论这中间的一部分，但还有其他的。
多数的例子都用到了表people，它的结构如下：

CREATE TABLE people
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, # ID number
  name CHAR(20) NOT NULL, # name
  height FLOAT, # height in inches
  PRIMARY KEY (id)
);

 

处理不返回结果集的查询

如果一个语句不需要返回结果，可以用数据库句柄的do方法，这个方法的参数为要执行的sql语句，返回受影响的行数。下面的例子创建了表people，并插入了几条记录，都用了do方法：

dbh.do("DROP TABLE IF EXISTS people")
dbh.do("CREATE TABLE people (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name CHAR(20) NOT NULL,
height FLOAT)")
rows = dbh.do("INSERT INTO people (name,height)
VALUES('Wanda',62.5),('Robert',75),('Phillip',71.5),('Sarah',68)")
printf "%d rows were inserted/n", rows

需要注意的是insert语句返回了一个值，即插入的行数，并把它打印了出来。

 

处理返回结果集的查询

像select和show这样的语句是要返回行记录的，处理这样的语句，要先向服务器提交查询，处理查询产生的每条记录，然后把结果集销毁。
一种办法是调用prepare产生一个statement 句柄，用这个句柄来执行查询，取回结果，然后释放结果集：
sth = dbh.prepare(statement)
sth.execute
... fetch rows ...
sth.finish
或者直接把语句发送给数据库连接句柄去执行而不用调用prepare：
sth = dbh.execute(statement)
... fetch rows ...
sth.finish
同样也有很多方法从执行完的语句取得结果，可以在一个循环里调用fetch方法直到返回nil为止：
sth = dbh.execute("SELECT * FROM people")
while row = sth.fetch do
  printf "ID: %d, Name: %s, Height: %.1f/n", row[0], row[1], row[2]
end
sth.finish
fetch 也可以用作一个 iterator来用, 也用each.方法。下面的两个是一样的作用：
sth = dbh.execute("SELECT * FROM people")
sth.fetch do |row|
  printf "ID: %d, Name: %s, Height: %.1f/n", row[0], row[1], row[2]
end
sth.finish
sth = dbh.execute("SELECT * FROM people")
sth.each do |row|
  printf "ID: %d, Name: %s, Height: %.1f/n", row[0], row[1], row[2]
end
sth.finish
fetch 和each 都产生了 DBI::Row 对象, 这个对象提供了访问他们内容的方法：
• 可以用by_index 或 by_field 来通过顺序或者名字访问字段值：
• val = row.by_index(2)
• val = row.by_field("height")
• 字段值也可以将row对象当成数组来取得：
• val = row[2]
• val = row["height"]
• 迭代方法 each_with_name 生成每个字段名和它们的值：
• sth = dbh.execute("SELECT * FROM people")
• sth.each do |row|
• row.each_with_name do |val, name|
• printf "%s: %s, ", name, val.to_s
• end
• print "/n"
• end
• sth.finish
• DBI::Row 对象提供了一个方法 column_names 来得到一个包含每个字段名的数组。 field_names 是 column_names的别名。
其他的返回行数据的方法包括fetch_array和fetch_hash，他们不返回DBI::Row对象，而是将下一行数据作为数组或者哈希返回， 如果已经到结果集的最后的话，也会返回nil。fetch_hash返回哈希结构，由列名作为key，而列的值作为这个key对应的值。这两个方法可以独立使用，也可以在迭代中使用。下面例子用了hash方法：
sth = dbh.execute("SELECT * FROM people")
while row = sth.fetch_hash do
  printf "ID: %d, Name: %s, Height: %.1f/n",
  row["id"], row["name"], row["height"]
end
sth.finish
sth = dbh.execute("SELECT * FROM people")
sth.fetch_hash do |row|
  printf "ID: %d, Name: %s, Height: %.1f/n",
  row["id"], row["name"], row["height"]
end
sth.finish
你也可以不用依照 “查询－－取结果－－完成”这种顺序来执行你的语句，数据库句柄可以一次取回所有的结果:
row = dbh.select_one(statement)
rows = dbh.select_all(statement)
select_one 执行一个查询，然后将结果的第一行作为一个数组返回，或者返回nil，如果没有匹配记录的话。select_all 返回一个 DBI::Row 的数组，(你可以用前面讨论过得方法访问里面的内容)。如果没有匹配结果，则返回空数组。注意不是nil。
MySQL 驱动会检查返回的结果集中的元数据（metadata），然后强制将这个字段的的值变为对应的Ruby类型（比如，从people取得的id，name，height字段的值将会被转变为Fixnum，String和Float对象）。但是，如果一个列的值为NULL，则用nil来表示，并且它的类型为NilClass。还有就是这不是DBI规格说明书的硬性规定，所以有的驱动可能不会做这样的工作。

 

引用，占位符（placeholder）和参数绑定

Ruby DBI提供了占位符机制，使得你可以不用在查询语句中把数据值的字面值写到里面，而是用一些特殊的符号标记数据的位置，当你真的要执行的时候，用真实的数据值填充占位符的位置。DBI会用数据值替换占位符，完成对字符串等加引号，特殊字符的转义（如果需要的话）等，而不必你自己去做，而且占位符机制能很好的处理NULL值，你只需要提供一个nil值，它会自动被换成NULL放到查询中。
下面例子解释了它是如何工作的。加入你想向people表里插入一条记录，这个人的名字叫Na'il，这个名字包括一个单引号，他的身高是76英寸。在查询语句中，用？来作为插入值的占位符，不需要引号括起来，然后将实际要插入的值作为do的参数，如下:
dbh.do("INSERT INTO people (id, name, height) VALUES(?, ?, ?)",
nil, "Na'il", 76)
这条语句发送给数据库的语句像这样：
INSERT INTO people (id,name,height) VALUES(NULL,'Na/'il',76)
这更适合于你要多次执行一个查询，你可以先生成一个预处理语句语句，然后每次用数据值填充去执行。假如要导入的数据存在文本文件people.txt里面，每一行了用tab分割，由name，height两列组成，下面的代码演示了如何从数据文件读取数据，然后执行insert语句将每一行插入数据库：
# prepare statement for use within insert loop
sth = dbh.prepare("INSERT INTO people (id, name, height) VALUES(?, ?, ?)")
# read each line from file, split into values, and insert into database
f = File.open("people.txt", "r")
f.each_line do |line|
name, height = line.chomp.split("/t")
sth.execute(nil, name, height)
end
f.close
生成一个预处理语句，然后在循环中多次执行它，比用循环来直接执行有效多了，主要是因为数据库能为预处理语句生成一个执行计划，以后每次执行都会用这个执行计划来执行，提高了效率。当然目前mysql还不支持这个功能，oracle支持。
如果想用占位符的方法执行select语句，你应该先考虑一下是否用预处理语句:
• 如果你用prepare 方法得到一个statement 句柄，用这个句柄执行查询，并提供数据值填充占位符：
• sth = dbh.prepare("SELECT * FROM people WHERE name = ?")
• sth.execute("Na'il")
• sth.fetch do |row|
•   printf "ID: %d, Name: %s, Height: %.1f/n", row[0], row[1], row[2]
• end
• sth.finish
• 如果你不用 prepare那么execute方法的第一个参数就是要执行的语句，后面的参数是要填充用的数据值：
• sth = dbh.execute("SELECT * FROM people WHERE name = ?", "Na'il")
• sth.fetch do |row|
• printf "ID: %d, Name: %s, Height: %.1f/n", row[0], row[1], row[2]
• end
• sth.finish
其它的驱动也许需要用不同的占位符，比如你可能需要写 :name 或 :n 来指明是按名称还是按位置来对应。
方法quote 能将一个值中的特殊字符处理、转义等，并返回这个结果。这适用于产生sql语句以供别的程序使用，比如，你想将上面的people.txt文件的内容转化为能在mysql命令行里执行的一组insert语句，只需要如下程序：
# read each line from file, split into values, and write INSERT statement
f = File.open("people.txt", "r")
f.each_line do |line|
name, height = line.chomp.split("/t")
printf "INSERT INTO people (id, name, height) VALUES(%s, %s, %s);/n",
dbh.quote(nil), dbh.quote(name), dbh.quote(height)
end
f.close

 

查询元数据 （Metadata）

对于不需要返回结果的语句，比如insert，delete等，do方法返回insert或者delete的行数。
对于返回结果的查询，比如select，你可以在execute方法之后用statement句柄取得返回的行和列的个数，以及各列的信息：
• 行数和列数不能直接得到，为了得到行数，你可以循环处理每一行的时候进行计数，或者将结果放到一个数据结构里，然后看看这个数据结构有多少个元素。要想得到返回的列的个数，你可以从sth.column_names.size得到。
• 方法column_info 返回各列的详细信息。
下面例子说明了如何从一个查询得到metadata：

sth = dbh.execute(query)
puts "Query: " + query
if sth.column_names.size == 0 then
  puts "Query has no result set"
  printf "Number of rows affected: %d/n", sth.rows
  else
    puts "Query has a result set"
    rows = sth.fetch_all
    printf "Number of rows: %d/n", rows.size
    printf "Number of columns: %d/n", sth.column_names.size
    sth.column_info.each_with_index do |info, i|
      printf "--- Column %d (%s) ---/n", i, info.name
      printf "precision: %s/n", info.precision
      printf "scale: %s/n", info.scale
    end
end
sth.finish

 

注意：本文档的早期版本中说你可以从sth.rows得到返回的行数，现在已经不支持了。（尽管现在在mysql驱动中还可以用，但是你不应该在依赖这个函数了）

 

接受代码块的方法（Methods That Take Code Blocks）

一些能产生句柄的方法可以用来在block中调用，用这种方法时，它们将句柄作为参数提供给block，并且在块结束后自动销毁这些句柄。
• DBI.connect 产生一个数据库句柄（database handle），在块结束后会（自动）调用disconnect。
• dbh.prepare 产生一个statement句柄（statement handle），在块结束后，会自动调用finish方法，在块内部，你必须调用execute方法来执行语句。
• dbh.execute 也和上面类似，但是你不需要在块内部调用execute方法，statement会自动执行。
下面的例子说明了上面的三个问题：
# connect can take a code block, passes the database handle to it,
# and automatically disconnects the handle at the end of the block
DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass") do |dbh|
# prepare can take a code block, passes the statement handle
# to it, and automatically calls finish at the end of the block
dbh.prepare("SHOW DATABASES") do |sth|
sth.execute
puts "Databases: " + sth.fetch_all.join(", ")
end
# execute can take a code block, passes the statement handle
# to it, and automatically calls finish at the end of the block
dbh.execute("SHOW DATABASES") do |sth|
puts "Databases: " + sth.fetch_all.join(", ")
end
end
此外还有一个 transaction 方法可以接收一个块，将在下面的“事务处理支持”中讨论。

 

再论连接数据库

前面讨论过的simple.rb 脚本用DBI 的connect方法连接数据库服务器：
dbh = DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass")
connect的第一个参数十DSN，它指明了要连接类型，后面的参数是用户名和密码。
DSN 可以是下面的任何格式的一种：
dbi:driver_name
dbi:driver_name:db_name:host_name
dbi:driver_name:key=val;key=val...
DSN总是以dbi或者DBI（而不能既有大写又有小写的字母）和驱动名称开头，对MySql来说，驱动名称是Mysql，对于其他的驱动，需要指定对应的正确的名字。
DSN中必须有dbi (或 DBI) ，如果在驱动后面没有其他信息，那么驱动会尝试用默认得数据库和机器名连接数据库。而mysql要求必须指定数据库名，所以上面的第一种写法不能用于mysql，必须用其他的写法。第二种写法需要两个值，一个数据库名，一个机器名，两个值用冒号分开。第三种格式允许用 param=value 格式指定一系列的参数，参数之间用分号分割，比如，下面三种写法完全等同：
dbi:Mysql:test:localhost
dbi:Mysql:host=localhost;database=test
dbi:Mysql:database=test;host=localhost
在 DSN 语法中使用 param=value 格式比较灵活，各个参数的位置可以随意设置。而且可以设置一些针对不同驱动的特有的参数，就是说可以在它接收的参数方面进行扩展。比如Mysql，除了host和database参数，还可以设置port，socket，flag等参数。（这些参数对应于ruby mysql 模块的real_connect方法中的各个参数，而DBD::Mysql也是基于这个Ruby Mysql模块的）

 

　
错误处理和调试

如果一个DBI方法是白了，将抛出一个异常。DBI方法可以抛出几种异常，但是和数据库相关的方法一般抛出DatabaseError异常，这种异常的对象有三个属性，err，errstr和state。DBI的文档没有说这三个属性是什么意思，但是看起来它们分别表示错误编号，一个字符串型的错误描述和一些“标准”的错误代码。目前MySQL驱动只支持errstr，但很容易用补丁使它也支持err属性。假定这两个属性都可用，那么下面方法说明了如何得到这些值：
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
为了得到你的语句执行时的调试信息，可以使用跟踪（tracing）。要想这样，首先你要载入dbi/trace模块：
require "dbi/trace"
模块 dbi/trace 默认没有包括在dbi模块中，因为这需要0.3.3以上版本的AspectR模块，这个模块可能在你的机器上并不存在。
dbi/trace 模块提供了一个trace方法，可以用来控制跟踪模式和输出目标：

trace(mode, destination)
mode 值为0（off），1，2，3，默认值为2； destination 是一个IO对象，默认为STDERR。
trace 可以作为一个类方法调用，这样随后创建的句柄都可以使用；或者作为一个单独的驱动，数据库，statement 句柄的对象方法，任何继承这些对象的子类都可以继承这些跟踪设置。比如，比如，你允许一个数据库句柄进行跟踪，从这个句柄创建的statement句柄也具备同样的跟踪设置。

 

事务处理支持

DBI提供了事务支持，但是怎样支持取决于你的底层数据库和DBD层数据库驱动的实现情况。比如Mysql驱动，在DBI 0.0.19之前都没有提供，所以你必须使用statement的自动提交功能来达到同样的目的，比如：
dbh.do("SET AUTOCOMMIT=0")
dbh.do("BEGIN")
... statements that make up the transaction ...
dbh.do("COMMIT")
对于 DBI 0.0.19 和更高版本，你可以使用mysql的事务控制，可以设置数据库句柄来设置是否自动提交：
dbh['AutoCommit'] = true
dbh['AutoCommit'] = false
当自动提交被禁止之后，你有两种方法来实现事务控制。下面的例子说明了这两种方法，一个表account，要在两个人时间的基金转帐中实现事务性操作：
• 首先是用 DBI的 commit 和 rollback 方法显示的提交或者取消事务：
• dbh['AutoCommit'] = false
• begin
• dbh.do("UPDATE account SET balance = balance - 50
• WHERE name = 'bill'")
• dbh.do("UPDATE account SET balance = balance + 50
• WHERE name = 'bob'")
• dbh.commit
• rescue
• puts "transaction failed"
• dbh.rollback
• end
• 第二种方法用了transaction方法，这种方法很简单，它接受了一个要求事务操作的处理块，transaction方法执行这个块，然后根据这个块执行结果是成功还是失败自动执行commit或者rollback。
• dbh['AutoCommit'] = false
• dbh.transaction do |dbh|
• dbh.do("UPDATE account SET balance = balance - 50
• WHERE name = 'bill'")
• dbh.do("UPDATE account SET balance = balance + 50
• WHERE name = 'bob'")
• end

 

使用不同驱动特有的功能（Driver-Specific Capabilities）

DBI提供了一个func方法，可以执行不同数据库驱动特有的功能，比如，mysql C API提供了mysq_insert_id（）方法，这个方法返回AUTO_INCREMENT 的最新值。Ruby Mysql模块提供了一个绑定到这个函数的函数：数据库句柄的insert_id 方法。这个方法是在DBD::Mysql中提供的，使得你可以通过DBI访问。
func 的第一个参数是你想执行的数据库特有的方法的名称，后面的参数是这个数据库特有方法的参数，如果没有参数，可以不填。insert_id没有参数，所以要想访问最新的AUTO_INCREMENT 值，可以这样：
dbh.do("INSERT INTO people (name,height) VALUES('Mike',70.5)")
id = dbh.func(:insert_id)
puts "ID for new record is: " + id.to_s
DBD::Mysql 提供的其它方法包括：
dbh.func(:createdb, db_name) 创建数据库
dbh.func(:dropdb, db_name) 删除数据库
dbh.func(:reload) 重新加载（reload）
dbh.func(:shutdown) 关闭数据库
注意的是，只有你的mysql版本在4以上，创建数据库和删除数据库的功能才可以使用。
有些时候，使用数据库特有的方法能有特别的有点，即使按通常的其他方法也能达到同样的作用。比如，DBD::Mysql 的insert_id方法的功能和执行查询语句“SELECT LAST_INSERT_ID()”一样，都返回同一个值，但是insert_id更有效，因为它把这个值保存在了客户端，再次需要时不用重复执行查找。每次有新的插入之后，这个值都会改变，所以你必须重新得到这个AUTO_INCREMENT 值。与此相对，LAST_INSERT_ID() 的结果保存在服务器上，所以是持久稳固的，它不会因为别的查询语句执行而改变。

 

一些有用的DBI模块和工具

模块DBI::Utils 包含了其他一些有趣的方法（包括子模块中的方法）：
• DBI::Utils::measure 接受一个block，然后计算执行这个block需要多长时间:
• elapsed = DBI::Utils::measure do
• dbh.do(query)
• end
• puts "Query: " + query
• puts "Elapsed time: " + elapsed.to_s
• 模块 DBI::Utils::TableFormatter 中的方法ascii 用来打印一个结果集（包括表头），第一个参数是一个包含列名的数组，第二个参数是一个row对象的数组。为了打印表people的内容，可以用如下代码：
• sth = dbh.execute("SELECT * FROM people")
• rows = sth.fetch_all
• col_names = sth.column_names
• sth.finish
• DBI::Utils::TableFormatter.ascii(col_names, rows)
输出结果如下：
+----+---------+--------+
| id | name | height |
+----+---------+--------+
| 1 | Wanda | 62.5 |
| 2 | Robert | 75.0 |
| 3 | Phillip | 71.5 |
| 4 | Sarah | 68.0 |
+----+---------+--------+
• 模块DBI::Utils::XMLFormatter 包含row和table方法，用来用xml格式输出一行或者整个结果集的数据。这使得从数据库中生成xml文档变得方便简单，下面例子演示了table方法：
• DBI::Utils::XMLFormatter.table(dbh.select_all("SELECT * FROM people"))
输出结果如下：
<?xml version="1.0" encoding="UTF-8" ?>
<rows>
<row>
<id>1</id>
<name>Wanda</name>
<height>62.5</height>
</row>
<row>
<id>2</id>
<name>Robert</name>
<height>75.0</height>
</row>
<row>
<id>3</id>
<name>Phillip</name>
<height>71.5</height>
</row>
<row>
<id>4</id>
<name>Sarah</name>
<height>68.0</height>
</row>
</rows>
方法 ascii 和table 支持更多的参数以提供对结果的更多控制和更多的格式和输出方式，可以参看这个模块的源代码获取更多信息。

 

资源

本文用到的脚本可以从下面的地址下载：
http://www.kitebird.com/articles/

那里你也可以找到另一篇文章 "使用 Ruby MySQL 模块" ，这篇文章讨论了作为DBD:Mysql的基础的Ruby Mysql模块。
你会发现下面这些额外资源对你很好的使用Ruby DBI很有帮助：
• 你可以从DBI sourceforge得到Ruby DBI模块和规范说明书：
• http://ruby-dbi.sourceforge.net/
•
• 如果你想用dbi/trace模块来使用DBI执行tracing的话，必须安装AspectR Ruby 模块 。 AspectR 可以在sourceforge得到：
• http://aspectr.sourceforge.net/
•
• Ruby主页，提供了关于ruby很全面的信息：
• http://www.ruby-lang.org/
•
• 可以从这里得到mysql数据库：
• http://www.mysql.com/

Rubygems 镜像 - 淘宝网

http://ruby.taobao.org/