What Are CRUD Operations?

Writing CRUD Operations in Raw SQL

CRUD operations describe actions. That’s why it’s a good idea to pull up your sleeves and write some code to explore how CRUD operations translate into raw SQL commands.

In the examples below, you’ll use Python’s built-in sqlite3 package. SQLite is a convenient SQL library to try things out, as you’ll work with a single SQLite database file.

You’ll name the database birds.db. As the name suggests, you’ll use the database to store the names of birds you like. To keep the example small, you’ll only keep track of the bird names and give them an ID as a unique identifier.

Start a new Python file named crud_sql.py with the code below:

 1import sqlite3
 2
 3def connect_to_db(db_path):
 4    return sqlite3.connect(db_path)
 5
 6if __name__ == "__main__":
 7    with connect_to_db("birds.db") as connection:
 8        connection.execute("""
 9          CREATE TABLE IF NOT EXISTS bird (
10            id INTEGER PRIMARY KEY AUTOINCREMENT,
11            name TEXT NOT NULL
12          );
13        """)

After you import sqlite3 in line 1, you define a function named connect_to_db() in line 3. As the name suggests, you’ll use this function to connect to the database, which you pass in as an argument. If sqlite3.connect() in line 4 can’t find a database at the given path, then the database will be created.

In line 6, you’re using the name-main idiom to connect to birds.db and create a bird table in the database when you execute crud_sql.py directly. In line 8, you’re executing the raw SQL command you define in lines 9 to 11. Since you’re using a context manager you don’t need to worry about closing the database.

The SQL command that you define does two things conditionally. If the bird table doesn’t exist in the database, then you create it with two columns:

1. id to uniquely identify database entries
2. name to store a bird’s name

You can think of the bird database table like a spreadsheet. The columns id and name are the table headers. Later, you’ll add rows to the table, which will be your database entries.

Open a terminal window in the same working directory as crud_sql.py and run the Python script:

$ python crud_sql.py

Executing crud_sql.py for the first time creates birds.db with a bird table. Next, start a new Python REPL to perform CRUD operations with raw SQL:

>>> from crud_sql import connect_to_db
>>> connection = connect_to_db("birds.db")
>>> cursor = connection.cursor()
>>> CREATE_BIRDS_SQL = """
... INSERT INTO
...   bird (name)
... VALUES
...   ('Humming Bird'),
...   ('Sugar Glider');
... """
>>> cursor.execute(CREATE_BIRDS_SQL)
<sqlite3.Cursor object at 0x105027bc0>

>>> connection.commit()

First, you establish the connection to the birds.db database and create a Cursor. Then, you prepare your SQL command to insert two flying animals into the bird table. Just like before, you use .execute() to perform the operation. This time, however, you open up a database transaction because you use the INSERT command.

At this stage, you could chain other queries to your transaction stack. To perform the operation, you must use .commit(). For now, you’ll use the connection open to perform the other CRUD operations. You’ll close the connection at the end of the section.

After you create the database records, it’s time to verify that they’re present in the database. For this, you use the retrieval CRUD operation:

>>> READ_BIRDS_SQL = "SELECT * from bird"
>>> cursor.execute(READ_BIRDS_SQL)
<sqlite3.Cursor object at 0x105027bc0>

>>> cursor.fetchall()
[(1, 'Humming Bird'), (2, 'Sugar Glider')]

With SELECT followed by an asterisk (*), you set up the READ_BIRDS_SQL query to retrieve all database entries of the bird table. If your database were bigger, then this would be a large transaction. For your small database, with only two small animals, this CRUD operation is fine.

The .fetchall() method returns a list of tuples. The list items are the database items. The tuple contents are the row values of id and name.

At this point, you may have spotted the incorrect spelling of the “Humming Bird” entry. The correct spelling of this tiny bird is “Hummingbird”. What a great opportunity to use another CRUD operation!

Go on and use the update CRUD operation on the “Humming Bird” entry by referencing its id:

>>> UPDATE_HUMMINGBIRD_SQL = """
... UPDATE
...   bird
... SET
...   name = "Hummingbird"
... WHERE
...   id = 1
... """
>>> cursor.execute(UPDATE_HUMMINGBIRD_SQL)
<sqlite3.Cursor object at 0x105027bc0>

