mysql-to-sqlite.rb

#!/usr/bin/ruby

require 'rubygems'
require 'mysql'
require 'sqlite3'

def with_mysql
  db_object = Mysql.init()
  db_object.real_connect('localhost','things','1234','things')
  
  begin
    yield db_object
   ensure
    db_object.close
  end
end

def with_sqlite
  db_object = SQLite3::Database.new( "development.sqlite3" )
  
  begin
    yield db_object
   ensure
    db_object.close
  end
end

with_mysql do |mysql|

  results = mysql.query("select * from things order by ID limit 20, 3;") # get 3 for testing
  
  results.each_hash do |row|

    count_of_all+=1
    
    rh = Hash.new
    
    rh["person"] = row["Person"]
    rh["place"] = row["Place"]
    
    if row["Section"] then
      rh["section"] = row["Section"]
    end

    if row["Regulation"] && row["Regulation"] != "Not Applicable" then
      rh["regulation"] = row["Regulation"]
    end

    rh["description"] = row["Description"]

    # This bit here works, and shows all the stuff in the "rh" hash
    #rh.each do |k, v|
      #puts k + " = " + v.to_s
    #end
    
    with_sqlite do |sqlite|
      sqlite.results_as_hash = true  # thought this made sqlite3 listen to hashes..?
      
      # so this code here is supposed to get each key and value from the "rh" hash
      # and enter the value into the database using the key as the column name...
      rh.each_pair do |k, v|
        #sqlite.execute( "insert into things (:key) values (:value)", "key" = "#{k}", "value" = "#{v}" )
        #sqlite.execute( "insert into things ( #{ k } ) values ( #{ v } )" )
        sqlite.execute( "insert into things ( #{ k } ) values ( #{ v } )" )
      end
      # but none of these options seem to work - what am I doing wrong?
      # I'm sure it's just a little syntax thing that's wrong...
      
    end
      
  end

  results.free
end