Using Ruby CSV creates Rails Records where string fields aren't queryable

I am trying to load seed data into my Rails application from a CSV file. I initially installed the fastercsv gem, only to find out that fastercsv has been deprecated in favor of the CSV library as of ruby 1.9. So I switched over to CSV after getting a very helpful error telling me to switch over.

Now, however, I am getting the strangest phenomenon where when I load my data everything looks normal, but I can't seem to query against the string fields. The string fields are populated by what appears to be the correct strings, but I can't access them. I can query against any of the number fields, and results will return, but not the string fields. I tried playing with the delimiter for quotes, but to no avail. I even stripped out all the quotes from my csv file, but still I couldn't query against the string fields. Below is my code, and some sample queries and returns from the Rails Console.

# seeds.rb
# ================

require 'csv'

directory = "db/init_data/"

file_name = "players.seed"
path_to_file = directory + file_name
puts 'Loading Player records'
# Pre-load All Player records
n=0
CSV.foreach(path_to_file) do |row|
  Player.create! :first_name => row[1], :last_name => row[2], :position_id => row[5], :weight => row[6], :height => row[7], :year => row[8], :home_state => row[9], :home_town => row[10], :home_country => row[11], :high_school_id => row[12], :name => row[13]      
n=n+1
end

Here are the first two records from my seed file.

# players.seed
"1","Allerik","Freeman","2011-10-11 22:21:21.230247","2011-10-11 22:21:21.230247","2","210","76","2013","NC","Charlotte","USA","1","Allerik Freeman"
"2","Kasey","Hill","2011-10-11 22:21:21.262409","2011-10-11 22:21:21.262409","1","170","73","2013","FL","Eustis","USA","2","Kasey Hill"

This is what I'm getting when I enter the rails console. It works fine if I want to query a number like year for example.

ruby-1.9.2-p290 :002 > Player.find_all_by_year(2013)
  Player Load (0.7ms)  SELECT "players".* FROM "players" WHERE "players"."year" = 2013
 => [#<Player id: 1, first_name: "Allerik", last_name: "Freeman", created_at: "2011-10-12 20:52:16", updated_at: "2011-10-12 20:52:16", position_id: 2, weight: 210, height: 76, year: 2013, home_state: "NC", home_town: "Charlotte", home_country: "USA", high_school_id: 1, name: "Allerik Freeman">, #<Player id: 2, first_name: "Kasey", last_name: "Hill", created_at: "2011-10-12 20:52:16", updated_at: "2011-10-12 20:52:16", position_id: 1, weight: 170, height: 72, year: 2013, home_state: "FL", home_town: "Eustis", home_country: "USA", high_school_id: 2, name: "Kasey Hill">]

But if I try to query by say last name, I get nothing, even though it shows me that the last name is present on the previous query.

ruby-1.9.2-p290 :004 > Player.find_all_by_last_name("Freeman")
  Player Load (0.3ms)  SELECT "players".* FROM "players" WHERE "players"."last_name" = 'Freeman'
 => [] 

The only way I could get it to work was to put it in an extra set of double quotation marks (escaped) using the hash variable notation, which got all my string records into the database in quotations, then I used a delete command to strip the quotes back out.

  n=0
  CSV.foreach(path_to_file) do |row|
    Player.create! :first_name => "\"#{row[1]}\"", :last_name => "\"#{row[2]}\"", :position_id => row[5], :weight => row[6], :height => row[7], :year => row[8], :home_state => "\"#{row[9]}\"", :home_town => "\"#{row[10]}\"", :home_country => "\"#{row[11]}\"", :high_school_id => row[12], :name => "\"#{开发者_StackOverflowrow[13]}\""      
    n=n+1
  end
  puts "There\'s too many playas to hate, we just loaded #{n} of \'em"

  @players = Player.all
  @players.each do |player|
    fname = player.first_name
    player.first_name = fname.delete("\"")
    lname = player.last_name
    player.last_name = lname.delete("\"")
    pcity = player.home_town
    player.home_town = pcity.delete("\"")
    pst = player.home_state
    player.home_state = pst.delete("\"")
    pcountry = player.home_country
    player.home_country = pcountry.delete("\"")
    pname = player.name
    player.name = pname.delete("\"")
    player.save!
  end  

Then I could query against the string data.

ruby-1.9.2-p290 :005 > Player.find_all_by_last_name("Freeman")
  Player Load (0.6ms)  SELECT "players".* FROM "players" WHERE "players"."last_name" = 'Freeman'
 => [#<Player id: 1, first_name: "Allerik", last_name: "Freeman", created_at: "2011-10-12 20:52:16", updated_at: "2011-10-12 20:52:16", position_id: 2, weight: 210, height: 76, year: 2013, home_state: "NC", home_town: "Charlotte", home_country: "USA", high_school_id: 1, name: "Allerik Freeman">, #<Player id: 59, first_name: "Austin", last_name: "Freeman", created_at: "2011-10-12 20:55:16", updated_at: "2011-10-12 20:55:16", position_id: 2, weight: 210, height: 76, year: 2007, home_state: "MD", home_town: "Hyattsville", home_country: "USA", high_school_id: nil, name: "Austin Freeman">] 

Obviously this is not a preferred method, as it doubled my load time, but I was honestly at my wit's end.

Any help would be greatly appreciated.

As requested here I've added the schema.rb

# schema.rb
# ===================
# encoding: UTF-8
# ...

ActiveRecord::Schema.define(:version => 20111007214728) do

#...

  create_table "players", :force => true do |t|
    t.string   "first_name"
    t.string   "last_name"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "position_id"
    t.integer  "weight"
    t.integer  "height"
    t.integer  "year"
    t.string   "home_state"
    t.string   "home_town"
    t.string   "home_country"
    t.integer  "high_school_id"
    t.string   "name"
  end

# ...

end

Here are screenshots of the database as viewed by my SQLite Database Browser as requested.

It looks like there is a similar issue here in the ruby forums, and that it probably has something to do with the encoding, but I'll need to do a lot more research into the encoding to figure this out.

Try adding # encoding: UTF-8 at the very top of players.seed

# encoding: UTF-8
# players.seed
...

please check the following:

• the encoding of strings in your database, e.g. it should probably be UTF-8

  how did you create your database? In MySQL you should use something like this:

  create database DatabaseName DEFAULT CHARACTER SET utf8;

• the encoding of the strings you get out of the CSV file when you parse / read it

See: http://www.ruby-doc.org/stdlib-1.9.2/libdoc/csv/rdoc/CSV.html

You could also try to read the CSV file directly to check the encoding of your strings when they are read from file.

edit:

Some sources say that SQLite only supports ISO-8859-1 encoding, and only UTF-8 if it's specified at compile-time.. that could be a problem. Which version of SQLite do you use? http://refdb.sourceforge.net/manual/ch08s09.html

On the other hand, this source says that SQLite 3.x uses UTF-8 http://www.sqlite.org/version3.html

Try adding "# coding: utf-8" to the first line in your seeds.rb

# coding: utf-8
# seeds.rb
# ================
...