Basic CRUD in FastAPI

FastAPI is a modern Python framework for building REST APIs. It is fast to write, fast to run, and it creates interactive API documentation (Swagger UI) for you automatically. If you have built a Web API in ASP.NET Core, FastAPI will feel familiar: a route maps to a function, the request body is converted to an object, and the return value is sent back as JSON.

In this lesson you will build a complete CRUD (Create, Read, Update, Delete) API in two steps:

Step 1: a Student API that stores data in a Python list, so you can learn FastAPI with no database
Step 2: an Employee API that saves the same kind of data in SQL Server using pyodbc and plain SQL
Testing every endpoint in the built-in Swagger UI at /docs
The limits of this basic approach, and what the advanced version fixes

CRUD and HTTP Methods

Every CRUD operation maps to one HTTP method. This is the same in FastAPI, ASP.NET Core and every other REST framework.

Operation	HTTP method	URL	FastAPI decorator
Read all	`GET`	`/students`	`@app.get`
Read one	`GET`	`/students/1`	`@app.get`
Create	`POST`	`/students`	`@app.post`
Update	`PUT`	`/students/1`	`@app.put`
Delete	`DELETE`	`/students/1`	`@app.delete`

Install FastAPI

FastAPI is the framework; Uvicorn is the web server that runs it (like Kestrel runs an ASP.NET Core app). Create a virtual environment and install both:

python -m venv venv
venv\Scripts\activate          # Windows
# source venv/bin/activate     # Linux / macOS

pip install fastapi uvicorn

Step 1: CRUD on an In-Memory List

Create a file named main.py. The data lives in a normal Python list of dictionaries, so it is reset every time the server restarts. That is fine for learning: you can focus on routes, parameters and request bodies first.

Create the app and the sample data

from fastapi import FastAPI

app = FastAPI()


# Static student data
students = [
    {
        "id": 1,
        "name": "Rahul",
        "age": 22,
        "course": "Python"
    },
    {
        "id": 2,
        "name": "Amit",
        "age": 24,
        "course": "AWS"
    },
    {
        "id": 3,
        "name": "Priya",
        "age": 21,
        "course": ".NET"
    }
]

app = FastAPI() creates the application object. Every endpoint is registered on it with a decorator such as @app.get(...). If decorators are new to you, read Decorators in Python first.

GET: read all students

# GET - Get all students
@app.get("/students")
def get_students():
    return students

FastAPI converts the returned list to JSON automatically. You don't call any serializer yourself.

GET: read one student by id

# GET - Get student by ID
@app.get("/students/{student_id}")
def get_student(student_id: int):

    for student in students:

        if student["id"] == student_id:
            return student

    return {"message": "Student not found"}

{student_id} in the URL is a path parameter. Because the function parameter has the type hint student_id: int, FastAPI converts "2" from the URL to the integer 2. If you call /students/abc, FastAPI rejects the request with a 422 validation error before your function even runs.

POST: add a new student

# POST - Add new student
@app.post("/students")
def add_student(student: dict):

    students.append(student)

    return {
        "message": "Student added successfully",
        "student": student
    }

A parameter that is not in the URL and has a type like dict is read from the request body. The JSON you send is turned into a Python dictionary and appended to the list.

PUT: update a student

# PUT - Update student
@app.put("/students/{student_id}")
def update_student(student_id: int, updated_student: dict):

    for student in students:

        if student["id"] == student_id:

            student["name"] = updated_student["name"]
            student["age"] = updated_student["age"]
            student["course"] = updated_student["course"]

            return {
                "message": "Student updated successfully",
                "student": student
            }

    return {"message": "Student not found"}

This endpoint uses both: the id comes from the URL and the new values come from the body.

DELETE: remove a student

# DELETE - Delete student
@app.delete("/students/{student_id}")
def delete_student(student_id: int):

    for student in students:

        if student["id"] == student_id:

            students.remove(student)

            return {
                "message": "Student deleted successfully"
            }

    return {"message": "Student not found"}

Run the API

From the folder that contains main.py:

uvicorn main:app --reload

main is the file name and app is the FastAPI object inside it. --reload restarts the server every time you save a file. You will see:

INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process using WatchFiles
INFO:     Application startup complete.

Test with Swagger UI

Open http://127.0.0.1:8000/docs in the browser. FastAPI has generated a Swagger page listing all five endpoints. Click an endpoint, press Try it out, fill in the values and press Execute.

For POST /students, send this body:

{
  "id": 4,
  "name": "Divyani",
  "age": 23,
  "course": "Data Science"
}

Response:

{
  "message": "Student added successfully",
  "student": {
    "id": 4,
    "name": "Divyani",
    "age": 23,
    "course": "Data Science"
  }
}

Now call GET /students again and the new student is in the list.

There is a second documentation page at http://127.0.0.1:8000/redoc, and the raw OpenAPI specification is at /openapi.json. You get all three without writing any configuration.

Step 2: The Same CRUD on SQL Server with pyodbc

A list is lost on restart, so the next step is a real database. The simplest way to talk to SQL Server from Python is pyodbc: you open a connection, run SQL and read the rows, very much like ADO.NET with SqlConnection and SqlCommand.

pip install pyodbc

pyodbc needs the Microsoft ODBC Driver for SQL Server installed on your machine. Check the installed driver name in ODBC Data Sources and use the same name in the connection string.

Create the database and table

CREATE DATABASE FastAPIDb;
GO

USE FastAPIDb;
GO

CREATE TABLE Employee
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Gender VARCHAR(20)
);
GO

--Test Data
INSERT INTO Employee (Name, Age, Gender)
VALUES
('Rahul', 25, 'Male'),
('Priya', 28, 'Female'),
('Amit', 30, 'Male');

Connection string

Create employee.py. Trusted_Connection=yes uses Windows Authentication. Replace the server name with your own SQL Server instance.

from fastapi import FastAPI
import pyodbc

app=FastAPI()

connection_string=(
    "Driver={ODBC Driver 17 for SQL Server};"
    r"Server=DESKTOP-PFD5S9B\SQLEXPRESS;"
    "Database=FastAPIDb;"
    "Trusted_Connection=yes"
)

The r before the server string makes it a raw string, so the backslash in DESKTOP-PFD5S9B\SQLEXPRESS is kept as it is.

GET all employees

@app.get("/get_all_employees")
def get_employees():

    connection=pyodbc.connect(connection_string)
    cursor=connection.cursor()

    cursor.execute("SELECT ID, Name, Age, Gender FROM Employee")

    employees=[]

    for row in cursor:
        employees.append({
            "id":row.ID,
            "name":row.Name,
            "Age":row.Age,
            "gender":row.Gender
        })

    connection.close()
    return employees

The cursor runs the query and lets you loop over the rows, like a SqlDataReader. Each row exposes its columns as attributes (row.Name), which we copy into a dictionary so FastAPI can return it as JSON.

GET employee by id

@app.get("/get_employee_by_id/{empid}")
def getemployeebyid(empid:int):
    connection=pyodbc.connect(connection_string)
    cursor=connection.cursor()

    cursor.execute("SELECT ID, Name, Age, Gender FROM Employee where ID = ?",empid)
    row=cursor.fetchone()

    connection.close()

    if row is None:
        return {"Message":"Employee Not Found"}

    return {
        "id":row.ID,
        "name":row.Name,
        "Age":row.Age,
        "Gender":row.Gender
    }

The ? is a parameter placeholder, and the value is passed separately. Always do this instead of joining the id into the SQL text: it protects the API from SQL injection, exactly like SqlParameter in ADO.NET. fetchone() returns a single row, or None when nothing matches.

POST: insert an employee

@app.post("/addEmployee")
def save_employee(employee:dict):
    connection=pyodbc.connect(connection_string)
    cursor=connection.cursor()
    cursor.execute(
        """
        INSERT INTO Employee (Name, Age, Gender)
        Values(?,?,?)
        """,
        employee["name"],
        employee["age"],
        employee["gender"]
    )
    connection.commit()
    connection.close()

    return{
        "Message":"Employee Added Successfully"
    }

pyodbc does not save changes automatically. connection.commit() is required after INSERT, UPDATE and DELETE; without it the change is rolled back when the connection closes.

PUT: update an employee

@app.put("/employees/{employee_id}")
def update_employee(employee_id: int, employee: dict):

    connection = pyodbc.connect(connection_string)

    cursor = connection.cursor()

    cursor.execute(
        """
        UPDATE Employee
        SET Name = ?, Age = ?, Gender = ?
        WHERE ID = ?
        """,
        employee["name"],
        employee["age"],
        employee["gender"],
        employee_id
    )

    connection.commit()

    connection.close()

    return {
        "message": "Employee updated successfully"
    }

DELETE: remove an employee

@app.delete("/employees/{employee_id}")
def delete_employee(employee_id: int):

    connection = pyodbc.connect(connection_string)

    cursor = connection.cursor()

    cursor.execute(
        "DELETE FROM Employee WHERE ID = ?",
        employee_id
    )

    connection.commit()

    connection.close()

    return {
        "message": "Employee deleted successfully"
    }

Run it with uvicorn employee:app --reload and test it at /docs. For POST /addEmployee send:

{
  "name": "Kusum",
  "age": 27,
  "gender": "Female"
}

FastAPI vs ASP.NET Core Web API

FastAPI	ASP.NET Core
`app = FastAPI()`	`WebApplication.CreateBuilder()` / `app.Build()`
`@app.get("/students")`	`[HttpGet("students")]`
`student_id: int` in the URL	`[FromRoute] int id`
Body parameter (`dict` or model)	`[FromBody] Student student`
Uvicorn	Kestrel
Swagger at `/docs`, built in	Swashbuckle / OpenAPI package
`pyodbc` + cursor	ADO.NET `SqlConnection` + `SqlCommand`

Limitations of This Basic Version

This code is perfect for learning how FastAPI works, but it is not how a production API is written:

No validation: the body is a plain dict. Send "age": "abc" or leave out name and the code fails with a KeyError and a 500 error.
Wrong status codes: "Student not found" is returned with 200 OK. A REST client expects 404 Not Found.
Raw SQL everywhere: every endpoint repeats the connect / execute / commit / close code.
Connections can leak: if a query throws an exception, connection.close() is never reached.
No error handling or rollback when a database write fails.
The table is created by hand with a SQL script instead of from the code.

All of these are solved in the next lesson with Pydantic schemas, SQLAlchemy ORM, dependency injection, HTTPException and Alembic migrations.

Next: Advanced CRUD in FastAPI with SQL Server, SQLAlchemy and Alembic