CODEWITHSUNDEEP
sqlmysqlruby-on-railsrubyactiverecord
Ethan
Ethan

Reputation: 60099

How do you talk SQL directly to MySQL from Ruby?

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.

Upvotes: 7

Views: 5630

Answers (6)

Nathan Long
Nathan Long

Reputation: 125892

Use Mysql2

To update this thread: I'd suggest Mysql2 now: http://rubygems.org/gems/mysql2

Upvotes: 0

user52804
user52804

Reputation: 319

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

Upvotes: 10

Tim Knight
Tim Knight

Reputation: 8356

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

Upvotes: 8

scable
scable

Reputation: 4084

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

Upvotes: 4

Keltia
Keltia

Reputation: 14743

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.

Upvotes: 4

frankodwyer
frankodwyer

Reputation: 14048

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.

Upvotes: 1

Related Questions