Talking Directly to a MySQL Database


You want to send SQL queries and commands directly to a MySQL database.


Do you really need to do this? Almost all the time, its better to use the generic DBI library. The biggest exception is when you e writing a a Rails application, and you need to run a SQL command that you can express with ActiveRecord.[8]

[8] You could use DBI with ActiveRecord, but most Rails programmers go straight to the database.

If you really want to communicate directly with MySQL, use the Ruby bindings to the MySQL client library (found in the mysql gem). It provides an interface thats pretty similar to DBIs.

Heres a MySQL-specific version of the method with_db, defined in this chapters introduction. It returns a Mysql object, which you can use to run queries or get server information.

	require mysql

	def with_db
	 dbh = Mysql.real_connect(localhost, cookbook_user, password,
	 yield dbh

The Mysql#query method runs any SQL statement, whether its a SELECT query or something else. When it runs a query, the return value is a result-set object (a MysqlRes); otherwise, its nil. Here it is running some SQL commands:

	with_db do |db|
	 db.query(drop table if exists secrets)
	 db.query(create table secrets( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	 secret LONGTEXT ))
	 db.query(%{insert into secrets(secret) values
	 ("Oh, MySQL, you
e the only one who really understands me.")})

And heres a query:

	with_db do |db|
	 res = db.query(select * from secrets)
	 res.each { |row| puts "#{row[0]}: #{row[1]}" }
	# 1: Oh, MySQL, you
e the only one who really understands me.


Like the database connection itself, the result set you get from query wants to be closed when you e done with it. This calls for yet another instance of the pattern seen in with_db, in which setup and cleanup are delegated to a method that takes a code block. Heres some code that alters query to take a code block:

	class Mysql
	 alias :query_no_block :query
	 def query(sql)
	 res = query_no_block(sql)
	 return res unless block_given?
	 yield res
	 ensure if res

Now we can write more concise query code, and not have to worry about freeing the result set:

	with_db do |db|
	 db.query(select * from secrets) do |res|
	 res.each { |row| puts "#{row[0]}: #{row[1]}" }
	# 1: Oh, MySQL, you
e the only one who really understands me.

The method MysqlRes#each yields you the rows of a result set as arrays. MysqlRes#each_hash also gives you one row at a time, but in hash form: you can access a rows fields by name instead of position. MysqlRes#num_rows gives you the number of rows matched by a query.

	with_db do |db|
	 db.query(select * from secrets) do |res|
	 puts "#{res.num_rows} row(s) matched:"
	 res.each_hash do |hash|
	 hash.each { |k,v| puts " #{k} = #{v}" }
	# 1 row(s) matched:
	# id = 1
	# secret = Oh, 
MySQL, you
e the only one who really understands me.

The MySQL interface provides no protection against SQL injection attacks. If you e sending SQL containing the values of possibly tainted variables, youll need to quote those values yourself.

See Also

  • Recipe 13.15, "Preventing SQL Injection Attacks," for more on SQL injection
  • "Using the Ruby MySQL Module" (
  • MySQL bindings (



Date and Time



Files and Directories

Code Blocks and Iteration

Objects and Classes8

Modules and Namespaces

Reflection and Metaprogramming


Graphics and Other File Formats

Databases and Persistence

Internet Services

Web Development Ruby on Rails

Web Services and Distributed Programming

Testing, Debugging, Optimizing, and Documenting

Packaging and Distributing Software

Automating Tasks with Rake

Multitasking and Multithreading

User Interface

Extending Ruby with Other Languages

System Administration

Ruby Cookbook
Ruby Cookbook (Cookbooks (OReilly))
ISBN: 0596523696
EAN: 2147483647
Year: N/A
Pages: 399 © 2008-2020.
If you may any questions please contact us: