gistfile1.rb

require 'sqlite3'
require 'csv'


# If you want to overwrite your database you will need 
# to delete it before running this file
$db = SQLite3::Database.new "politicians.db"

module PoliticianDB
  def self.setup
    $db.execute(
      <<-SQL
        CREATE TABLE politicians (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name VARCHAR(64) NOT NULL,
          location_1 CHAR(2) NOT NULL,
          grade_since_1996 REAL NOT NULL,
          grade_112th REAL NOT NULL,
          years_in_congress INT NOT NULL,
          dw1_score REAL NOT NULL
        );
      SQL
    )
  end

  def self.seed
    CSV.foreach("politician_data.csv", :headers => true, :header_converters => :symbol) do |row|
      write_to_db(row) 
    end
  end

  def write_to_db(csv_object)
    $db.execute(
      <<-SQL
        INSERT INTO politicians 
          (name, location_1, grade_since_1996, grade_112th, years_in_congress, dw1_score)
        VALUES
          (#{csv_object[:name]}, #{csv_object[:location_1]}, #{csv_object[:grade_level_since_1996]}, #{csv_object[:grade_level_112th_congress]}, #{csv_object[:years_in_congress]}, #{csv_object[:dw1_score]})
      SQL
    )
  end

  def self.drop
    $db.execute(
        "DROP TABLE politicians;"
      )
  end

end

PoliticianDB.setup
PoliticianDB.seed