PYTHON

(1) Deliverables: The file lab3smith.py (replace Smith with your last name).

(2) Goal: Input customer name, current date, 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 date target_currency source_amount exchange_rate target_amount 

Note: Every publicly traded currency has a three letter currency code:

USD,1.000000;EUR,0.835789;GBP,0.739355;INR,66.83375;AUD,1.326406;CAD,1.284655;SGD,1.333338;CHF,1.000048;MYR,3.941504;JPY,109.1196;CNY,6.358716

(3) 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.

(4) Details:

- Modify the SQLKids2 example script to input the name, date, target currency, and source amount. 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.

- Modify the ReadFromWeb Example to obtain the exchange rates for the target currency. Merge this example with your modified SQLKids2 Example. Important: reorder the statements of your script so that a variable is always defined before it is used.

- You no longer need to input target_amount because it is computed using this formula:

target_amount = source_amount * exchange_rate 

- 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 name, date, target_currency, source_amount, exchange_rate, target_amount.

SQLKids2.py:

# SQLKids2 Example # Create an SQL table using data read from keyboard import sqlite3 # Attach to database created by SQLKids2 Example. conn = sqlite3.connect('kids2.db') cur = conn.cursor( ) # Create kids table. cur.execute( \ '''create table if not exists kids( name varchar(10), gender varchar(1), age integer);''') # Read data from keyboard. name = input("Enter name: ") gender = input("Enter gender: ") age = int(input("Enter age: ")) # Insert values into database. cur.execute(f'''insert into kids values( '{name}', '{gender}', {age});''') # Commit changes. conn.commit( ) # Query kids table. cur.execute("select * from kids;") # Print results from query print(cur.fetchall( )) # Close database. conn.close( )

readfromweb.py

from urllib.request import urlopen import sys target_currency = input("Enter target currency: ") url = "http://facweb.cdm.depaul.edu/sjost/it212/rates.txt" response = urlopen(url) line = str(response.read( )) # Remove first two characters and last character from line # This is called slicing line = line[2:-1] items = line.split(";") exchange_rate = 0.0 for item in items: fields = item.split(",") code = fields[0].strip( ) rate = float(fields[1].strip( )) if target_currency == code: exchange_rate = rate break; if exchange_rate == 0.0: print("Currency not on website.") sys.exit( ) source_amount = 1000.0 target_amount = source_amount * exchange_rate print("Target amount: " + str(target_amount))

HINTS

(1) The import statements on top of your script should be

from urllib.request import urlopen import sys import sqlite3 

(2) Use input statements to input these items:

name (str) date (str) target_currency (str) source_amount (float) 

(3) From the SQLKids2 Example, use this code fragment to obtain the target currency:

exchange_rate = 0.0 for item in items: fields = item.split(",") code = fields[0].strip( ) rate = float(fields[1].strip( )) if target_currency == code: exchange_rate = rate break; 

(4) Use this statement to create the transactions.db database:

conn = sqlite3.connect('lab2transactions.db')

(5) Modify the kids table to create a table named transactions with these columns:

Column	Datatype
customer_name	varchar(15)
date	varchar(12)
target_currency	varchar(3)
source_amount	float
exchange_rate	varchar(15)
target_amount	float

(6) 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.