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();