Honors 1000, Productive Data Manipulation in Python and SQL (Fall 2020)

Final Project (Teams of 2 to 4) Due: 13 December 2020 - Sunday

(sudo handin1000 project senator.py hrep.py queries.py congress.py)

U.S. Congress (2018-2020)

Consider the data about current U.S. Congress legislators obtained from US Congress Legislators:

• senators.csv
• hreps.csv

In this project you will build a MySQL Database, write some SQL queries, and implement Python Web Services for the server component of Web/Mobile application. More specifically,

1. Create two MySQL tables according to the following schema:

   create table SENATOR (
     id       int not null auto_increment,
     lname    varchar(25),
     fname    varchar(25),
     birthday date,
     gender   char(1),
     state    char(2),
     party    varchar(20),
     url      varchar(50),
     twitter  varchar(50),
     facebook varchar(50),
     youtube  varchar(50),
     primary key (id)
   );
   
   create table HREP (
     id       int not null auto_increment,
     lname    varchar(25),
     fname    varchar(25),
     birthday date,
     gender   char(1),
     state    char(2),
     district int,
     party    varchar(20),
     url      varchar(50),
     twitter  varchar(50),
     facebook varchar(50),
     youtube  varchar(50),
     primary key (id)
   );
   

2. Write Python programs (senator.py and hrep.py) that will read the data about senators and house representatives from the csv files and produce MySQL INSERT statements for the data.

3. Run the INSERT statements to load data into the two tables.

4. Write SQL queries for the following:
   1. Get legislator names, state, hrep or senator, and gender for legislators who were born in "October" sorted by legislator type, by state within legislator type and by last names within state.
   2. Get senator names and state codes of senators who are younger than 50 years old sorted by age (youngest to oldest). You may use the following expression to computer age from birthday: TIMESTAMPDIFF(YEAR, birthday, CURDATE())
   3. Get state codes and number of house representatives in each state sorted in descending order of number of house representatives.
   4. Get counts of male and female legislators for senate as well as house of representatives.
   5. Get senator names and state codes for senators who do not have a Twitter handle sorted by state and within state by last names.
   A sample run of these 5 queries is given in sample run. Place all 5 queries in file called queries.sql

5. Write Python Web Service API (congress.py) to respond to the following endpoints:

     /congress/legislator/<state_code>
   
     /congress/senator/<state_code>
     /congress/senator/party/<party>
   
     /congress/hrep/<state_code>
     /congress/hrep/<state_code>/<district>
     /congress/hrep/party/<party>
   

   In the endpoints,

   For <state_code> the API should respond to "AK", "AL", "GA", etc.

   For <party> the API should respond to "Democrat" or "Republican".

   For <district> the API should respond to district numbers such as 6, 8, 12 etc.