1. 数据库操作概述
Ruby中的数据库接口成为DBI(Database Interface),它是对底层的数据库驱动进行的抽象,形成的不同数据库之间的统一的接口。
2. 数据库操作的基本链接
在操作mysql数据库的时候需要引用外部文件“mysql”,在程序的开始部分添加:
require ""mysql;
下面给出一个基本的链接查询程序:
#encoding:utf-8 require "mysql" dbh = Mysql.real_connect("localhost","root","","infosys",3306); sql = "select * from students" res = dbh.query(sql); while row=res.fetch_row puts row[0].to_s; puts row[1].to_s; puts row[2].to_s; puts row[3].to_s; end
几乎所有的数据库链接查询都是这样操作,主机名,用户名,密码,数据库,端口,然后是查询语句,最后在查询结果集里遍历查询结果。
上面的输出结果为:
watkins@watkins:~/temp/workspace/ruby$ ruby db.rb 1 weisong male 1881******* watkins@watkins:~/temp/workspace/ruby$
3. 使用MySQL模块查询结果集
在使用query方法执行完查询语句以后,如果该SQL 语句有返回值,那么返回的是一个Mysql::Result类的实例。
可以使用fetch_row来取得结果集中的一行。
#encoding:utf-8 require "mysql" dbh = Mysql.real_connect("localhost","root","","infosys",3306); sql = "select * from students" res = dbh.query(sql); while row=res.fetch_row puts row[0].to_s; puts row[1].to_s; puts row[2].to_s; puts row[3].to_s; end
也可以直接使用结果集自身的迭代来进行遍历结果集中的数据:
#encoding:utf-8 require "mysql" dbh = Mysql.real_connect("localhost","root","","infosys",3306); sql = "select * from students" res = dbh.query(sql); #while row=res.fetch_row # puts row[0].to_s; # puts row[1].to_s; # puts row[2].to_s; # puts row[3].to_s; #end puts "***********************************"; res.each do |row| puts "#{row[0]} \t #{row[1]} \t #{row[2]}"; end
4. 向数据写入特殊字符
在应用程序中,难免有时候会向数据库中写入特殊字符,例如 单引号,双引号,分号等等,向数据库中保存这些符号需要使用escape_string方法来处理这些特殊字符。
#encoding:utf-8 require "mysql" dbh = Mysql.real_connect("localhost","root","","infosys",3306); #sql = "select * from students" msg = "this is a legal text 4\"3' "; puts msg; msg = dbh.escape_string("this is a legal text 4\"3'' "); sql = "insert into students values(3,'ccc','male','156896532','#{msg}')"; res = dbh.query(sql); puts dbh.affected_rows; dbh.close; #while row=res.fetch_row # puts row[0].to_s; # puts row[1].to_s; # puts row[2].to_s; # puts row[3].to_s; #end #puts "***********************************"; #res.each do |row| # puts "#{row[0]} \t #{row[1]} \t #{row[2]}"; #end
5. 查询结果集元数据
可以使用num_rows方法 , num_fields方法获取查询结果集中的行数,列数。
还可以使用fetch_field获取字符,获得这个字段的各种属性。
#encoding:utf-8 require "mysql" dbh = Mysql.real_connect("localhost","root","","infosys",3306); sql = "select * from students" #msg = "this is a legal text 4\"3' "; #puts msg; #msg = dbh.escape_string("this is a legal text 4\"3'' "); #sql = "insert into students values(3,'ccc','male','156896532','#{msg}')"; res = dbh.query(sql); puts dbh.affected_rows; dbh.close; #while row=res.fetch_row # puts row[0].to_s; # puts row[1].to_s; # puts row[2].to_s; # puts row[3].to_s; #end #puts "***********************************"; #res.each do |row| # puts "#{row[0]} \t #{row[1]} \t #{row[2]}"; #end puts res.num_rows; puts res.num_fields; while field=res.fetch_field puts field.name; puts field.table; puts field.type; puts field.length; end
上面程序的输出:
watkins@watkins:~/temp/workspace/ruby$ ruby db.rb 3 3 5 id students 3 11 name students 253 45 sex students 253 45 phonenumber students 253 45 address students 253 45 watkins@watkins:~/temp/workspace/ruby$