Honors 1000 - Productive Data Manipulation in Python and SQL

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

Final Project (Teams of 2 to 3) Due: 11 December 2022 - Sunday

(submit senator.py hrep.py queries.py congress.py to shared Google Drive - one submission per group; include a file, members.txt, containing names of group members)

U.S. Congress (2020-2022)

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

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,

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)
);

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.
Run the INSERT statements to load data into the two tables.
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 compute 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. Do not include states that have fewer than 10 representatives.
4. Get counts of male and female legislators for senate as well as house of representatives.
A sample run of these 4 queries is given in sample run. Place all 4 queries in file called queries.sql
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/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, Republican, or Independent.
The API return values and their corresponding JSON structures are given below:
/congress/legislator/GA
/congress/senator/GA
/congress/senator/party/Democrat
/congress/hrep/GA
/congress/hrep/party/Democrat
You may use congress.py as the initial template and add code to this program to complete all the endpoints code (I have given code for one of the endpoints!).
You may use the code in frontend code to test your entire Web application.