自己要写一个将csv文件数据导入数据库的Ruby程序,用到了ActiveRecord,程序如下:
require 'csv'require 'rubygems'
require 'active_record'
require 'yaml'
class_name = ARGV[0].split('/').last.split('.')[0]
eval("
class #{class_name} < ActiveRecord::Base
end
") #Create class dynamicly
class Import_csv_data
def initialize(csv_file_path) #Method initialization gets csv_file_path form command line
if csv_file_path.eql?(nil)
puts 'Please ruby the file with csv file path, such as "ruby example.rb /home/..example.csv"'
else
@table_name = csv_file_path.split('/').last.split('.')[0]
@csv_file_path = csv_file_path
@field_num = 0
end
end
def import_data() #Main Method. invoke Method read_csv_file, connect_db
if @table_name
connect_db()
all_data = read_csv_file()
1.upto(all_data.length - 1) do |data_index|
data = []
sql = make_sql(all_data, data_index)
puts sql
eval("
data = #{@table_name}.find_by_sql \"#{sql}\"
")
if data.eql?([])
Object::const_get(@table_name).new do |value|
0.upto(all_data[0].length - 2) do |num|
eval("
value.#{all_data[0][num]} = #{all_data[data_index][num]}
")
end
value.save
end
end
end
end
end
private
def read_csv_file() #Method read_csv_file is to get data from csv file, return all_data which
#is two-dimensional array.
all_data = []
CSV.open(@csv_file_path,'r') do |row|
all_data << row
@field_num = row.length - 1
end
return all_data
end
def connect_db() #Method is to connect DB
db_config = YAML.load(File.open("db_config.yaml")) #Get congfigrat
ActiveRecord::Base.establish_connection(:adapter => db_config["adapter"],
:host => db_config["host"],
:username => db_config["username"],
:password => db_config["password"],
:database => db_config["database"])
ActiveRecord::Base.pluralize_table_names = false
end
def make_sql(all_data, data_index) #Method is to make sql query
table_name = @table_name.downcase
sql = "select * from #{table_name} where "
1.upto(ARGV.length - 1) do |argv_index|
unless argv_index == 1
sql << " and "
end
sql << "#{all_data[0][ARGV[argv_index].to_i]} = #{all_data[data_index][ARGV[argv_index].to_i]}"
end
return sql
end
end
Import_csv_data.new(ARGV[0]).import_data()
功能不算复杂,编写的过程却异常艰难:
1,因为要做到通用性,根据csv文件的名字将数据导入相应的表中,所以继承于ActiveRecord::Base的类要动态生成,这里,我用了最简单的方法:eval(“ your codes ”),其实,还有很多其他的方法,例如,Object::const_set(@table_name),等等。
2,在用到ActiveRecord包时,遇到复合主键的问题,因为ActiveRecord不支持复合主键,所以我自己编写了SQL语句,但是在调用方法#{@table_name}.find_by_sql 时,总是报语法错误,反复调试后,发现在SQL语句两边加双引号 \"#{sql}\" 后,问题解决。