>>> connection.commit()

When performing the UPDATE command, you must provide the new content you want to overwrite the old one with. Here, you change the little bird’s name to “Hummingbird”.

Another important detail for the update CRUD operation is that you need to provide information about which data you want to update. If you want to update one specific database entry, then you need to provide a unique identifier like the ID.

The same thing is true when you want to perform a delete CRUD operation. Again, you must let the database know the exact entry or entries you want to delete to avoid deleting wanted entries.

Trying out the delete operation in SQL gives you the chance to fix another flaw in one of your database entries. A sugar glider is a cute, flying animal. Yet, the sugar glider is not a bird.

You may diversify your database at some point in the future to be a habitat of any cute animal. But for now, it’s time to remove the sugar glider from the birds.db:

>>> DELETE_SUGAR_GLIDER_SQL = "DELETE FROM bird WHERE id = 2"
>>> cursor.execute(DELETE_SUGAR_GLIDER_SQL)
<sqlite3.Cursor object at 0x105027bc0>

>>> connection.commit()
>>> connection.close()

With DELETE, you don’t write new data. That’s why providing an id is sufficient to let the database know which entry you want to delete.

Again, make sure not to miss the WHERE clause. Otherwise, you’d delete all entries of the bird table. If you want to learn more about the SQL commands you can use with SQLite, then you can check out the overview on SQL As Understood By SQLite.

Now that you’ve finished all SQL CRUD operations, you also call .close() to close the database connection.

Translating CRUD concepts into SQL helps you to construct queries to manipulate data in your database. As you’ve seen in the examples above, the CRUD operations are closely related to SQL commands. However, writing pure SQL can be cumbersome. That’s where a package like SQLAlchemy comes into play to help you perform CRUD operations more conveniently.

Executing CRUD Operations With SQLAlchemy

SQLAlchemy is a popular Object-Relational Mapper (ORM) for Python. An ORM allows you to interact with a database using Python objects instead of writing raw SQL queries like you did in the previous section. This makes writing code more convenient for you as a Python developer.

Another convenience of using ORMs is that you don’t need to bother about the exact SQL implementation:

Unfortunately, despite SQL being standardized since 1986, a lot of different implementations exist. They deviate more or less from each other, making developing applications that would work with a range of different SQL servers particularly difficult. (Source)

With an ORM like SQLAlchemy, you can switch the SQL library in the background and keep your database queries unchanged.

PS> python -m venv venv
PS> .\venv\Scripts\activate
(venv) PS>

$ python -m venv venv
$ source venv/bin/activate
(venv) $

(venv) $ python -m pip install SQLAlchemy

In the previous section, you performed CRUD operations with pure SQL. Even when using an ORM like SQLAlchemy, the concept of using CRUD operations holds true.

Again, start by creating a Python file that helps you to connect to the birds.db database:

 1from sqlalchemy import create_engine, Column, Integer, String
 2from sqlalchemy.orm import DeclarativeBase, sessionmaker
 3
 4class Base(DeclarativeBase):
 5    pass
 6
 7class Bird(Base):
 8    __tablename__ = "bird"
 9    id = Column(Integer, primary_key=True)
10    name = Column(String)
11
12    def __repr__(self):
13        return f"Bird(id={self.id}, name={self.name!r})"
14
15engine = create_engine("sqlite:///birds.db")
16Session = sessionmaker(bind=engine)
17
18def init_db():
19    Base.metadata.create_all(engine)

One important advantage of using SQLAlchemy is that you can declare models. In line 7, you create a Bird class, which both describes a bird database table and the Python object model that will interact with table items later.

With init_db() in line 18, you either create the birds.db database if it doesn’t exist or connect to it if it’s present. That means you can continue working with the existing birds.db database from the former section to investigate the CRUD operations with SQLAlchemy.

Start another Python REPL session from within the same folder that you saved crud_sql_alchemy.py in. Then, perform your first CRUD operations with SQLAlchemy:

>>> from crud_sql_alchemy import Session, Bird, init_db
>>> init_db()
>>> session = Session()
>>> new_bird = Bird(name="Test Bird")
>>> session.add(new_bird)
>>> session.commit()

