The idea of CRUD is strongly connected with databases. That’s why it’s no surprise that CRUD operations correspond almost one-to-one with SQL commands:When you create data, you’re using the INSERT command to add new records to a table. After creation, you may read data using SELECT. With a SELECT query, you’re asking the database to retrieve the specific pieces of information you need, whether it’s a single value, a set of records, or complex relationships between data points.The update operation corresponds to the UPDATE command in SQL, which allows you to modify data. It lets you edit or change an existing item.Lastly, the delete operation relates to the DELETE command. This is the digital equivalent of shredding a confidential document. With DELETE, you permanently remove an item from the database.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:
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:
id to uniquely identify database entries
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.
Note: Strictly speaking, you’re already performing your first CRUD operation by creating the database table. You could go on and define queries to read, update, and delete database tables. Instead, you’ll move on and perform CRUD operations on database items, not the database tables themselves.
Open a terminal window in the same working directory as crud_sql.py and run the Python script:
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:
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.
Note: Both database entries contain flaws. Can you spot them already? You’ll fix them in a moment.
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:
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:
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.
Note: Don’t forget the WHERE clause in the SQL command. Without specifying which id you want to update, you’d update all data records in the bird table.
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:
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.
Note: You’ll continue to work with birds.db in the upcoming sections. Then, you’ll verify that the CRUD operations you just performed were successful.
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.
SQLAlchemy is an external Python package that you can find on the Python Package Index (PyPI).
As with other external packages, it’s a good idea to install SQLAlchemy inside of a virtual environment. That way, you’re installing any project dependencies not system-wide but only in your project’s virtual environment.
Select your operating system below and use your platform-specific command to set up a virtual environment:
With the commands shown above, you create and activate a virtual environment named venv by using Python’s built-in venv module. The parenthesized (venv) in front of the prompt indicates that you’ve successfully activated the virtual environment.
After you’ve created and activated your virtual environment, you can go ahead and install SQLAlchemy:
Now that you’ve installed SQLAlchemy, you can go on and explore CRUD operations with the SQLAlchemy ORM.
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:
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:
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:
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.
Note: Most database engines keep track of the IDs that you’ve used before. Even if your new bird is the only bird in the database, the ID can be higher than 1.
Now that you’ve caught the “Test Bird” and saved the object in the bird variable, you can go on and update its name:
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:
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.