Flask Lookup¶

In this assignment, you'll demonstrate command of the basics of Flask apps connecting to a MySQL database (the WMDB). This is our first real web-database application!

• Processing parameterized GET requests
• Connecting to a database and making queries from Python
• Combining dynamic data with static templates to produce rendered pages
• Handling different routes
• Dynamically creating hyperlinks
• Using forms to get information from the user
• Using template inheritance in Jinja2 to have similar pages (optional)

Solution¶

To see an example of what you might build, you can look at my reference solution. That link opens up a new tab. You have to be on campus or inside the VPN to view the solution.

Note that the URLs on my solution have a cs304lookup prefix; you should not do that.

Please don't feel that you have to present exactly the same interface that I do. I'm sure you can think of something more interesting, creative, or beautiful. Nevertheless, it exhibits the functionality I want.

Time¶

Historically, this takes almost everyone less than 13 hours to complete. If it's taken you that long and you're not done, stop and get some help.

Searching a Database via a Web Interface¶

Searching a database for useful information using the MySQL shell is difficult at best and requires knowing the SQL language. In this assignment, you'll create a web interface somewhat similar to the IMDB but pulling data from the WMDB.

Interface¶

The app will support these important URLs:

• To display the movie with TT = 456, the URL will be /tt/456
• To display the person with NM = 123, the URL will be /nm/123

• To look up movies/people with a fragment of the name/title:

/query/?query=text&kind=movie/person

where text is the text to search for, and movie/person indicates what kind of thing to search for.

In addition, the main page should have links to an example of a person and an example of a movie. Make it something or someone that you or your partner added!

The Lookup Routes¶

You have to implement four routes:

• something that displays a search form. I suggest using the / route for that.
• a /query/ route that processes the submitted search forms
• a /nm/<person_id_number> route that shows the detail page for that particular person.
• a /tt/<movie_id_number> route that shows the detail page for that particular movie.

You will not need to use POST in this assignment, since we are not updating the database, and POST is for updates. All routes will use GET.

GET is the default method in Flask, so the following are equivalent:

@app.route('/some-url/', methods=['GET'])
def some_function():
    pass

@app.route('/some-url2/')
def some_function2():
    pass

Order¶

I strongly suggest that you proceed like this:

1. Implement a /nm/<person_id_number> route that shows the detail page for that particular person. Skip the links to movies for now.
2. Test it by visiting localhost:port/nm/123, where "port" is your UID. Do you see George Clooney's page?¹
3. Implement a /tt/<movie_id_number> route that shows the detail page for that particular movie. Skip displaying the case for now. Test it with http://localhost:8080/tt/1454468 Do you see Gravity?
4. Improve that page by adding hyperlinks to the cast for Gravity. Those will be links to /nm/ routes, but those work now.
5. Improve the person detail page by adding links to their movies.
6. Test that

Okay, now you're ready to display the form.

1. Implement a dummy handler for processing a form. It should just print the two values from the form to the console and return a dummy result like You submitted {} and {}'.format(query,kind)
2. Create a template file that has the form. Make sure the ACTION of the form is the URL for the dummy handler you just created.
3. Implement a home route that renders the form.
4. Test that the form submits and has the information you need.

Finally, you're ready to process the form, which is last.

1. The form submission handler gets the two pieces of information from the form.
2. It makes the appropriate query, getting a list of results.
3. Print the result list to the console.
4. Test that code.
5. Add a conditional in the handler that looks at how long the list is.
   • if the list is length zero, inform the user
   • if the list is length one, redirect them to the appropriate detail page
   • if the list is longer than one, return a page with a list of hyperlinks to the various detail pages.

Movie Detail Page¶

The movie detail page must give the title of the movie in an H1 element with a class of title. It must have a paragraph with the title and release year. Then a list of the cast, with each name hyperlinked to the person's page.

Person Detail Page¶

The person page must give the person's name in an H1 element with a class of person. It must also have a paragraph saying the name of the student (staff member) who added that person, and when the WMDB person was born. Then, it must have a list of the actor's movies (if any), each hyperlinked to the movie page.

Note that the person page is a little more complicated than the movie page. You might start with the movie page.

Feel free to substitute the word person wherever I said actor, since the people in the person table are not always actors, though our focus in this assignment is on actors, since we are listing the cast of a movie.