With SQLAlchemy, you can create new database entries using the .add() method of Session. In the example above, you’re creating a new entry with the name “Test Bird”.

To check if “Test Bird” is present in the database, you can query the database:

>>> from sqlalchemy import select
>>> query = select(Bird).where(Bird.name == "Test Bird")
>>> bird = session.execute(query).scalar_one()
>>> bird
<Bird(id=3, name='Test Bird')>

To retrieve database entries with SQLAlchemy, you use select() combined with the .where() method. The query resembles the raw SQL statement, but the Python code looks arguably way more familiar. Here, you want to select all Bird objects whose names are equal to "Test Bird".

In return, you get a Select object that you save in a query variable.

When you pass query into .execute(), you perform the query. Although there is only one “Test Bird” in your database that matches your query, you need to use .scalar_one() to save this exact database entry as bird.

Now that you’ve caught the “Test Bird” and saved the object in the bird variable, you can go on and update its name:

>>> bird.name = "Example Bird"
>>> session.commit()
>>> session.execute(select(Bird)).scalars().all()
[<Bird(id=1, name='Hummingbird')>, <Bird(id=3, name='Example Bird')>]

In SQLAlchemy, the update operation is as straightforward as changing an object’s property and then committing the change. To verify that everything worked, you could query the database again and check out all database entries.

After creating, reading, and updating your database with SQLAlchemy, only one operation of your CRUD routine remains missing: deleting an entry.

As long as you stay in the session, you can continue to work with bird:

>>> session.delete(bird)
>>> session.commit()
>>> session.close()

You can prepare to delete a database entry with SQLAlchemy by calling the session’s .delete() method. Again, you need to call .commit() to perform the delete CRUD operation.

Finally, it’s good practice to reset your session. You can think of session.close() as cleaning up after you.

Using SQLAlchemy can lower the complexities of writing raw SQL by adding a layer of abstraction to perform CRUD operations. When working with web applications, there can be an even further layer of abstraction when performing CRUD operations.

Next, you’ll explore the role of CRUD operations on the web and then move on to build your own CRUD-powered REST API that uses HTTP request methods and SQLAlchemy to interact with your database.

Performing CRUD Operations With a REST API

REST is an acronym for Representational State Transfer. With a REST API, you define a set of paths and rules for HTTP request methods to interact with your web service.

CRUD operations in REST APIs are HTTP methods in the first place. Similarly to interacting with websites in your browser, REST API calls may trigger database CRUD operations in the back end.

In the next section, you’ll create your own REST API with FastAPI to better understand how a REST API, HTTP request methods, and databases work together using CRUD operations.

Executing CRUD Operations With FastAPI

FastAPI is a web framework for building APIs with Python. Go ahead and install FastAPI into the same virtual environment you created before and installed SQLAlchemy in:

(venv) $ python -m pip install fastapi "uvicorn[standard]"

Besides FastAPI, you also install Uvicorn with the command above. Uvicorn will be the server on which you’ll run FastAPI.

Also, FastAPI installs Pydantic as its dependency. Pydantic will help you to model your data. If you want to learn more about Pydantic, then you can read the tutorial on simplifying data validation in Python.

You’ll use FastAPI to create endpoints that you send HTTP requests to. Once an endpoint receives a request, you perform a CRUD operation on your birds.db database. To streamline the process, you’ll leverage the crud_sql_alchemy.py file from before.

If you haven’t followed along with the tutorial so far, then you can download the Python scripts by clicking the link below:

Create a new file named crud_fastapi.py next to crud_sql_alchemy.py and start with the code below:

 1from fastapi import FastAPI, HTTPException, Depends
 2from sqlalchemy import select
 3from sqlalchemy.orm import Session
 4from pydantic import BaseModel, ConfigDict
 5
 6from crud_sql_alchemy import Bird, init_db
 7from crud_sql_alchemy import Session as SessionLocal
 8
 9app = FastAPI()
10init_db()
11
12class BirdCreate(BaseModel):
13    name: str
14
15class BirdUpdate(BaseModel):
16    name: str
17
18class BirdResponse(BaseModel):
19    model_config = ConfigDict(from_attributes=True)
20
21    id: int
22    name: str
23
24def get_db():
25    db = SessionLocal()
26    try:
27        yield db
28    finally:
29        db.close()

