How to CRUD records with FastAPI – Part 1

In one of our previous posts (SuperFast introduction to FastAPI) we highlighted how immediate was to setup a running application that show the {“hello”: “world”}…very complicated. In this series of articles we are going to enrich a bit the hello world application and try to expose some REST interface so that we can CRUD records with FastAPI. This article in particular will deal with the setup of the project by installing the libraries and have the code ready for the next part.

Other articles of the same series

POST: Create a POST API endpoint

GET: Create a GET API endpoint

PUT: Create a PUT API endpoint

DELETE: Create a DELETE API Endpoint

In this article:

• What is CRUD
• Install SQLAlchemy
• Define the SQL table
• Create the API endpoints
• Test our endpoints with curl
• Final Code
• GitlabRepo Link 🙂

What is CRUD

What is REST? What is CRUD? Is not the scope of this article, but as hint I can tell you is just an interface that can help us to:

C-reate records

R-ead records

U-pdate records

D-elete records

The CRUD actions are mapped against the http methods (in order) POST/GET/PUT/DELETE. In other words

POST will create a record (article here)

GET will retrieve one or more records (article here)

PUT will edit a record (article here)

DELETE will delete a record (article here)

Which records? Obviously the ones we have in our database, or the ones we are going to have in the database that was created here

Install SQLAlchemy

For achieving the CRUD operation we will need to store these in the MySQL database and for doing that we will need the connection driver. I’m not a big fan of ORMs because of all the implications that are derived, but for this article I will do an exception and will (start to) use SQLAlchemy, that in theory should speedup my goal. Let’s see how it works.

First of all, we will need to install the libraries, so just run pip install SQLAlchemy and pip install pymysql

All the documentation about these libraries can be found here https://www.sqlalchemy.org/ and here https://github.com/PyMySQL/PyMySQL

Let’s start with something simple that will consists in one database table that will represent a car record (car that is participating to a race)

Define the SQL table

Our table definition should be something similar to this:

CREATE TABLE `race_cars` (
  `id` int NOT NULL AUTO_INCREMENT,
  `car_number` int NOT NULL,
  `driver_name` varchar(100) NOT NULL,
  `team_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `car_number` (`car_number`)
)

The table definition in this case is super easy, we can enrich later, but for now we want to:

• Create a car entry
• Retrieve the list of all the cars and retrive one in particular
• Update a car entry
• Delete a car entry

Create the API endpoints

In our article about the SuperFast introduction to FastAPI we ended up having our main.py like this

from fastapi import FastAPI

app = FastAPI(
    title="Example-01",
    description="keep-4-yourself-example-01",
)

@app.get("/")
def say_hello():
    return {"hello": "world "}

We need to add the car endpoints for GET/POST/PUT/DELETE

With that framework is super easy because all we have to do is to add the decorators @app.get, @app.post, @app.put and @app.delete.

The end result will be like this

from fastapi import FastAPI

app = FastAPI(
    title="Example-02-mysql-alchemy",
    description="keep-4-yourself-example-02",
)

@app.get("/")
def say_hello():
    return {"hello": "world "}

@app.get("/race-cars")
def get_all_cars():
    return {"cars": ["all"]}

@app.get("/race-cars/{car_id}")
def get_car(
    car_id: int
):
    return {"car": [f"returning details for {car_id}"]}

@app.put("/race-cars/{car_id}")
def edit_car(
    car_id: int
):
    return {"car": [f"editing details for {car_id}"]}

@app.post("/race-cars/")
def create_car():
    return {"car": [f"creating car"]}

@app.delete("/race-cars/{car_id}")
def delete_car(
    car_id: int
):
    return {"car": [f"delete car {car_id}"]}

Test our endpoints with curl (or Postman)

# example 1
d3@d3:~$ curl "http://192.168.1.11:8080/race-cars" -X GET
{"cars":["all"]}
# example 2
d3@d3:~$ curl "http://192.168.1.11:8080/race-cars/7" -X GET
{"car":["returning details for 7"]}
# example 3
d3@d3:~$ curl "http://192.168.1.11:8080/race-cars/7" -X PUT
{"car":["editing details for 7"]}
# example 4
d3@d3:~$ curl "http://192.168.1.11:8080/race-cars/7" -X POST
{"detail":"Method Not Allowed"}
# example 5
d3@d3:~$ curl "http://192.168.1.11:8080/race-cars/" -X POST
{"detail":[{"loc":["query","car_id"],"msg":"field required","type":"value_error.missing"}]}
# example 6
d3@d3:~$ curl "http://192.168.1.11:8080/race-cars/7" -X DELETE
{"car":["delete car 7"]}

Important to note is the example #4. FastAPI is not expecting any POST request with an ID and is blocking the request by giving back an error that the Method is not allowed. We might return on this topic but for now let’s say that FastAPI will parse the request url against the defined one and give back an error if there is no match.

Implement the POST method

The POST method should be the one responsible for creating the record, this mean that according to our table definition (here) we will need

• a car_number (int)
• a driver_name (string at most 100 chars)
• a team_name (string at most 100 chars)

For achieving this in an automated way we have to introduce the pydantic models that in this case is a real game changer because is simplifying the mapping of the object and removing a lot of complexity around validation (at least for the basic one), more information can be found here https://pydantic-docs.helpmanual.io/usage/models/.

What we will need is the race_cars model defined as follow (please note the ideal implmentation would be to get an input model and an output model but we will get there in one of the next articles)

# at the top of the main.py file you should have this
from typing import Optional     #### new line
from pydantic import BaseModel  #### new line
from fastapi import FastAPI

class RaceCar(BaseModel):       #### new line
    id: Optional[int] = None    #### new line
    car_number: int             #### new line
    driver_name: str            #### new line
    team_name: str              #### new line

app = FastAPI(
    title="Example-02-mysql-alchemy",
    description="keep-4-yourself-example-02",
)

....

# change our post function to be
@app.post("/race-cars/")
def create_car(
    race_car: RaceCar
):
    return {"car": race_car}

The test will consist in one curl command

d3@d3:~$ curl -X POST "http://192.168.1.11:8080/race-cars/" -d '{"car_number": 4, "driver_name": "j", "team_name": "jj"}' -H "Content-Type: application/json"
{"id":null,"car_number":4,"driver_name":"j","team_name":"jj"}

As expected we are getting back the same information, nothing really exciting up to here, but what is missing now is the store of the record (that is represented by our race_car object that is an instance of the RaceCar model).

As we have seen the initial setup was super easy, the final code should look like this:

Final Code

from typing import Optional
from pydantic import BaseModel
from fastapi import FastAPI

class RaceCar(BaseModel):
    id: Optional[int] = None
    car_number: int
    driver_name: str
    team_name: str

app = FastAPI(
    title="Example-02-CRUD-part-1",
    description="keep-4-yourself-example-02",
)

@app.get("/")
def say_hello():
    return {"hello": "world "}

@app.get("/race-cars")
def get_all_cars():
    return {"cars": ["all"]}

@app.get("/race-cars/{car_id}")
def get_car(
    car_id: int
):
    return {"car": [f"returning details for {car_id}"]}

@app.put("/race-cars/{car_id}")
def edit_car(
    car_id: int
):
    return {"car": [f"editing details for {car_id}"]}

@app.post("/race-cars/")
def create_car(
    race_car: RaceCar
):
    return {"car": race_car}

@app.delete("/race-cars/{car_id}")
def delete_car(
    car_id: int
):
    return {"car": [f"delete car {car_id}"]}

Gitlab Repo Link

I thought that by adding the gitlab repo link to the article would add a nice touch (if not add in the comment why yes and why not, will help us understanding if is useful or not)

Gitlab https://github.com/keep4yourself/crud-records-with-fast-api-p1

If you are looking for a nice and well organized book that explains the core fundamentals around FastAPI we recommend this book: