RubyによるMySQLデータエクスポート/インポート

サンプルDBデータを作成するのに、DBデータを「エクスポート→Excel編集→インポート」の流れで作業するのを想定したスクリプトを書いてみました。今後も使えるかもと思い、調べたことなどメモしておきます。

ざっくり仕様

メインのロジックはdata_loader.rbに実装
ダウンロード実行用に叩くためのexp.rbとアップロード実行用に叩くためのimp.rbを作成
exp.rbとimp.rbの引数にテーブル名（スペース区切りで複数指定可）を指定すると、指定されたテーブルのみエクスポートまたはインポート
- 何も指定しない場合はすべてのテーブルを対象

困ったこと

Ruby（mysql2）からローカルのMySQLに接続できない
- localhostを127.0.0.1にしたら解決
インポートファイルが空白だと、文字列項目だと空文字、数値項目だと0になってしまうがNULLにしたい
- 「\N」にしておくとNULLになる
項目のダブルクォートは使いたくないが、改行が入っていると困る
- エクスポートしたテキストファイルからCtrl-AしてExcelに貼り付ける想定なので、改行のままだと扱いづらい…ということで、エクスポート時に改行を「<LF>」に置換、アップロード時に改行に戻すことに
MySQL上で「on Update CURRENT_TIMESTAMP」を設定しているタイムスタンプ項目はどう扱うのか？
- 値を指定してもNULL、「\N」を指定してもNULLになってしまう…
- インポートファイルから項目自体をなくしたら解決したので、タイムスタンプ項目はエクスポートの際に出力しないことに
アップロード用のワークファイルはCR+LFではなくLFで出力したい
- ワークファイルを開く際のモードはバイナリモード（wではなくwb）を使う

ソースコード

data_loader.rb

# -*- coding: utf-8 -*-
require 'rubygems'
require 'mysql2'

class DataLoader
  MySQL = {
    :host => "127.0.0.1",
    :username => "<username>",
    :password => "<password>",
    :database => "<database>",
  }

  OutDir = "./out"
  InDir = "./in"

  def initialize
    @conn = Mysql2::Client.new(MySQL)
    @conn.query "set character set utf8"
  end

  def exp(args)
    puts "---- start"
    unless File.exist?(OutDir)
      Dir.mkdir(OutDir)
    end

    tables.each_with_index do |table, i|
      if args.empty? || args.include?(table)
        print "##{i + 1} #{table}"

        sql = "select * from #{table}"
        result = @conn.query(sql)

        out = File.open(OutDir + "/#{table}.dat", "wb")

        tmp = []
        result.fields.each do |fld|
          tmp << fld unless fld == "<timestamp>"
        end
        out.puts tmp.join("\t")

        result.entries.each do |rec|
          tmp = []
          result.fields.each do |fld|
            case fld
            when "<timestamp>"
              nil
            when "<create_at>", "<update_at>"
              tmp << "1970-01-01 00:00:00"
            when "<create_by>", "<update_by>"
              tmp << "system"
            else
              tmp << (rec[fld].instance_of?(String) ? rec[fld].gsub(/\r?\n/, '<LF>') : rec[fld])
            end
          end
          out.puts tmp.join("\t")
        end

        out.close

        puts " ---> #{result.entries.length} records exported"
      end
    end
    puts "---- end"
  end

  def imp(args)
    puts "---- start"
    tables.each_with_index do |table, i|
      if args.empty? || args.include?(table)
        file = "#{InDir}/#{table}.dat"
        if File.exist?(file)
          print "##{i + 1} #{table}"

          impfile = File.expand_path(File.dirname(__FILE__) + "/#{InDir}/_work_.dat")
          out = File.open(impfile, "wb")

          open(file, "r:utf-8") do |fh|
            while line = fh.gets
              tmp = line.split(/\t/)
              tmp.each_with_index do |item, i|
                case item
                when ""
                  tmp[i] = "\\N"
                when /<LF>/
                  tmp[i] = "\"" + item.gsub(/<LF>/, "\n") + "\""
                end
              end
              out.puts tmp.join("\t")
            end
          end

          out.close

          sql = "truncate table #{table}"
          @conn.query sql

          sql = %!load data local infile '#{impfile}' into table #{table} fields optionally enclosed by '"' terminated by '\\t' ignore 1 lines!
          @conn.query sql

          sql ="select count(1) as total from #{table}"
          result = @conn.query(sql)
          total = result.entries.first["total"]

          puts " ---> #{total} records imported"
        end
      end
    end
    puts "---- end"
  end

  private
    def tables
      sql = <<-EOT
select *
from information_schema.tables
where table_schema = schema()
order by table_name
      EOT
      buf = []
      @conn.query(sql).entries.each do |rec|
        buf << rec["TABLE_NAME"]
      end
      buf
    end
end

exp.rb

# -*- coding: utf-8 -*-
require File.dirname(__FILE__) + "/data_loader.rb"

DataLoader.new.exp ARGV

imp.rb

# -*- coding: utf-8 -*-
require File.dirname(__FILE__) + "/data_loader.rb"

DataLoader.new.imp ARGV

CodeLog

開発メモ

RubyによるMySQLデータエクスポート/インポート

ざっくり仕様

困ったこと

ソースコード

data_loader.rb

exp.rb

imp.rb