How do you talk SQL directly to MySQL from Ruby?

Question

I want to write a script in Ruby to clean up some messed up keys in several copies of the same MySQL schema. I'd like to do something like SHOW CREATE TABLE, then look at what comes back and delete keys if they exist.

I know in the Rails environment you can do this...

ActiveRecord::Base.connection.execute( some sql )

But what you get back is a "Result" object. For this task I need a String so I can analyze it and act accordingly.

Answer 1

There is probably a better way to do that programmatically, however if you really want to drive the interactive commands and parse the results, then expect may be more suitable. You could still kick off expect from your ruby script.

Answer 2

If you don't want to use ActiveRecord an ORM may be a bit complicated for your usage right now), you can still use the ruby-mysql library or even better IMHO is to use the Ruby DBI/DBD library (here) which has DBD drivers for mysql & postgresql out-of-the-box.

That way, you can issue straight SQL statements like this

require "dbi"
require "dbi/dbrc"

# == Configuration

DB = "sympa"
HOST = "saphir"

cnt = 0
dup = 0

# == Crude option processing
#
list_name = ARGV.shift.to_s
file = ARGV.shift.to_s

db = DBI::DBRC.new(DB)
DBI.connect(db.dsn + ":#{HOST}", db.user, db.password) do |dbh|
  date = Time.now.asctime

  if not list_name or list_name == "" then
    puts "List name is mandatory"
    exit 1
  end

  req1 = <<-"EOR"
insert into user_table (email_user,lang_user)
values (?, ?)
  EOR
...
req2 = <<-"EOR"
insert into subscriber_table
(user_subscriber, list_subscriber, visibility_subscriber,
date_subscriber, reception_subscriber) values (?, ?, ?, NOW(), ?)
EOR

sth1 = dbh.prepare(req1)
sth2 = dbh.prepare(req2)
...
#
# Insertion in user_table
#
begin
  sth1.execute(line, "en")
  cnt += 1
rescue DBI::DatabaseError => err
  $stderr.puts("DBI: #{err}")
end

dbi/dbrc is a useful module that enables you to avoid putting login&password directly in the script. See there.

Answer 3

You could check the mysql-ruby gem.

Here is a write-up on how to use it: Using the Ruby MySQL Module

More can be found via google

Answer 4

I would use the mysql-ruby gem and you would do something like this:

require 'mysql'

m = MySQL.new("localhost", "username", "password", "database")
r = m.query("SELECT * FROM people ORDER BY name")
r.each_hash do |f|
  print "#{f['name']} - #{f['email']}"
end

Answer 5

This should help you:

>> result = ActiveRecord::Base.connection.execute("SHOW TABLES")
=> #<Mysql::Result:0x37ecb30>

>> result.class.instance_methods - Object.instance_methods
=> ["all_hashes", "field_seek", "row_tell", "fetch_field_direct", "free", "field_tell", "fetch_lengths", "num_fields", "data_seek", "fetch_row", "num_rows", "fetch_field", "each", "each_hash", "fetch_hash", "row_seek", "fetch_fields"]

Look at #all_hashes on the MySql::Result instance