Average time to complete: 3-5 hours
Use MySQL Workbench or the mysql command line utility to import the simpledb database into your local MySQL server using the script in the class sampledb/simpledb_mysql.sql file.
If you have not already done so, install Python on your computer. If using Windows, pick the installer option to “Add Python to PATH.”
Copy the class examples/pythondb folder to your computer. Make sure you get a fresh copy so that you have the latest updates. Install Python on your computer (download Python from python.org; if you already have Python installed, what you have will probably work) and then execute the following command to install the required libraries:
pip install flask mysql-connector-python
Note: If this command fails to run successfully, the issue may be that you did not pick the “Add Python to PATH” option when you ran the installer. You can re-run the installer to pick the option.
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 credentials in dbconfig.py so that they are 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.
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.
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).
In this part of the lab, you experiment with Anvil, a database app builder tool.
Begin by signing up for a free Anvil account at https://anvil.works/sign-up.
Work through the tutorial “Feedback Form” (https://anvil.works/learn/tutorials/feedback-form).
Create a new app named “Register” similar to the Feedback Form app. This app should be a registration form for a mother/daughter church outing. The registration form should have the following fields:
Create a Data Table to hold the registration. It should have the following fields:
When the user submits the form, write Python code to check to see that the name and email address are not blank. You can do these checks either in the server code or client code (it’s easier to do them in client code, but more secure to do them in server code). If the submitted information does not pass the checks, display an appropriate error message. 5 points.
If the submitted information passes the checks, compute the amount_due. The cost is $20 + $5 for each daughter. Then, insert a record into the Data Table with the mother name, email, daughter count, and computed amount due, and display a “Registration Success” message that shows the total amount due. 5 points.
Take a screen shot showing your app running in two different scenarios: 1) the error message that appears when user omits the mother name (Screen Shot #3); 2) the message displayed when the user registers with 2 daughters (Screen Shot #4) and the record inserted into the data table (Screen Shot #5).
Publish your app.
Enhance your Anvil registration form to prevent a user from registering with an email address that has been previously registered. To do this, you’ll need to read up on Using Data Tables from Python to figure out how to query the data table to see if someone has already registered with a given email address. If you are doing your validation checks in client code, you must set permissions to allow your client code to access the data table (see Data Security).
Take a screen shot showing the error that occurs when the user attempts to register a duplicate email address (Screen Shot #6).
If you do the bonus, you must figure it out on your own (no help allowed).
Create a report.pdf that contains screen shots for this lab and the usual cover page. Include in your time for the lab the time you spent working through the Anvil tutorials. Put the URL of your published app on the cover page of your report. Submit report.pdf and dbwebapp.py using Canvas.