First, you import FastAPI to create your web application and HTTPException to handle potential errors. You use Pydantic’s BaseModel for data validation and settings management.

Before you can try out your API, you need to define the endpoints for your CRUD operations. Continue editing crud_fastapi.py and add the code below to define your first endpoint:

# ...

@app.post("/birds/", response_model=BirdResponse)
def create_bird(bird: BirdCreate, db: Session = Depends(get_db)):
    new_bird = Bird(name=bird.name)
    db.add(new_bird)
    db.commit()
    db.refresh(new_bird)
    return new_bird

You use the create_bird() function to create a new bird to the bird table. This function responds to POST requests at the /birds/ endpoint. Since you’re working with SQLAlchemy, the code looks very similar to the example in the previous section.

The next endpoint you’ll implement is the endpoint to read all birds that are currently stored in the database:

# ...

@app.get("/birds/", response_model=list[BirdResponse])
def read_birds(db: Session = Depends(get_db)):
    birds = db.execute(select(Bird)).scalars().all()
    return birds

The read_birds() function returns all birds stored in the bird table of your database. This endpoint responds to GET requests, allowing you to retrieve a list of all birds.

If you only want to receive a specific bird, you need to tell the API which bird you want to get in the response. To do so, add another endpoint that accepts the id of a bird as a parameter:

# ...

@app.get("/birds/{bird_id}", response_model=BirdResponse)
def read_bird(bird_id: int, db: Session = Depends(get_db)):
    query = select(Bird).where(Bird.id == bird_id)
    found_bird = db.execute(query).scalar_one()
    if found_bird is None:
        raise HTTPException(status_code=404, detail="Bird not found")
    return found_bird

When you pass in bird_id as part of the endpoint URL of your GET request, then the read_bird() function tries to return the bird with the given ID.

The SQLAlchemy code above looks similar to the steps you performed in the Python REPL before. One minor difference is that here you cover the situation when no bird with the given ID is found. In this case, you’re raising an HTTPException to inform the API user that the bird wasn’t found.

You’ll use a similar pattern for the update CRUD operation. You only go on to change the bird’s name if a bird with the requested ID exists:

# ...

@app.put("/birds/{bird_id}", response_model=BirdResponse)
def update_bird(bird_id: int, bird: BirdUpdate, db: Session = Depends(get_db)):
    query = select(Bird).where(Bird.id == bird_id)
    found_bird = db.execute(query).scalar_one()
    if found_bird is None:
        raise HTTPException(status_code=404, detail="Bird not found")
    found_bird.name = bird.name
    db.commit()
    db.refresh(found_bird)
    return found_bird

The update_bird() function replaces an existing bird’s details with the provided new name. Just like before, the delete CRUD operation looks very much like the one you use to update a database record. Instead of renaming a bird, you call the .delete() method this time:

# ...

@app.delete("/birds/{bird_id}", response_model=dict)
def delete_bird(bird_id: int, db: Session = Depends(get_db)):
    query = select(Bird).where(Bird.id == bird_id)
    found_bird = db.execute(query).scalar_one()
    if found_bird is None:
        raise HTTPException(status_code=404, detail="Bird not found")
    db.delete(found_bird)
    db.commit()
    return {"message": "Bird deleted successfully"}

Now that all your CRUD API endpoints are present, it’s time to try out your API! Start the Uvicorn server by running the uvicorn command. As an argument, you need to provide the filename without the .py extension followed by a colon (:) and the name of the FastAPI app:

(venv) $ uvicorn crud_fastapi:app
INFO:     Will watch for changes in these directories: [...]
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [29889] using WatchFiles
INFO:     Started server process [29891]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

One cool thing about FastAPI is that it comes with interactive API documentation out of the box. That means you can test out the REST API endpoints directly in the browser. Visit http://127.0.0.1:8000/docs and perform some CRUD operations on your REST API:

CRUD operations in REST APIs provide a standardized framework for building web services that can create, read, update, and delete resources over the web.

FastAPI is a great package for connecting CRUD operations with HTTP request methods. With your endpoints, you mapped the CRUD operations to functions that interact with your database. Again, SQLAlchemy is a great helper for performing CRUD operations on your database intuitively.