Performing CRUD Operations With SQLite

00:00 In this lesson, you’re going to explore how to work with your newly created SQLite database. In the previous lesson, you created a new database file by executing crud_sql.py from your terminal window.

00:14 In this lesson, you’ll be using an interactive REPL session to run your queries in real time against a database table. To activate the live session, you can simply type python.

00:27 To get started, you’re going to import the connect_to_db() function, which you defined in the previous lesson. Next, you’ll create a connection to the database by passing in "birds.db".

00:42 In order to communicate with the database, you’ll create a cursor object, which you’ll use to send SQL commands to SQLite. This means that every query you write going forward will be passed through this cursor object.

00:56 To add two new rows to the bird table, you’re going to create a multi-line string, which contains an INSERT statement. So here you can type INSERT INTO bird (name), and then you list the values you want to insert. Inside the VALUES block, you’ll add two birds, 'Humming Bird' and 'Sugar Glider'.

01:22 Once you’re done, you can send the query to the database using the cursor.execute() function.

01:31 To finalize the insert, you can call connection.commit(), which will write the changes to the database file. Next, let’s take a look at how to retrieve the two records you just created.

01:44 To clear your window, you can press Ctrl+L on your keyboard. You can retrieve the records by first defining your SELECT query. You can call it READ_BIRDS_SQL and type SELECT * from bird.

01:59 This query asks SQLite to return every row from the bird table. Once again, you’ll send the query using the cursor.

02:10 To view the results, you can call cursor.fetchall(), which returns a list of tuples, where each tuple represents one row in the bird table. The first value that you see here is the id column, and the second value is the bird’s name.

02:27 To modify one of the existing records, you can write an UPDATE statement. Let’s update the spelling of Humming Bird.

02:36 So here you can type UPDATE bird SET name to Hummingbird, which is the correct spelling. Next, you’ll add a WHERE clause and set the id equal to 1.

02:52 Please note that this part is extremely important because without a WHERE clause, this query would update every single row in the database table.

03:02 Make sure to finalize the changes by calling connection.commit(). At this point, you may have noticed a mistake in one of our database entries.

03:12 A Sugar Glider is a cute animal, but it’s not a bird. Let’s take a look at how to delete this database entry. To remove the second entry from the bird table, you can create a DELETE statement

03:25 and set id equal to 2.

03:31 Once you execute the query, your table will contain only the correct Hummingbird entry.

03:40 When you’re done performing CRUD operations, make sure to close the database connection. Calling connection.close() releases the file handle and ensures everything is safely written to the database.

03:54 In this lesson, you’ve learned how to perform all four CRUD operations using raw SQL and how to save these changes using the commit() function.

04:03 You also learned the importance of properly closing a database connection to ensure clean and reliable execution. In the next lesson, you’ll explore how SQLAlchemy can simplify this process by using Python objects.