Ruby で自家製スクリプトを書く 11

SQL 文に接尾辞を追加した場合に対応させる

まずは、接尾辞がない場合の出力結果を確認する。
config.yaml

filename:
  csv: sample.csv
  sql: sample.sql
encoding:
  output: UTF-8
  input: Shift-JIS
csv:
  fs: ', '
  quote: '"'
  headers: false
fields:
   - number: 2     # CSV のフィールドの番号
     name: name    # CSV のフィールドの名称
   - number: 3
     name: address
   - number: 4
     name: zip
sql:
  prefix: INSERT INTO `sample_table`                # SQL 文の接頭辞
  infix: VALUES                                     # SQL 文の接中辞
  # 接尾辞をコメントする
  #suffix: ON DUPLICATE KEY UPDATE `updated` = NOW() # SQL 文の接尾辞
  delimiter: ;                                      # SQL 文の区切り文字

csv2sql.rb

#!/bin/env ruby
$KCODE='utf8'

require 'yaml'
require 'kconv'
require 'rubygems'
require 'fastercsv'

config_file = 'config.yaml'
configs = YAML.load_file(config_file)

def convert_char_encoding(encoding, data)
  begin
    case encoding
      when 'jis'
        return Kconv.tojis(data)
      when 'Shfit-JIS'
        return Kconv.tosjis(data)
      when 'EUC-JP'
        return Kconv.toeuc(data)
      when 'UTF-8'
        return Kconv.toutf8(data)
      else
        exit(1)
    end
  rescue SystemExit => e
    p 'set invalid charcter encoding or nil.'
    exit(0)
  end
end

day = Time.now
current_time = day.strftime("%Y%m%d_%H%M%S")

fp = File.open(current_time + configs['filename']['sql'], 'w+')

FasterCSV.foreach(configs['filename']['csv'], {:col_sep => configs['csv']['fs'], :quote_char => configs['csv']['quote'], :headers => configs['csv']['headers']}) do |row|
  values = []
  column_names = []
  configs['fields'].each do |filed|
    number = filed['number'] - 1
    if (configs['encoding']['input'] == configs['encoding']['output'])
      values.push row[number]
    else
      values.push convert_char_encoding(configs['encoding']['output'], row[number])
    end
    column_names.push filed['name']
  end
  sql = configs['sql']['prefix'] + ' (`' + column_names.join('`, `') + '`) ' +  configs['sql']['infix'] + " ('" + values.join("', '") + "')"
  if configs['sql']['suffix']
    sql = sql + ' ' + configs['sql']['suffix']
  end
  p sql
  fp.puts sql + configs['sql']['delimiter'] + "\n"
end
fp.close

実行結果。

% ruby csv2sql.rb
"INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('myname', 'myaddress', '123-4567')"
"INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('yourname', 'youraddress', '890-1234')"
"INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hisname', 'hisaddress', '')"
"INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hername', 'heraddress', 'A56-7890')"
% cat 20080525_180734sample.sql 
INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('myname', 'myaddress', '123-4567');
INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('yourname', 'youraddress', '890-1234');
INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hisname', 'hisaddress', '');
INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hername', 'heraddress', 'A56-7890');

接尾辞を追加した場合

filename:
  csv: sample.csv
  sql: sample.sql
encoding:
  output: UTF-8
  input: Shift-JIS
csv:
  fs: ', '
  quote: '"'
  headers: false
fields:
   - number: 2     # CSV のフィールドの番号
     name: name    # CSV のフィールドの名称
   - number: 3
     name: address
   - number: 4
     name: zip
sql:
  prefix: INSERT INTO `sample_table`                # SQL 文の接頭辞
  infix: VALUES                                     # SQL 文の接中辞
  # コメントを外す
  suffix: ON DUPLICATE KEY UPDATE `updated` = NOW() # SQL 文の接尾辞
  delimiter: ;                                      # SQL 文の区切り文字

実行結果。

% ruby csv2sql.rb              
"INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('myname', 'myaddress', '123-4567') ON DUPLICATE KEY UPDATE `updated` = NOW()"
"INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('yourname', 'youraddress', '890-1234') ON DUPLICATE KEY UPDATE `updated` = NOW()"
"INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hisname', 'hisaddress', '') ON DUPLICATE KEY UPDATE `updated` = NOW()"
"INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hername', 'heraddress', 'A56-7890') ON DUPLICATE KEY UPDATE `updated` = NOW()"
% cat 20080525_181445sample.sql 
INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('myname', 'myaddress', '123-4567') ON DUPLICATE KEY UPDATE `updated` = NOW();
INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('yourname', 'youraddress', '890-1234') ON DUPLICATE KEY UPDATE `updated` = NOW();
INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hisname', 'hisaddress', '') ON DUPLICATE KEY UPDATE `updated` = NOW();
INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hername', 'heraddress', 'A56-7890') ON DUPLICATE KEY UPDATE `updated` = NOW();