Description
Objectives
The assignment has two parts.
In Part 1, you need to write a python program to interact with an Azure SQL Server database.
In Part 2, you need to implement a Web service using SQL transactions and Flask.
After this assignment, you will not only get experience with database application development and transaction management, but also get familiar with some popular tools such as Azure SQL server, pyodbc, and Flask.
If you have never written any python program, please take a look at “Learn the Basics” .
Download A5.zip. Complete connect_db.py, loaddata.py, and query.py. Note that there are two deadlines for this assignment (see Submission).
Part 1. Database Programming (10 points)
Please first go through Azure SQL Server Setup and then do Task 1.
Task 1. Azure SQL Server Setup (2 points)
Step 1. Follow this video to create an empty SQL database (named VideoStore) on Azure.
Step 2. Since TA needs to check whether you succeed, please go to Firewall settings and add a rule that allows any IP to access your Azure services (see the screenshot below) .
Drawing
Step 3. Install pyodbc on your computer
Step 4. Install the ODBC driver (see Prerequisites (MacOS, Windows, or Ubuntu))
Step 5. Open connect_db.py in the A5 folder, and replace ODBC_STR with your connection string. Open a terminal and type python connect_db.py. You will see:
Drawing
Remarks
If you don’t know where to find the connection string, please watch here.
If you encounter Can’t open lib ‘ODBC Driver 13 for SQL Server’?, please refer to this post
If you encounter any other difficulty, please ask the questions on Piazza.
You need to submit connect_db.py for this task
Task 2. Data Loading (8 points)
Imagine you open your own video rental store (like Netflix). Your store allows customers to rent movies. You need to keep track of which customers are currently renting what movies. You design the following database.
RentalPlan(pid INT, pname VARCHAR(50), monthly_fee FLOAT, max_movies INT)
Customer(cid INT, pidFK-RentalPlan INT, username VARCHAR(50), password VARCHAR(50))
Rental(cidFK-Customer INT, midFK-Movie INT, date_and_time DATETIME, status VARCHAR(6))
Movie(mid INT, mname VARCHAR(50), year INT)
Note
Please use the ON DELETE CASCADE option for all foreign key declarations.
You do not need to specify a primary key for the Rental table.
RentalPlan. Each plan has a plan id, a name (“Basic”, “Rental Plus”, “Super Access”, or “Ultra Access”), the maximum number of rentals allowed (“Basic” allows one movie, “Rental Plus” allows three, “Super Access” allows five, “Ultra Plus” allows ten), and the monthly fee.
Rental. Each row represents the fact that a movie was rented by a customer with a customer id. The movie is identified by a movie id. The rental has a status that can be “open”, or “closed”, and the date and time the movie was checked out, to distinguish multiple rentals of the same movie by the same customer. When a customer first rents a movie, then you create an “open” entry in Rentals; when she returns it you update it to “closed” (you never delete it).
In this task, your job is to write a python program to create four tables in the VideoStore database that you created on Azure, and then load data into the tables.
Open the loaddata.py file.
a. Complete the LoadRentalPlan function （2 points)
def LoadRentalPlan(filename, conn):
“””
Input:
$filename: “RentalPlan.txt”
$conn: you can get it by calling connect_db()
Functionality:
1. Create a table named “RentalPlan” in the “VideoStore” database on Azure
2. Read data from “RentalPlan.txt” and insert them into “RentalPlan”
* Columns are separated by ‘|’
* You can use executemany() to insert multiple rows in bulk
“””
# WRITE YOUR CODE HERE
b. Complete the LoadCustomer function （2 points)
def LoadCustomer(filename, conn):
“””
Input:
$filename: “Customer.txt”
$conn: you can get it by calling connect_db()
Functionality:
1. Create a table named “Customer” in the “VideoStore” database on Azure
2. Read data from “Customer.txt” and insert them into “Customer”.
* Columns are separated by ‘|’
* You can use executemany() to insert multiple rows in bulk
“””
# WRITE YOUR CODE HERE
c. Complete the LoadMovie function （2 points)
def LoadMovie(filename, conn):
“””
Input:
$filename: “Movie.txt”
$conn: you can get it by calling connect_db()
Functionality:
1. Create a table named “Movie” in the “VideoStore” database on Azure
2. Read data from “Movie.txt” and insert them into “Movie”.
* Columns are separated by ‘|’
* You can use executemany() to insert multiple rows in bulk
“””
# WRITE YOUR CODE HERE
d. Complete the LoadRental function （2 points)
def LoadRental(filename, conn):
“””
Input:
$filename: “Rental.txt”
$conn: you can get it by calling connect_db()
Functionality:
1. Create a table named “Rental” in the VideoStore database on Azure
2. Read data from “Rental.txt” and insert them into “Rental”.
* Columns are separated by ‘|’
* You can use executemany() to insert multiple rows in bulk
“””
# WRITE YOUR CODE HERE
Note
You need to submit loaddata.py for this task
After running python loaddata.py, you will see the four new tables from Azure SQL Server:
Drawing
Part 2. Transaction Management (10 points)
Many websites provide a web API for developers to use their services. Examples include Twitter API, Yelp API, and Square API. In this part, your job is to implement a Web API for your video rental store. Be sure to use SQL transactions when appropriate.
Warmup: Flask Tutorials
You need to know some basic knowledge about Flask. Flask is a (micro) web framework written in Python. It’s quite easy to learn and widely used in practice.
Step 1. Read A Minimal Application. Make sure you can see ‘Hello, World!’ when opening http://127.0.0.1:5000/ in your browser
Step 2. Enter the A5 folder and open a terminal. Run FLASK_APP=query.py flask run. Open the following URL in your browser and make sure you can see the response {“cid”:1}.
http://127.0.0.1:5000/login?username=mike&password=mike123
Step 3. Read the code in query.py and make sure you understand why the response of the above URL is {“cid”:1}
Hints.
Once you understand how query.py works, there is no need to learn Flask’s other features for this assignment.
I recommend you enabling debug mode while testing your code. That is, run FLASK_APP=query.py FLASK_DEBUG=1 flask run instead.
Task 3: Who is the renter? (3 points).
Please provide a method to get the renter of a given movie. The renter is represented by cid; the movie is represented by mid. If the movie is not being rented by anyone, return cid = -1.
Please complete the getRenterID function in query.py.
@app.route(‘/getRenterID’)
def getRenterID():
“””
This HTTP method takes mid as input, and
returns cid which represents the customer who is renting the movie.
If this movie is not being rented by anyone, return cid = -1
“””
mid = int(request.args.get(‘mid’, -1))
# WRITE YOUR CODE HERE
response = {‘cid’: cid}
return response
Test
http://127.0.0.1:5000/getRenterID?mid=1 Response: {‘cid’: 2}
http://127.0.0.1:5000/getRenterID?mid=4 Response: {‘cid’: -1}
Task 4: How many more movies that a customer can rent? (3 points).
Please provide a method to get how many more movies that a given customer can rent. Unlike Task 3, you may need to write multiple SQL statements for this task. In Azure SQL Server, by default each statement executes in its own transaction. To group multiple statements into a transaction, you need to set conn.autocommit = False.
Please complete the getRemainingRentals function in query.py.
@app.route(‘/getRemainingRentals’)
def getRemainingRentals():
“””
This HTTP method takes cid as input, and returns n which represents
how many more movies that cid can rent.
n = 0 means the customer has reached its maximum number of rentals.
“””
cid = int(request.args.get(‘cid’, -1))
# Tell ODBC that you are starting a multi-statement transaction
conn.autocommit = False
# WRITE YOUR CODE HERE
conn.autocommit = True
response = {“remain”: n}
return jsonify(response)
Test
http://127.0.0.1:5000/getRemainingRentals?cid=1 Response: {‘remain’: 5}
http://127.0.0.1:5000/getRemainingRentals?cid=2 Response: {‘remain’: 2}
http://127.0.0.1:5000/getRemainingRentals?cid=3 Response: {‘remain’: 9}
http://127.0.0.1:5000/getRemainingRentals?cid=4 Response: {‘remain’: 1}
Task 5: Rent a movie (4 points).
Please provide a method that handle the request when a customer wants to rent a movie. You must use SQL transactions in order to guarantee ACID properties. In particular, you must ensure that the following two constraints are always satisfied, even if multiple instances of your application talk to the database.
C1. At any time a movie can be rented to at most one customer.
C2. At any time a customer can have at most as many movies rented as his/her plan allows.
When a customer requests to rent a movie, you may need to deny this request if it violates a constraint. You can implement denying in many ways, but we strongly recommend using the SQL ROLLBACK statement (i.e., conn.rollback()).
@app.route(‘/rent’)
def rent():
“””
This HTTP method takes cid and mid as input, and returns either “success” or “fail”.
It returns “fail” if C1, C2, or both are violated:
C1. at any time a movie can be rented to at most one customer.
C2. at any time a customer can have at most as many movies rented as his/her plan allows.
Otherwise, it returns “success” and also updates the database accordingly.
“””
cid = int(request.args.get(‘cid’, -1))
mid = int(request.args.get(‘mid’, -1))
conn = get_db()
# Tell ODBC that you are starting a multi-statement transaction
conn.autocommit = False
# WRITE YOUR CODE HERE
conn.autocommit = True
#response = {“rent”: “success”} OR response = {“rent”: “fail”}
return jsonify(response)
Test
On the initial database, you will get the following test results.
http://127.0.0.1:5000/rent?cid=4&mid=5 Response: {“rent”: “success”}
http://127.0.0.1:5000/rent?cid=4&mid=6 Response: {“rent”: “fail”}
http://127.0.0.1:5000/rent?cid=1&mid=3 Response: {“rent”: “success”}
http://127.0.0.1:5000/rent?cid=1&mid=2 Response: {“rent”: “fail”}
Submission
Download A5.zip.
[Deadline: 12/04 at 11:59 PM]. Put connect_db.py, loaddata.py, and the four given data files (Customer.txt, Movie.txt, Rental.txt, and RentalPlan.txt) into A5-1-submission.zip.
[Deadline: 12/16 at 11:59 PM]. Put query.py and connect_db.py into A5-2-submission.zip.
