Lab 3

IT 212 -- Lab 3

Goal: Input customer name, source currency, target currency, and source amount from the keyboard. Compute the target amount from exchange rate information posted on the web. Then write a line to the transactions table in an SQLite3 file summarizing the transaction. The transaction should contain this information:
```
name  source_currency  target_currency  source_amount  
source_exchange_rate  target_exchange_rate  target_amount
```
Deliverable: The file lab3smith.py (replace Smith with your last name).
Relevant Projects: ReadFromWeb and SQLKids2 Examples. Run and understand these before you start combining and modifying them.
Note: Every publicly traded currency has a three letter currency code. This currency webpage shows the top ten traded currencies with their corresponding exchange rates on Saturday, Feb 23, 2020:
http://facweb.cdm.depaul.edu/sjost/it212/rates.txt
http://facweb.cdm.depaul.edu/sjost/it212/rates.json.txt
Definitions: The target currency is the currency to which the customer wants to convert. The source amount is the amount of source currency that the customer wants to convert. The target amount is the amount of target currency after it has been converted.
Details:
1. Modify Item 4 from the Reading from the Web Section to input a currency code and output the exchange rate for that currency.
2. Modify the SQLKids2 example script to input the name, date, source currency, target currency, and source amount. Use Detail 1 to obtain the source exchange rate and target exchange rates from the web. Then use this formula to obtain the target amount:
```
sr = source exchange rate;  tr = target exchange rate
sa = source amount;         ta = target amount

ta = tr * sa / sr
```
  Script name: lab3Smith.py (replace Smith with your last name). Suggested table name: transactions; suggested database file name: lab3transactions.db. Use currencies that are listed on the currency webpage above. Put the import statements from the SQLKids2 and ReadFromWeb examples at the top of your script.
3. Modify the SQLKids2 Example to (a) create a table named transactions if it does not already exist, (b) insert a row into the transactions table that contains these columns:
```
name  source_currency  target_currency  source_amount  
source_exchange_rate  target_exchange_rate  target_amount
```
4. The Python variables that you use, for example, sc tc sa sr tr ta, need not be the same as the columns of the SQLite3 table, for example:
```
name  source_currency  target_currency  source_amount  
source_exchange_rate  target_exchange_rate  target_amount
```

Suggestions from Working on Lab3

The import statements on top of your script should be

from urllib.request import urlopen
import json
import sqlite3
import sys

Use input statements to input these items:

name (str)  date (str)  source_currency (str) 

target_currency (str)  source_amount (float)

You must use the dictionary twice, once to obtain the source currency srand the second time to obtain the target currency tc. Use sys.exit to directly exit your script if an illegal currency code is entered at the keyboard:
```
if sc in d:
    sr = d[sc]
else:
    print("Code for source currency not found.")
    sys.exit( )
    
if tc in d
    tr = d[tc]
else:
    print("Code for target currency not found.")
    sys.exit( )
```
Use this statement to create the output database:
```
conn = sqlite3.connect('lab3transactions.db')
```
Modify the kids table to create a table named transactions with these columns:

Column Datatype

name varchar(15)

source_currency varchar(3)

target_currency varchar(3)

source_amount float

source_exchange_rate float

target_exchange_rate float

target_amount float
When you modify the insert statement
```
# Insert values into database.
cur.execute(f'''insert into kids values(
  '{name}', '{gender}', {age});''')
```
remember that in SQL, string values need single quotes, but numeric values (int and float) do not use single quotes.

Column	Datatype
name	varchar(15)
source_currency	varchar(3)
target_currency	varchar(3)
source_amount	float
source_exchange_rate	float
target_exchange_rate	float
target_amount	float

Pseudocode for Lab 3

To complete Lab 3, combine the Python code from two sources:
1. Problem 3, Feb 26 Notes, Reading from the Web Section
2. SQLKids2 Example

Here is suggested pseudocode:

Import needed packages.
Input name, sc, tc, sa from keyboard.
Obtain dictionary from web
Obtain sr from dictionary.
Obtain tr from dictionary.
Compute ta from formula.
Create connection and cursor
    (Database name is lab3transactions.db).
Create database table named transactions.
Insert row in table based on Python variables
    defined above.
Commit changes to table.
Display table.
Close connection.

Grading Criteria:

Show prompts to let the user know what information to enter.
Functionality: 75%, User Prompts: 10%, Source Code Comments: 10%, Submission: 5%.