Depending on how you do your coding, the detail page for a person can end up missing a lot of data, particularly if either they have no films (maybe they are a director) or their addedby field is null (it shouldn't be, but it happens sometimes). Your coding should handle those situations and at least give the person's name and birthdate (if any).

Errors¶

If an person is not found, the page must say:

Sorry, no person with that ID is in the database

Similarly, if a movie is not found, the page must say:

Sorry, no movie with that ID is in the database

Multiple Matches¶

You should get the earlier stuff working first. Once it's working, add a form to your base template (so that it appears on every page) that allows people to search by part of a person's name or part of a movie title. There are then three cases:

• No matches; report that situation
• Exactly one match; redirect to the page for that person or movie
• Multiple matches; render a page that lists all the matches, as hyperlinks to the specific page for that person/movie.

SQL Injection¶

Whenever we use SQL, we have to worry about SQL injection attacks, so you still have to do that with this assignment. Make sure any queries that involve untrustworthy data use prepared queries.

Strict API¶

Because I'll be doing semi-automated testing of your scripts, they need to have precise names and interfaces. Please use the URLs that I described above.

This strict API won't affect your coding much, if at all. Please, be creative in your appearance and the internals of your web app, not in the interface.

I'll be testing your code with URLs like these:

/
/nm/123
/query/?query=clooney&kind=person
/query/?query=george&kind=person
/tt/1454468
/tt/666
/query/?query=sally&kind=movie
/query/?query=harry&kind=movie

If you don't understand those URLs or what they should produce, please talk to me. You can also try them in my my reference solution.

Getting Started¶

There is a flask-starter folder in ~cs304/pub/downloads which you are welcome to copy (use the -r switch to cp to recursively copy the whole folder to a lookup folder in your cs304 folder. Don't have an extra layer of folders; your app.py file should be directly in your lookup folder, not deeper.

Inheritance¶

In my solution, I have the form on every page. I did that with template inheritance, which we will learn very soon. It is not required that you do template inheritance. It is sufficient that the form be on the first page.

You'll notice some similarities among the pages. Again, template inheritance makes this easier, but it is not required. A certain amount of copy/paste is expected.

Testing Your Code¶

You should, of course, test your code to make sure that the kinds of URLs listed above work. In addition, I've provided some pre-defined test cases. These follow the strict API.

How to use the test cases:

1. If you're using an SSH tunnel, as set up by VS Code, that's the default, so go to step 3.
2. If you're using your own port, click at the top that says "using my own port", so just go to step 3:
3. go to your line, click "lookup" and you'll see a set of test cases to click on. If they all work; you're good to go.

Don't worry about the "rnd" added to the end of the URL. that's to avoid certain caching issues.

Advice¶

Everyone has their own habits for programming assignments, but here is some advice that you may find helpful. I found it useful to remind myself of these as I was coding the solution!

• Test your queries using the MySQL client, where it'll be easier see what kinds of results you'll get.
• Build in small increments. Don't sit down and code a complete solution right away. Code the smallest thing that you can test and then test it.
• Test often. As soon as you build a small chunk of the solution, test it and see if it works. Try to leave things in working order before you turn to other homework assignments.
• Look at the source of your HTML pages (the output of your scripts). Bugs there can be hard to track down if you're just looking at your Python code.
• Document as you go, so you'll know what works and what assumptions you're making.
• Take breaks. If you run into programming troubles that seem intractable, don't beat your head against it for hours. Take a break and come at it again when you're fresh. Sometimes that's all it takes to figure the problem out. We've all had that experience, I think.

Improved User Experience¶

As an incentive to spend a little time thinking about a beautiful as well as functional interface, you can earn up to 10 additional points for your interface. That's a pure judgment call on my part, based on nominations from the graders.

How you'll be graded¶

My policy for this programming assignment (and most programming assignments) is:

• 70 percent for functionality: does it work? do all the features work? all test cases passed?
• 10 percent for modularity and coding style
• 10 percent for documentation
• 10 percent for efficiency, security (SQL injection) and other stuff

Specifically, I will look for:

• No SQL injection errors
• No global variables (constants are ok). This includes the database connection.
• Good modularity, function/variable names and parameter passing
• Good documentation
• reasonable programming style. lines shouldn't go beyond 80 characters

Please don't forget all the good coding style that you've learned in earlier classes. Look for appropriate abstractions and useful modules to reduce the redundancy and complexity of your code.

Don't forget to put your name(s) in a comment at the top of the file.

Submission¶

The procedure for submitting this assignment will be very similar to the Forms Assignment. The essential checklist is:

• zip up the lookup folder into lookup.zip
• drop the lookup.zip file to the course account
• copy the zip file to your laptop
• upload the zip file to Gradescope
• add your partner to the Gradescope assignment
• fill out the time and work form (see below).

Summary Checklist¶

1. App works for you
2. App passes the examples in the testing page I provide
3. Did all steps in the submission section

Thank you!

Time and Work

The following link has been updated for Fall 2025.

Finally, when you have completed the assignment, make sure you fill out the Time and Work Fall 2025 That report is required.