原文链接：http://blog.csdn.net/ruby_cn/article/details/174293
转这个是因为经常记不住这个用到的，因为python比较熟，而ruby不熟，2个语言思路不一样，所以只取其一。
数据库连接
# 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
处理不返回结果集的查询
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
处理返回结果集的查询
处理返回结果集的查询
sth = dbh.prepare(statement) sth.execute ... fetch rows ... sth.finish
或
sth = dbh.execute(statement) ... fetch rows ... sth.finish
各种循环取值
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
可以用by_index 或 by_field 来通过顺序或者名字访问字段值：
val = row.by_index(2)
val = row.by_field("height")
字段值也可以将row对象当成数组来取得：
val = row[2]
val = row["height"]
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的别名。
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。