gistfile1.rb

#!/usr/bin/env ruby
# coding: utf-8

require "mycmd"
require "csv"

class Zipdb
  def initialize(db, table, csv)
    @client = Mycmd::Configuration.connect
    @db = db
    @table = table
    @csv = csv
  end

  def create
    create_database
    create_table
    import_csv
  end

  def create_database
    execute("DROP DATABASE IF EXISTS `#{@db}`")
    execute("CREATE DATABASE `#{@db}` DEFAULT CHARACTER SET utf8")
    execute("USE `#{@db}`")
  end

  def create_table
    use_database
    execute(<<-'SQL')
      CREATE TABLE zip_code (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `jis_code` varchar(6) NOT NULL,
        `old_zip` varchar(5),
        `zip` varchar(7) NOT NULL,
        `state_kana` varchar(40),
        `city_kana` varchar(100),
        `town_kana` varchar(100),
        `state` varchar(20),
        `city` varchar(50),
        `town` varchar(50),
        PRIMARY KEY(`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      SQL
  end

  def import_csv
    use_database
    CSV.open(@csv).each do |row|
      row.map!{|x| "'#{x.strip}'"}
      execute("INSERT INTO `#{@table}` (`jis_code`, `old_zip`, `zip`, `state_kana`, `city_kana`, `town_kana`, `state`, `city`, `town`) VALUES (#{row[0..8].join(',')})")
    end
  end

  private
  def execute(sql)
    puts sql
    @client.query(sql)
  end

  def use_database
    @client.query("USE `#{@db}`")
  end
end

Zipdb.new("sample", "zip_code", "KEN_ALL.CSV").create