Day 22: pydantic. sqlalchemy. Fastapi.
Hi 👋 I am Hud, a postdoc for engineering data science at the AI Manufacturing Center in Laramie, Wyoming. My funding is running out (AaAaaA !), so while I am actively looking for a new job, instead of doing the 205th coding certificate to prove my worthiness — I thought I’d do design challenges and document how I spend my time upskilling so other engineers can do the same.
Nowadays, certificates are everywhere. Documenting small upskill projects that you can later show off is the best way to get recognition as a professional engineer.
This is day 22 of a 30-day design challenge. Follow along and let me know if you get stuck!
TL;DR tasks
Download the provided code for the challenge.
Task 1: Create SQLAlchemy models
- Define SQLAlchemy models for each table in the SQLite database. Each model should represent a table and its columns as Python classes and attributes.
Task 2: Set up the SQLAlchemy engine and session
- Set up the SQLAlchemy engine to connect to the SQLite database.
- Create a sessionmaker to handle database sessions for querying and transactions.
Task 3: Replace direct SQL queries
- Replace the direct SQL queries in the API routes with SQLAlchemy ORM queries using the models and session created in the previous steps.
Task 4: Split code into separate files
- Organize the code by splitting it into separate files. Create a models.py file to contain the SQLAlchemy models and a database.py file to handle database setup and sessions.
Task 5: Test the refactored code
- Run the refactored code and verify that the API still functions as expected after switching to SQLAlchemy ORM.
The problem
The FastAPI application handles creating, deleting, and retrieving events and tickets. Each function is responsible for a specific task, such as creating an event or booking a ticket. However, in the current code, raw SQL queries are used to interact with the database, which can be error-prone and difficult to maintain as the application grows.
Using raw SQL can pose a security injection risk because it allows for the possibility of SQL injection attacks. SQL injection attacks occur when an attacker is able to insert malicious SQL code into a query, which can then be executed by the database. This can lead to unauthorized access to data or even the deletion of data.
Updating the model: Pydantic + SQLalchemy ORM
The current code has is using Pydantic to define two data models, EventCreate and TicketCreate, which inherit from the BaseModel class.
BaseModel is a class provided by Pydantic that provides a set of default behaviors for data models, such as validation and serialization. By inheriting from BaseModel, the EventCreate and TicketCreate classes automatically gain these behaviors.
In this specific code, EventCreate defines the information needed to create an event, including the event title, location, start and end dates, and the number of available tickets. TicketCreate defines the information needed to create a ticket, including the event ID, customer name, and customer email.
When we want to create database schema, we can use SQLalchemy to complement the pydantic objects. Since in the current code everything is written within the supersized main file, it’s probably good to separate the models somehow:
We can use instead sqlalchemy ‘s ORM to define the database schema and interact with python objects. We will see later how we return these objects when we create or update an event. Additionally, we see how the keys of Event and Ticket relates to the schema:
SQLAlchemy ORM provides a high level of abstraction and can help reduce the amount of boilerplate code needed to interact with the database. This is how SQLalchemy interacts with the database.
To initialize the db, yield is used in the get_db() function to create a generator function that returns a database session and then closes it after the request is finished. This is useful because it ensures that the database session is properly closed after the request is finished, which helps to prevent resource leaks and other issues.
Depends is used as a dependency injection mechanism in the create_event() function to inject the database session into the function. This is useful because it allows the function to be more modular and testable, since the database session can be easily mocked or replaced with a different implementation.
Here we see how we can create and return aEvent object defined with pydantic, which ingests a dictionary structure Event(**event.dict()). Here’s an example of how that dictionary structure when we use the API in creating the event:
Handling errors
As you can see we simply add any exceptions handling within the FastAPI method. Using HTTPException is a good practice in FastAPI because it allows you to easily handle and return HTTP errors in a consistent and standardized way. It also allows you to provide detailed error messages to the client, which can be helpful for debugging and troubleshooting.
Running the application
We need two terminals: the first is to run main.py and the server running at http://localhost:8000/; the second is to use the API use_api.py by running the functions we defined above. Here’s how a snippet of the output for e.g. http://localhost:8000/events/1:
{"location":"Laramie","title":"Python Conference 2023","id":1,"available_tickets":49,"start_date":"2023-03-15T09:00:00","end_date":"2023-03-18T16:00:00"}Conclusion
Congratulations! You finished Day 22 from the 30-day design challenge.
If you have reached this far, you know how to:
- Transform raw SQL queries to ORMs
- Complement pydantic objects with sqlalchemy ORM
- Use FastAPI to create a web API for managing events and tickets
Check out the day 23 challenge!
Also, you can access the full 30-day GitHub repository here.
💡 My goal here is to help engineering data scientists upskill in design. I’d like to hear from you! Was this helpful? Anything I can improve? Connect with me on LinkedIn | Medium
