Understanding CRUD operations using SQL
Let’s assume that the editors or the users of our book review application want to make some modifications to the book inventory, such as adding a few books to the database, updating an entry in the database, and so on. SQL provides various ways to perform such CRUD operations. Before we dive into the world of Django models and migrations, let’s explore these basic SQL operations first.
You will be running a few SQL commands for the CRUD operations that follow.
To run them, follow these steps:
- Navigate to the Execute SQL tab in DB Browser. You can type in or paste the SQL commands we’ve listed in the sections that follow in the SQL 1 window. You can spend some time modifying your queries, and understanding them before you execute them.
- When you’re ready, click the icon that looks like a Play button or press the F5 key to execute the command. The results will show up in the window below the SQL 1 window:
Figure 2.5: Executing SQL commands in DB Browser
Now we will try out some SQL create operations to create a few records in the database.
SQL create operations
A create operation in SQL is performed using the insert command, which, as the name implies, lets us insert data into the database. Let’s go back to our bookr example. Since we have already created the database and the book table, we can now create or insert an entry in the database by executing the following command:
insert into book values ('The Sparrow Warrior', 'Super Hero
Publications', 'Patric Javagal');
This inserts the values defined in the command into the book table. Here, The Sparrow Warrior is the title, Super Hero Publications is the publisher, and Patric Javagal is the author of the book. Note that the order of insertion corresponds with the way we have created our table; that is, the values are inserted into the columns representing title, publisher, and author, respectively. Similarly, let’s execute two more inserts to populate the book table:
insert into book values ('Ninja Warrior', 'East Hill Publications', 'Edward Smith');
insert into book values ('The European History', 'Northside
Publications', 'Eric Robbins');
The three inserts executed so far will insert three rows into the book table. But how do we verify that? How do we know whether those three entries we inserted are entered into the database correctly? Let’s learn how to do that in the next section.
SQL read operations
We can read from the database using the select SQL operation. For example, the following SQL select command retrieves the selected entries created in the book table:
select title, publisher, author from book;
You should see the following output:
Figure 2.6: Output after using the select command
Here, select is the command that reads from the database, and the title, publisher, and author fields are the columns that we intend to select from the book table. Since these are all the columns the database has, the select statement has returned all the values present in the database. The select statement is also called an SQL query. An alternate way to get all the fields in the database is by using the * wildcard in the select query instead of specifying all the column names explicitly:
select * from book;
This will return the same output as shown in the preceding figure. Now, suppose we want to get the author’s name for the book titled The Sparrow Warrior; in this case, the select query would be as follows:
select author from book where title="The Sparrow Warrior";
Here, we have added a special SQL keyword called where so that the select query returns only the entries that match the condition. The result of the query, of course, will be Patric Javagal. Now, what if we wanted to change the name of the book’s publisher?
SQL update operations
In SQL, the way to update a record in the database is by using the update command:
update book set publisher = 'Northside Publications' where title='The Sparrow Warrior';
Here, we set the publisher value to Northside Publications if the value of the title is The Sparrow Warrior. We can then run the select query we ran in the SQL read operations section to see how the updated table looks after running the update command:
Figure 2.7: Updating the publisher value for The Sparrow Warrior
Next, what if we wanted to delete the title of the record we just updated? We will see just that in the next section.
SQL delete Operations
Here is an example of how to delete a record from the database using the delete command:
delete from book where title='The Sparrow Warrior';
The delete command is the SQL keyword for delete operations. Here, this operation will be performed only if the title is The Sparrow Warrior. Here is how the book table will look after the delete operation:
Figure 2.8 – Output after performing the delete operation
These are the basic operations of SQL. We will not go further into all the SQL commands and syntax, but feel free to explore more about database base operations using SQL.
Note
For further reading, you can start by exploring some advanced SQL select operations with join statements, which are used to query data across multiple tables. For a detailed course on SQL, you can refer to The SQL Workshop (https://www.packtpub.com/product/the-sql-workshop/9781838642358).
In this section, we learned how to interact with the database by performing CRUD operations on the database using SQL. In the next section, we will learn about how Django’s ORM, which is an abstract layer, interacts with a database.