Setting Up SQLite and Table Creation

00:00 To bring our CRUD operations to life, we first need a database to work with, and that’s what we’ll focus on in this lesson.

00:07 You’ll take the first steps toward setting up a new SQLite database. You’ll connect to it through a Python script and create your first table using raw SQL.

00:17 Along the way, you’ll see how Python and SQL communicate with each other behind the scenes. Before jumping into the hands-on portion, let’s quickly talk about what SQLite actually is, the self-contained database engine that comes built into Python.

00:33 It doesn’t require any setup or server configuration, and everything is stored in a single database file. It’s lightweight and yet powerful, which makes it an excellent starting point for learning SQL.

00:47 To follow along, I’d highly recommend using Visual Studio Code, but you can also use any other code editor of your choice. To get started, you’re going to create a new Python file called crud_sql.py, and the first thing you need to do is import the sqlite3 module.

01:04 This module allows Python to talk to your SQLite database. Next, you’ll define a function called connect_to_db(), which takes a database path as its argument and returns a connection object, which you’ll use to run your SQL commands.

01:20 In line six, you’re going to use the if __name__ == "__main__" idiom, which ensures that this block of code only runs when the script is executed directly.

01:29 Inside this block, you’ll open a connection to a database called birds.db, which you’ll use to store the names of birds.

01:41 And finally, you’ll run a SQL command that creates a table called bird. This table will include two columns. The first one is id, which will be used to uniquely identify database entries, and the second one is name, to store the bird’s name.

01:59 Make sure to save this file in your designated project folder.

02:04 Once you execute the code, it’ll automatically create the birds.db database file along with the bird table inside it.

02:13 Here’s the big picture of how everything connects together. At the top, you have your Python script, which communicates with the database through the sqlite3 module.

02:22 The sqlite3 layer takes your raw SQL statements and sends them to the database for execution. The simple flow is what makes it possible to write SQL inside a Python program without setting up any external servers or tools.

02:38 If you’d like to reinforce the concepts introduced in this lesson, the following resources will be particularly useful. The article called What Does if __name__ == "__main__" Do in Python explains why we use that pattern and provides valuable insight into script execution.

02:54 The next resource listed here called Python’s with Statement: Manage External Resources Safely explores what happens when we open and close database connections and how to do it safely.

03:06 In this lesson, you’ve learned how to initialize SQLite directly from Python, how to create a brand new database file, and how to write raw SQL using the sqlite3 module.

03:19 You also learned how to create your first database table and run your Python script. With this foundation in place, you’re ready to move into actual CRUD operations and start adding, reading, updating, and deleting data in your database.