ConorFl/congess_parser_answer.txt

## congess_parser_answer.txt
Count Queries

1. There are 240 Republican representatives.
2. There are 187 Democratic representatives.
3. There are 51 Democratic senators, 47 Republican senators.

The questions asks for "representatives", but the details below it ask to specify if it's a Rep or Sen, so we've
compiled the lists using all Rep's and Sen's.

select name, location, party, substr(name, 0, 4), years_in_congress from congress_members order by grade_level_since_1996 limit 10;
Top 10 reps
name                location                                   party       substr(name, 0, 4)  years_in_congress
------------------  -----------------------------------------  ----------  ------------------  -----------------
Rep. Mick Mulvaney  SC (33.99855000018255, -81.0452500001872)  R           Rep                 1
Rep. Rob Woodall    GA (32.83967999993223, -83.62758000031658  R           Rep                 1
Sen. Rand Paul      KY (37.645969999815804, -84.7749699999653  R           Sen                 1
Rep. Sean Duffy     WI (44.39319000021851, -89.8163600002137)  R           Rep                 1
Rep. Tim Griffin    AR (34.748649999697875, -92.2744899997135  R           Rep                 1
Rep. W. Todd Akin   MO (38.63578999960896, -92.5663000000448)  R           Rep                 11
Rep. Vicky Hartzle  MO (38.63578999960896, -92.5663000000448)  R           Rep                 1
Rep. Tom Graves     GA (32.83967999993223, -83.62758000031658  R           Rep                 3
Rep. David Schweik  AZ (34.86596999961597, -111.7638099997315  R           Rep                 1
Sen. Ron Johnson    WI (44.39319000021851, -89.8163600002137)  R           Sen                 1


Top 10 Dem reps
select name, location, party, substr(name, 0, 4), years_in_congress from congress_members where party = 'D' order by grade_level_since_1996 limit 10;
name                 location                                      party       substr(name, 0, 4)  years_in_congress
-------------------  --------------------------------------------  ----------  ------------------  -----------------
Rep. John Garamendi  CA (37.638300000444815, -120.99958999997835)  D           Rep                 3
Rep. John Lewis      GA (32.83967999993223, -83.62758000031658)    D           Rep                 25
Rep. Colleen Hanabu  HI (21.304770000335395, -157.85761000030112)  D           Rep                 1
Rep. Keith Ellison   MN (46.3556499998478, -94.79419999982997)     D           Rep                 5
Rep. Tim Ryan        OH (40.06020999969189, -82.40426000019869)    D           Rep                 9
Sen. Barbara Boxer   CA (37.638300000444815, -120.99958999997835)  D           Sen                 17
Sen. Kent Conrad     ND (47.475320000018144, -100.11841999998285)  D           Sen                 23
Sen. Chuck Schumer   NY (42.82699999955048, -75.54396999981549)    D           Sen                 11
Sen. Claire McCaski  MO (38.63578999960896, -92.5663000000448)     D           Sen                 3
Sen. Harry Reid      NV (39.49323999972637, -117.07183999971608)   D           Sen                 23

Top 10 Rep reps
select name, location, party, substr(name, 0, 4), years_in_congress from congress_members where party = 'R' order by grade_level_since_1996 limit 10;
name                location                                   party       substr(name, 0, 4)  years_in_congress
------------------  -----------------------------------------  ----------  ------------------  -----------------
Rep. Mick Mulvaney  SC (33.99855000018255, -81.0452500001872)  R           Rep                 1
Rep. Rob Woodall    GA (32.83967999993223, -83.62758000031658  R           Rep                 1
Sen. Rand Paul      KY (37.645969999815804, -84.7749699999653  R           Sen                 1
Rep. Sean Duffy     WI (44.39319000021851, -89.8163600002137)  R           Rep                 1
Rep. Tim Griffin    AR (34.748649999697875, -92.2744899997135  R           Rep                 1
Rep. W. Todd Akin   MO (38.63578999960896, -92.5663000000448)  R           Rep                 11
Rep. Vicky Hartzle  MO (38.63578999960896, -92.5663000000448)  R           Rep                 1
Rep. Tom Graves     GA (32.83967999993223, -83.62758000031658  R           Rep                 3
Rep. David Schweik  AZ (34.86596999961597, -111.7638099997315  R           Rep                 1
Sen. Ron Johnson    WI (44.39319000021851, -89.8163600002137)  R           Sen                 1


## importing_political_db.rb
require 'csv'
require 'sqlite3'

class CongressParser
  $db = SQLite3::Database.new "congress_members.db"

  attr_accessor :file, :congress_members
  def initialize(file)
    @file = file
    @congress_members = []
  end

  def parse_congress_members
    CSV.foreach(@file) do |row|
      @congress_members << ({
        name: row[0],
        party: row[1],
        location: row[2],
        grade_level_since_1996: row[3],
        grade_level: row[4],
        years_in_congress:row[5],
        dw1_score: row[6]
      })
    end
  end

  def congress_array_to_db
    @congress_members.each do |member|
      $db.execute("INSERT INTO congress_members
        (name, party, location, grade_level_since_1996, grade_level, years_in_congress, dw1_score, created_at, updated_at)
        VALUES
        ('#{member[:name]}','#{member[:party]}', '#{member[:location]}', #{member[:grade_level_since_1996]},
          #{member[:grade_level]}, #{member[:years_in_congress]}, #{member[:dw1_score]}, DATETIME('now'), DATETIME('now'))
        ")
    end
  end

  def initialize_db
      # return if File.exists? "students.db"
    # if you don't want to overwrite your database, uncomment the above.
    $db.execute(<<-SQL
        CREATE TABLE congress_members (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name VARCHAR(64) NOT NULL,
          party VARCHAR(64) NOT NULL,
          location VARCHAR(64) NOT NULL,
          grade_level_since_1996 REAL NOT NULL,
          grade_level REAL NOT NULL,
          years_in_congress INTEGER NOT NULL DEFAULT '',
          dw1_score REAL NOT NULL,
          created_at DATETIME NOT NULL,
          updated_at DATETIME NOT NULL
          );
      SQL
      )
  end
end

module Sqloperations
  # def self.show_all
  #   $db.execute "select * from #{self.table_name}"
  # end


  def id
    $db.execute "SELECT id FROM #{self.table_name} WHERE name='#{@name}' AND location='#{@location}' AND party='#{@party}'"
  end

  def self.included(base)
    base.extend ClassMethods
  end

  module ClassMethods
    def show_all
      # puts "CLASS METHODS"
      puts self
      $db.execute "select * from #{self::TABLE_NAME};"
    end
    def count
      $db.execute "SELECT COUNT(*) FROM #{self::TABLE_NAME}"
    end
    def show_all_by(attribute, value)
      $db.execute "select * from #{self::TABLE_NAME} where #{attribute}='#{value}'"
    end
    def delete(attribute, value)
      $db.execute "DELETE FROM #{self::TABLE_NAME} WHERE #{attribute}='#{value}'"
    end

    def where(attribute, value)
      $db.execute "SELECT * FROM #{self::TABLE_NAME} WHERE #{attribute.gsub('?', value.to_s)}"
    end

    # def update
    #   $db.execute "UPDATE #{self::TABLE_NAME} WHERE id='#{id}'"
    # end
  end
end

class CongressMember
  include Sqloperations
  TABLE_NAME = 'congress_members'

  attr_accessor :name, :party, :location, :grade_level_since_1996, :grade_level, :years_in_congress, :dw1_score
  def initialize(args)
    @name = args[:name]
    @party = args[:party]
    @location = args[:location]
    @grade_level_since_1996 = args[:grade_level_since_1996]
    @grade_level = args[:grade_level]
    @years_in_congress = args[:years_in_congress]
    @dw1_score = args[:dw1_score]
    @id = nil
    # @created_at = Time.now
    # @updated_at = Time.now
    # @table_name = "congress_members"
  end

  def save!
    if !@id
      $db.execute(
        "INSERT INTO #{TABLE_NAME}
        VALUES (NULL,?,?,?,?,?,?,?, DATETIME('now'), DATETIME('now'))", @name, @party, @location, @grade_level_since_1996, @grade_level, @years_in_congress, @dw1_score
        )
      @id = $db.last_insert_row_id
    else
      $db.execute(
        "UPDATE #{TABLE_NAME} SET
        name = '#{@name}', party = '#{@party}', location = '#{@location}', grade_level_since_1996 = #{grade_level_since_1996},
        grade_level = #{@grade_level}, years_in_congress = #{@years_in_congress}, dw1_score = #{@dw1_score}
        WHERE id = #{@id} "
        )
    end
  end
  def self.ten_worst_speaking_reps
    $db.execute("SELECT name, location, party, SUBSTR(name, 0, 4) FROM congress_members where name like 'Rep%' order by grade_level_since_1996 limit 10;")
  end
  def self.ten_worst_speaking_dem_reps
    $db.execute("SELECT name, location, party, SUBSTR(name, 0, 4) FROM congress_members where name like 'Rep%' and party = 'D' order by grade_level_since_1996 limit 10;")
  end
  def self.ten_worst_speaking_rep_reps
    $db.execute("SELECT name, location, party, SUBSTR(name, 0, 4) FROM congress_members where name like 'Rep%' and party = 'R' order by grade_level_since_1996 limit 10;")
  end

end


# p CongressMember.id

# parser = CongressParser.new('Importing_Politicians_Database.csv')
# parser.parse_congress_members
# parser.initialize_db
# parser.congress_array_to_db
# p parser.congress_members
# puts 'create member now'
politician = CongressMember.new(name: 'water', party: 'dsaf', location: 'ny', grade_level_since_1996: 12, grade_level: 1, years_in_congress: 3, dw1_score: 2)
politician.name = 'conor'
politician.save!
# # puts 'save now'
# politician.save!
puts 'run module below:'
# puts CongressMember.where('id = ?', 516)
# puts CongressMember.count
puts CongressMember.ten_worst_speaking_reps
puts CongressMember.ten_worst_speaking_dem_reps
puts CongressMember.ten_worst_speaking_rep_reps
	Count Queries

	1. There are 240 Republican representatives.
	2. There are 187 Democratic representatives.
	3. There are 51 Democratic senators, 47 Republican senators.

	The questions asks for "representatives", but the details below it ask to specify if it's a Rep or Sen, so we've
	compiled the lists using all Rep's and Sen's.

	select name, location, party, substr(name, 0, 4), years_in_congress from congress_members order by grade_level_since_1996 limit 10;
	Top 10 reps
	name location party substr(name, 0, 4) years_in_congress
	------------------ ----------------------------------------- ---------- ------------------ -----------------
	Rep. Mick Mulvaney SC (33.99855000018255, -81.0452500001872) R Rep 1
	Rep. Rob Woodall GA (32.83967999993223, -83.62758000031658 R Rep 1
	Sen. Rand Paul KY (37.645969999815804, -84.7749699999653 R Sen 1
	Rep. Sean Duffy WI (44.39319000021851, -89.8163600002137) R Rep 1
	Rep. Tim Griffin AR (34.748649999697875, -92.2744899997135 R Rep 1
	Rep. W. Todd Akin MO (38.63578999960896, -92.5663000000448) R Rep 11
	Rep. Vicky Hartzle MO (38.63578999960896, -92.5663000000448) R Rep 1
	Rep. Tom Graves GA (32.83967999993223, -83.62758000031658 R Rep 3
	Rep. David Schweik AZ (34.86596999961597, -111.7638099997315 R Rep 1
	Sen. Ron Johnson WI (44.39319000021851, -89.8163600002137) R Sen 1



	Top 10 Dem reps
	select name, location, party, substr(name, 0, 4), years_in_congress from congress_members where party = 'D' order by grade_level_since_1996 limit 10;
	name location party substr(name, 0, 4) years_in_congress
	------------------- -------------------------------------------- ---------- ------------------ -----------------
	Rep. John Garamendi CA (37.638300000444815, -120.99958999997835) D Rep 3
	Rep. John Lewis GA (32.83967999993223, -83.62758000031658) D Rep 25
	Rep. Colleen Hanabu HI (21.304770000335395, -157.85761000030112) D Rep 1
	Rep. Keith Ellison MN (46.3556499998478, -94.79419999982997) D Rep 5
	Rep. Tim Ryan OH (40.06020999969189, -82.40426000019869) D Rep 9
	Sen. Barbara Boxer CA (37.638300000444815, -120.99958999997835) D Sen 17
	Sen. Kent Conrad ND (47.475320000018144, -100.11841999998285) D Sen 23
	Sen. Chuck Schumer NY (42.82699999955048, -75.54396999981549) D Sen 11
	Sen. Claire McCaski MO (38.63578999960896, -92.5663000000448) D Sen 3
	Sen. Harry Reid NV (39.49323999972637, -117.07183999971608) D Sen 23

	Top 10 Rep reps
	select name, location, party, substr(name, 0, 4), years_in_congress from congress_members where party = 'R' order by grade_level_since_1996 limit 10;
	name location party substr(name, 0, 4) years_in_congress
	------------------ ----------------------------------------- ---------- ------------------ -----------------
	Rep. Mick Mulvaney SC (33.99855000018255, -81.0452500001872) R Rep 1
	Rep. Rob Woodall GA (32.83967999993223, -83.62758000031658 R Rep 1
	Sen. Rand Paul KY (37.645969999815804, -84.7749699999653 R Sen 1
	Rep. Sean Duffy WI (44.39319000021851, -89.8163600002137) R Rep 1
	Rep. Tim Griffin AR (34.748649999697875, -92.2744899997135 R Rep 1
	Rep. W. Todd Akin MO (38.63578999960896, -92.5663000000448) R Rep 11
	Rep. Vicky Hartzle MO (38.63578999960896, -92.5663000000448) R Rep 1
	Rep. Tom Graves GA (32.83967999993223, -83.62758000031658 R Rep 3
	Rep. David Schweik AZ (34.86596999961597, -111.7638099997315 R Rep 1
	Sen. Ron Johnson WI (44.39319000021851, -89.8163600002137) R Sen 1
	require 'csv'
	require 'sqlite3'

	class CongressParser
	$db = SQLite3::Database.new "congress_members.db"

	attr_accessor :file, :congress_members
	def initialize(file)
	@file = file
	@congress_members = []
	end

	def parse_congress_members
	CSV.foreach(@file) do \|row\|
	@congress_members << ({
	name: row[0],
	party: row[1],
	location: row[2],
	grade_level_since_1996: row[3],
	grade_level: row[4],
	years_in_congress:row[5],
	dw1_score: row[6]
	})
	end
	end

	def congress_array_to_db
	@congress_members.each do \|member\|
	$db.execute("INSERT INTO congress_members
	(name, party, location, grade_level_since_1996, grade_level, years_in_congress, dw1_score, created_at, updated_at)
	VALUES
	('#{member[:name]}','#{member[:party]}', '#{member[:location]}', #{member[:grade_level_since_1996]},
	#{member[:grade_level]}, #{member[:years_in_congress]}, #{member[:dw1_score]}, DATETIME('now'), DATETIME('now'))
	")
	end
	end

	def initialize_db
	# return if File.exists? "students.db"
	# if you don't want to overwrite your database, uncomment the above.
	$db.execute(<<-SQL
	CREATE TABLE congress_members (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name VARCHAR(64) NOT NULL,
	party VARCHAR(64) NOT NULL,
	location VARCHAR(64) NOT NULL,
	grade_level_since_1996 REAL NOT NULL,
	grade_level REAL NOT NULL,
	years_in_congress INTEGER NOT NULL DEFAULT '',
	dw1_score REAL NOT NULL,
	created_at DATETIME NOT NULL,
	updated_at DATETIME NOT NULL
	);
	SQL
	)
	end
	end

	module Sqloperations
	# def self.show_all
	# $db.execute "select * from #{self.table_name}"
	# end



	def id
	$db.execute "SELECT id FROM #{self.table_name} WHERE name='#{@name}' AND location='#{@location}' AND party='#{@party}'"
	end

	def self.included(base)
	base.extend ClassMethods
	end

	module ClassMethods
	def show_all
	# puts "CLASS METHODS"
	puts self
	$db.execute "select * from #{self::TABLE_NAME};"
	end
	def count
	$db.execute "SELECT COUNT(*) FROM #{self::TABLE_NAME}"
	end
	def show_all_by(attribute, value)
	$db.execute "select * from #{self::TABLE_NAME} where #{attribute}='#{value}'"
	end
	def delete(attribute, value)
	$db.execute "DELETE FROM #{self::TABLE_NAME} WHERE #{attribute}='#{value}'"
	end

	def where(attribute, value)
	$db.execute "SELECT * FROM #{self::TABLE_NAME} WHERE #{attribute.gsub('?', value.to_s)}"
	end

	# def update
	# $db.execute "UPDATE #{self::TABLE_NAME} WHERE id='#{id}'"
	# end
	end
	end

	class CongressMember
	include Sqloperations
	TABLE_NAME = 'congress_members'

	attr_accessor :name, :party, :location, :grade_level_since_1996, :grade_level, :years_in_congress, :dw1_score
	def initialize(args)
	@name = args[:name]
	@party = args[:party]
	@location = args[:location]
	@grade_level_since_1996 = args[:grade_level_since_1996]
	@grade_level = args[:grade_level]
	@years_in_congress = args[:years_in_congress]
	@dw1_score = args[:dw1_score]
	@id = nil
	# @created_at = Time.now
	# @updated_at = Time.now
	# @table_name = "congress_members"
	end

	def save!
	if !@id
	$db.execute(
	"INSERT INTO #{TABLE_NAME}
	VALUES (NULL,?,?,?,?,?,?,?, DATETIME('now'), DATETIME('now'))", @name, @party, @location, @grade_level_since_1996, @grade_level, @years_in_congress, @dw1_score
	)
	@id = $db.last_insert_row_id
	else
	$db.execute(
	"UPDATE #{TABLE_NAME} SET
	name = '#{@name}', party = '#{@party}', location = '#{@location}', grade_level_since_1996 = #{grade_level_since_1996},
	grade_level = #{@grade_level}, years_in_congress = #{@years_in_congress}, dw1_score = #{@dw1_score}
	WHERE id = #{@id} "
	)
	end
	end
	def self.ten_worst_speaking_reps
	$db.execute("SELECT name, location, party, SUBSTR(name, 0, 4) FROM congress_members where name like 'Rep%' order by grade_level_since_1996 limit 10;")
	end
	def self.ten_worst_speaking_dem_reps
	$db.execute("SELECT name, location, party, SUBSTR(name, 0, 4) FROM congress_members where name like 'Rep%' and party = 'D' order by grade_level_since_1996 limit 10;")
	end
	def self.ten_worst_speaking_rep_reps
	$db.execute("SELECT name, location, party, SUBSTR(name, 0, 4) FROM congress_members where name like 'Rep%' and party = 'R' order by grade_level_since_1996 limit 10;")
	end

	end



	# p CongressMember.id

	# parser = CongressParser.new('Importing_Politicians_Database.csv')
	# parser.parse_congress_members
	# parser.initialize_db
	# parser.congress_array_to_db
	# p parser.congress_members
	# puts 'create member now'
	politician = CongressMember.new(name: 'water', party: 'dsaf', location: 'ny', grade_level_since_1996: 12, grade_level: 1, years_in_congress: 3, dw1_score: 2)
	politician.name = 'conor'
	politician.save!
	# # puts 'save now'
	# politician.save!
	puts 'run module below:'
	# puts CongressMember.where('id = ?', 516)
	# puts CongressMember.count
	puts CongressMember.ten_worst_speaking_reps
	puts CongressMember.ten_worst_speaking_dem_reps
	puts CongressMember.ten_worst_speaking_rep_reps