# Exercises on database interaction
Create a new Python project. Within that project, create the following folders and files:
📂coasters (project folder)
├── 📄.env
├── 📄config.py
├── 📄database.py
├── 📄main.py
└── 📂queries
├── 📄coaster_queries.py
└── 📄themepark_queries.py
2
3
4
5
6
7
8
TIP
Don't remember how to correctly create a Python project, including a virtual environment? Have a look at the page about PyCharm to read how you should create a Python project for the Full Stack Essentials course.
Below, you can find the descriptions of the endpoints that you have to create in main.py using FastAPI. Seperate all business logic from database logic as seen in the previous sections of the course. Your SQL queries should be kept in two seperate files: all SQL queries on the rollercoaster table should end up in coaster_queries.py, queries on the themepark table belong in themepark_queries.py.
In this exercise, we will make use of the roller coasters database that you also used in the Data Essentials course in semester 1. In case you don't have that database in your MySQL anymore, download the coasters.sql script and run it in your MySQL workbench to create the database before you start with this exercise.
This is the data model of the database:
TIP
Before you start programming anything: don't forget to install FastAPI and the MySQL connector in the virtual environment of your project first!
These are the two commands that you need:
pip install "fastapi[all]"
pip install mysql-connector-python
2
Or execute them in one go using the following command:
pip install "fastapi[all]" mysql-connector-python
# All theme park names
Create an endpoint that will give us the names of all theme parks in alphabetical order. We should be able to address this endpoint in the following way:
TIP
Ordering the result alphabetically can be done in your SQL statement using an ORDER BY component. That way, you don't need to write additional logic in main.py.
When you address the endpoint in Postman, the result should look exactly like this:
# All theme parks and their details
Create a second endpoint that will give us all theme parks with their name, opening date, city and website. Sort the result based on the name of the theme parks in alphabetical order. We should be able to address this endpoint in the following way:
When you address the endpoint in Postman, the result should look exactly like this:
# All theme parks in a chosen opening year
Create a third endpoint that will give us theme parks with their name, opening date, city and website. Create a query parameter to pass a year to the endpoint. Our result should only show theme parks that were opened in this year. Sort the result based on the name of the theme parks in alphabetical order.
In case we want to get all theme parks opened in 1975, we should get the desired result using this link:
http://127.0.0.1:8000/themeparks/opening?year=1975 (opens new window)
When you address the endpoint in Postman, the result should look exactly like this:
Make sure that when no theme parks were opened in the year that was used, an empty array is the value of the themeparks key in your result. You should not get an error!
For example, when we use the year 2024, we should get this result in Postman:
# All roller coasters with a number of inversions
Create another endpoint that will give us roller coasters with their name, length, height, maximum speed and number of inversions. Create a query parameter to pass the number of inversions to the endpoint. Our result should only show roller coasters that have the requested number of inversions or more inversions. Sort the result based on the number of inversions. If multiple roller coasters with the same number of inversions exist, then sort on the name of the coasters in alphabetical order next.
In case we want to get all roller coasters with 3 or more inversions, we should get the desired result using this link:
http://127.0.0.1:8000/coasters/inversions?number=3 (opens new window)
When you address the endpoint in Postman, the result should look exactly like this:
Make sure that when no roller coasters have the requested number of inversions (or more), an empty array is the value of the rollercoasters key in your result. You should not get an error!
For example, when we use 7 as number of inversions, we should get this result in Postman:
We should also be able to not use a query parameter in our URL at all. In that case, all roller coasters that have at least one inversion will be returned.
So we should also be able to address our endpoint as such:
http://127.0.0.1:8000/coasters/inversions (opens new window)
And the result should look like this in Postman:
# Get a specific roller coaster using its ID
Now create an endpoint that will give us a specific roller coaster with its name, length, height, maximum speed and number of inversions. Use a query parameter with which the ID of the roller coaster can be given to your endpoint.
If we want information about the roller coaster with rollercoasterID 3, we should get the desired result using this link:
http://127.0.0.1:8000/coasters?coasterID=3 (opens new window)
When you address the endpoint in Postman, the result should look exactly like this:
Make sure that when no roller coaster exists with that rollercoasterID, an empty document is returned as result. You should not get an error!
For example, when we use 737 as coasterID, we should get this result in Postman:
# Get a specific theme park including its roller coasters using its ID
Create an endpoint that will give us a specific theme park with its name and website. Use a query parameter with which the ID of the roller coaster can be given to your endpoint.
Besides information about the theme park itself, also include a list of rollercoasters of this theme park. Of each roller coaster, we want to know the name, length, height, maximum speed and number of inversions (similar to the previous endpoint).
If we want information about the theme park with themeParkID 11, we should get the desired result using this link:
http://127.0.0.1:8000/themeparks/coasters?themeParkID=11 (opens new window)
We should get this result in Postman:
Try solving this exercise in two ways: once by using two seperate SQL queries (one to retrieve theme park information and one for rollercoasters) and once using one SQL query in which you use a 'join'.
Similar to the previous endpoint, make sure that when no theme park exists with that themeParkID, an empty document is returned as result. You should not get an error!