Congress Database 1: From CSV to SQLite with Ruby

Coup d'etat

update politicians set name='Sen. Shereef Bishay' where id=422;

Import_the_database_with_Ruby.rb

require 'csv'
require 'sqlite3'



  def csv_to_array(file_name)
    array = []
    CSV.foreach(file_name) {|line| array <<  line}
    array
  end


# creating politician array and stripping last and first array within array
politicians = csv_to_array('Importing_Politicians_Database.csv')
politicians.pop
politicians.shift



# created politician class...don't know why
class Politician

  attr_accessor :name, :party, :location, :grade_level_1, :grade_level_2, :years_in_congress, :dw1

  def initialize(name,party,location,grade_level_1,grade_level_2,years_in_congress,dw1)
    @name, @party, @location, @grade_level_1, @grade_level_2, @years_in_congress, @dw1 = name, party, location, grade_level_1, grade_level_2, years_in_congress, dw1
  end

end





# Creating database with below schema

db =SQLite3::Database.new( "test_database.db" )

db.execute <<-SQL
  create table politicians (
    id INTEGER PRIMARY KEY,
    'Name' varchar(30),
    `Party` VARCHAR,
    `Location 1` VARCHAR,
    `Grade Level (since 1996)` DOUBLE,
    `Grade Level (112th Congress)` DOUBLE,
    `Years in Congress` INTEGER,
    `DW1 Score` FLOAT
  );
SQL




#Adding politicians to the database | Didn't know how to add objects :S
politicians.each do |politician|
  db.execute("INSERT INTO politicians ('Name', 'Party', 'Location 1', 'Grade Level (since 1996)', 'Grade Level (112th Congress)', 'Years in Congress', 'DW1 Score')
            VALUES (?, ?, ?, ?, ?, ?, ?)", [politician[0], politician[1], politician[2], politician[3], politician[4], politician[5], politician[6]])
end

Oratory skills

What 10 representatives are speaking at the lowest grade level? -> 
select * from politicians order by 'Grade_Level(since1996)' desc limit 10;

What 10 Democrat representatives are speaking at the lowest grade level? -> 
select * from politicians where party='D' order by 'Grade_Level(since1996)' desc limit 10;


What 10 Republican representatives are speaking at the lowest grade level?
select * from politicians where party='R' order by 'Grade_Level(since1996)' desc limit 10;

Query the Congress members DB

How many Republican representatives are there? -> select count(*) from politicians where Party='R'; [287]
How many Democrats? -> select count(*) from politicians where Party='D'; [238]
How about senators, Republican and Democrat? -> select count(*) from politicians where name like 'Sen%'; [100]