Honors 1000 - Productive Data Manipulation in Python and SQL

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

Final Project (Teams of 2 to 3) Due: 10 December 2023 - Sunday

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

U.S. Congress (2022-2024)

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

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

Create two sqlite3 tables according to the following schema:

drop table SENATOR;
drop table HREP;

create table SENATOR (
  id       int,
  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,
  lname    varchar(25),
  fname    varchar(25),
  birthday date,
  gender   char(1),
  state    char(2),
  party    varchar(20),
  district int,
  url      varchar(50),
  twitter  varchar(50),
  facebook varchar(50),
  youtube  varchar(50),
  primary key (id)
);

Run the INSERT statements available in senators.sql and hreps.sql 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: round((julianday('now') - julianday(birthday))/365.25,0)
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 queries in congress-queries.html for the backend.
You may also use the frontend code that I wrote to test your backend. Please make a folder called frontend in your project folder where congress.py is present and download the 3 frontend files into the folder. You may have to install CORS Python package
```
pip3 install cors
or
pip install cors
```