SophiaPretty
$14/per page/Negotiable
METCS521 Project Part 2
Purpose
This assignment is the second of a mini project where you are demonstrating your understanding of the modules of this class.
The mini project is about building a small ETL program in python. Each pat of the project focuses on different portions / stages of the ETL process.
Assignment Background
Summarizing your acquired knowledge from module 5, we are going to focus on the L part of the ETL process. For reference, ETL stands for Extract, Transform, Load. We are going to focus here on Load.
Assignment Statement
· Save data in a database
· Read data from a database
You are required to supply Last Name_First Name_Project_Part2.py and Last Name_First Name_create_dbs.py. Please upload as a single zip file.
Requirements:
Let's create a place to store each dataset separately.
1. Create a “create_dbs.py” file.
2. In this file created in step 1, write python code using sqlite3 to:
a. Create a “baseball.db” database
1. Create 1 table named “baseball_stats” with the following columns:
2. player_name
3. games_played
4. average
5. salary
The "baseball.db" SQLite3 database should look like this:
Baseball_stats
========================
player_name text
games_played int
average real
salary real
3. In this file created in step 1, write python code using sqlite3 to:
a. Create a “stocks.db” database:
b. Create 1 table named “stock_stats”
1. company_name
2. ticker
3. exchange_country
4. price
5. exchange_rate
6. share_outstanding
7. net_income
8. market_value_usd
9. pe_ratio
The " stocks.db" SQLite3 database should look like this:
stock_stats
========================
company_name text
ticker text
country text
price real
exchange_rate real
shares_outstanding real
net_income real
market_value real
pe_ratio real
4. Create AbstractDAO class. It should have the methods:
a. it should have 1 (instance) member: db_name
b. insert_records(records) – Should raise the NotImplementedError
c. select_all() - Should raise the NotImplementedError
d. connect():
1. connect to the database identified by db_name
2. returns the created connection
5. Create BaseballStatsDAO class
a. Class should inherit AbstractDAO
b. Class should implement the methods listed:
1. insert_records:
1. takes a list of records as parameter ( BaseballStatsDAO takes BaseballStatRecord)
2. call the method connect()
3. using the returned connection, create a cursor.
4. For each record in the list, write and execute an INSERT INTO statement to save the record's information to the correct table.
Example for baseball: cursor.execute(“INSERT INTO baseball_stats VALUES ( ?, ? , ? , ?)”, (name, number_games_played, avg, salary))
5. Commit the connection
6. Close the connection
2. select_all
1. call the method connect()
2. using the returned connection, create a cursor.
3. create an empty deque to hold the records in memory
4. write and execute a SELECT statement to get all the records of the table for the DAO
Example for baseball: cursor.execute(“SELECT player_name, games_played, average, salary FROM baseball_stats;”)
5. For each row fetched, iterate with a for loop over the result of your select command
1. Create a new record (BaseballStatRecord)
2. Add the record to the deque
6. Close the connection
7. Return the deque
6. Create StockStatsDAO class
a. Class should inherit AbstractDAO
b. Class should implement the methods listed:
1. insert_records:
1. takes a list of records as parameter (StockStatsDAO takes StockStatRecord)
2. invokes the method connect()
3. using the returned connection, create a cursor.
4. For each record in the list, write and execute an INSERT INTO statement to save the record's information to the correct table.
Example for stocks: see insert statement for baseball stats
5. Commit the connection
6. Close the connection
2. select_all
1. invokes the method connect()
2. using the returned connection, create a cursor.
3. create an empty deque to hold the records in memory
4. write and execute a SELECT statement to get all the records of the table for the DAO
Example for stocks: refer to select statement for Baseball
5. For each row fetched, iterate with a for loop over the result of your execute:
1. Create a new record (StockStatRecord)
2. Add the record to the deque
6. Close the connection
7. Return the deque
Use the code written for Project Part 1. If needed, a partial solution can be provided by your facilitator. This will only be done on a case by case basis and only in extreme circumstances. A 10-point deduction will be made if a solution is needed.
This section loads the records into the correct database using the classes from Project Part1
7. load MLB2008.csv using the BaseballCSVReader
8. load StockValuations.csv using the StocksCSVReader
9. Instantiate a new DAO instance for BaseballStats
10. Instantiate a new DAO instance for StocksStats
11. Insert the loaded records into baseball database using Baseball DAO's insert_records.
12. Insert the loaded records into stocks database using Stocks DAO's insert_records.
Awesome! Now we have data in the database. Let's use it!
Stocks stats:
13. Using the instance of StockStatsDAO select_all the records
a. Calculate and print the number of tickers by exchange_country using a dictionary.
Baseball stats:
14. Using the instance of BaseballStatsDAO select_all the records
a. Compute the average salary and enter into a dictionary by batting average
b. Print the dictionary formatting the salary to 2 decimal places.
NOTE: use round(record.avg, 3)) The rounding needs to happen at average time
Sample Output: The output is a sample and is not complete
0.083 400,000.00
0.130 600,000.00
0.132 1,500,000.00
0.147 6,333,333.00
0.158 14,726,910.00
0.159 3,850,000.00
IE 1
CA 10
ZA 1
JP 37
NO 2
SE 7
Attachments: