Average time to complete: 2-3 hours
Use Beekeeper Studio or the mysql command line utility to import the simpledb database into your local MySQL server using the script in the sampledb/simpledb_mysql.sql file (see the class files). Verify that it imported correctly: there should be 4 tables.
If you have not already done so, install Python on your computer.
Copy the class examples/pythondb folder to your computer. Make sure you get a fresh copy so that you have the latest updates. Execute the following command to install the required libraries:
pip install flask mysql-connector-python
Using a programmer’s editor such as Notepad++ or Visual Studio Code, open the python-mysql-demo.py script located in the examples/pythondb folder. We looked at this in class; read through it to remind yourself what it does. Then, open a command prompt and execute the python-mysql-demo.py script:
python python-mysql-demo.py
This script will connect to the database, attempt to update records in a table in the simpledb database, and do some other tests. If you get an error message indicating the script could not connect to your database, you may need to adjust the root password in the call to connect() so that it is correct for your database.
Edit dbconfig.py to have the correct root password for your MySQL database server. Then, take a look at dbwebapp.py. Use the python command to run it; that will start a web server on port 5000. Use your web browser to connect to the server and execute the web application by entering the following URL into your browser:
http://localhost:5000
In this part of the lab, you will enhance dbwebapp.py to display information in a different database. First, import the contents of the class sampledb/univdb.sql into a new local database named univdb. Then, modify dbconfig.py to connect to the univdb database, and change dbwebapp.py to display all of the records in the course table. (5 points)
Next, modify the application to allow the user to specify a course number in the query string. When your application executes, if no course number is specified in the query string, display all of the records in the course table. If a courseno is specified in the query string, display only the record with the specified course number. Tip: Write two different queries: one for the case where you want to display all of the records, and one for the case where you retrieve just one. (5 points)
If you need a refresher on working with Python web applications in Flask, review the CpS 110 textbook chapter on web applications. One of the sections of the chapter shows you how to check to see if a query parameter is present in the query string.
Test your changes locally. Take two screen shots: one showing the browser window when no course number is specified in the query string (screenshot #1), and one showing the browser window when a course number is specified (screenshot #2).
Use Beekeeper Studio to connect to products.sqlite3 located in the pythondb folder.
View the contents of the products table.
Follow the instructions in the README-apidemo.md file to run and test the products_api.py
web API that uses the products.sqlite3 database.
Next, make a copy of products_api.py named courses_api.py. Change it so it
connects to your univdb MySQL database and allows the user to retrieve courses
instead of products. You need convert only the list_products() and get_product()
functions; delete all of the non-GET endpoint functions and any code that is not
needed for the two functions.
Take a screen shot showing the execution of two curl commands: one that retrieves all of the courses, and one that retrieves a specific course.
Create a report.pdf that contains screen shots for this lab and the usual cover page. Submit report.pdf, dbwebapp.py, and courses_api.py using Canvas.