Ruby Sequel MySQL Insert Error

I am learning Ruby and I am using the Sequel gem to handle my model. but I keep running into an error I can't seem to hunt down. The thing that doesn't make sense is that is it failing on an insert because a field doesn't exist in a table that I am inserting into. However, I am not attempting to use the field that doesn't exist.

Here is my error:

0
{:id=>5, :name=>"barcelona"} : {:id=>4, :name=>"berlin"}
/Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/mysql.rb:175:in `query': Mysql::Error: Unknown column 'name' in 'field list' (Sequel::DatabaseError)
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/mysql.rb:175:in `block in _execute'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/database/logging.rb:33:in `log_yield'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/mysql.rb:175:in `_execute'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:34:in `block in execute'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/database/connecting.rb:250:in `block in synchronize'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/connection_pool/threaded.rb:98:in `hold'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/database/connecting.rb:250:in `synchronize'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:34:in `execute'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/mysql.rb:160:in `execute_insert'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/dataset/actions.rb:927:in `execute_insert'
    from /Users/username/.rvm/gems/ruby-2.2.0/gems/sequel-4.20.0/lib/sequel/dataset/actions.rb:336:in `insert'
    from travellingAmerican.rb:70:in `addDist'
    from travellingAmerican.rb:64:in `block (2 levels) in addLocations'
    from travellingAmerican.rb:61:in `each'
    from travellingAmerican.rb:61:in `block in addLocations'
    from travellingAmerican.rb:58:in `each'
    from travellingAmerican.rb:58:in `addLocations'
    from travellingAmerican.rb:93:in `<main>'

Here is models.rb:

#Database
require 'sequel'

def create
    DB.create_table(:locations) do
        primary_key :id
        String :name, :unique=>true
    end

    DB.create_table(:distances) do
        primary_key :id
        foreign_key :to, :locations
        foreign_key :from, :locations
        Float :miles, :default=>-1
        TrueClass :valid, :default=>0
        unique [:to, :from]
    end

end

def drop
    begin
        DB.drop_table(:distances)
    rescue
        puts "Couldn't drop Distances"
    end

    begin
        DB.drop_table(:locations)
    rescue
        puts "Couldn't drop locations"
    end

end


DB = Sequel.connect(:adapter=>'mysql', :host=>'myHost', :user=>'myUser', :password=>'myPass', :database=>'myDB')

if __FILE__ == $PROGRAM_NAME
    puts "Will Execute"
    drop
    create
    puts "Done Executing"
end

Now the file the error is actually in, travellingAmerican.rb:

#Traveling American
require './models.rb'
require 'json'




def urlCreator(cities)
    urls = []
    rootUrl = "https://maps.googleapis.com/maps/api/distancematrix/json?"
    origin = "origins="
    dest = "destinations="
    key = "myApiKey"

    cities.each do |city|
            city = city.gsub(/[" "]/, '+')
            newOrigin = origin + city + "|"
            newDest = dest
        cities.each do |inCity|
            newDest += inCity + "|"

        end
        newUrl = rootUrl + newOrigin + "&" + newDest + "&" + key
        urls.push(newUrl)
    end
    urls
end

def processFile(file)
    cities = []


    File.readlines(file).each do |line|
        line = line.strip

        if line.count(",") == 1
            line = line.split()
        else
            cities.push(line)
        end
    end
    return cities
end

def addLocations(cities)

    cities.each do |city|
        begin
            DB[:locations].insert(:name => city.downcase)
        rescue Sequel::UniqueConstraintViolation
            puts "Duplicate, not added"
        end
    end

    allLocs = DB[:locations].where(:name => cities).order(:name).all
    #if you puts allLocs it works beautifully

    for i in 0...allLocs.length
        puts i
        toLoc = allLocs[i]
        for j in (i+1)...allLocs.length
            fromLoc = allLocs[j]
            puts toLoc.to_s + " : " + fromLoc.to_s 
            #If you comment out the line below everything runs fine, minus the insertion obviously
            addDist(toLoc, fromLoc)
        end
    end
end

def addDist(tos, froms)
    #the line with the error
    DB[:distances].insert(:to=>tos, :from=>froms)
end

if __FILE__ == $PROGRAM_NAME
    popSize = -1
    file = ""

    count = 0
    ARGV.each do|arg|
        if count == 0
            popSize = arg.to_i
        else
            file = arg
        end

        count += 1
    end

    if popSize == -1 or file == ""
         abort("Usage: ruby travellingAmerican.rb popSize cityList")
    end

    cities = processFile(file)
    addLocations(cities)

    puts urlCreator(cities)

end

Lastly here is cities.csv if you would like to run this on your own machine:

San Diego
Munich
Paris
Berlin
Barcelona
Monte Carlo
Cinque Terre
Turin 
Milan
Florence
Rome
Venice

To run this on your own machine just execute ruby travellingAmerican.rb 1000 cities.csv in a shell.

I hope I have provided enough info for everyone! Thanks for looking.

Solution

There are some problems I detected:

Here you add the cities with downcase:

    cities.each do |city|
        begin
            DB[:locations].insert(:name => city.downcase)
        rescue Sequel::UniqueConstraintViolation
            puts "Duplicate, not added"
        end
    end

The you read them in a Hash:

    allLocs = DB[:locations].where(:name => cities).order(:name).all

The condition is without the downcase! So you get no result. You could do:

allLocs = DB[:locations].where(:name => cities.map(&:downcase)).order(:name).all

Later you use

        addDist(toLoc, fromLoc)

toLoc and fromLoc are both Hashes, but the should be the key for the table cities.

This should work:

        addDist(toLoc[:id], fromLoc[:id])

As an alternative you can adapt the insertion in addDist

DB[:distances].insert(:to=>tos[:id], :from=>froms[:id])

The usage of for is not ruby-like. I would use directly Sequel::Dataset#each:

selection = DB[:locations].where(:name => cities.map(&:downcase)).order(:name)
selection.each{|toLoc|
  selection.each{|fromLoc|
        puts toLoc.to_s + " : " + fromLoc.to_s 
        #If you comment out the line below everything runs fine, minus the insertion obviously
        addDist(toLoc[:id], fromLoc[:id])
   }
}

(You could discuss, if the where and order methods are really needed here)