24.4. SQLite

For this course, we are not going to create or access a large-scale database. Instead, we will mimic the behavior of a database by creating a file in our project directory. This file will contain all the necessary tables, and we will use it to store and access information.

The queries we use with the database must be written in SQL. There are different versions of SQL used across the coding community, but we will focus on one called SQLite (pronounced sequel light). It provides a smaller, less complicated introduction to working with databases.

Python comes with a module specifically designed around SQLite. Let’s begin by importing it and connecting to a practice database!

24.4.1. Setup

1. Launch Visual Studio Code. From the File menu, open your local_practice directory.

2. Create a new folder called database_practice. Make sure you keep it outside of any existing project or Git repository.

3. From the File menu, open database_practice.

4. In the terminal, use git init to initialize the project as a new Git repository.

5. Create a main.py file and open it in the workspace.

6. On line 1, import the SQLite module:

   import sqlite3
   

   sqlite3 contains all the methods and tools we need to create and maintain a local database.

That’s it! We are now ready to dive into our first database!

24.4.2. Create a Database

To interact with a database, we must do three things inside our Python code:

1. Create the database.

2. Define a connection object that links our program to the database.

3. Define a cursor object that will execute our SQL commands.

In main.py, add the following statements:

import sqlite3

database = sqlite3.connect('practice.db')
cursor = database.cursor()

Line 3 creates the database object and assigns it the properties and methods we need to interact with our database.

The .connect() method links our program to a database called practice.db. If this file does not exist in our project folder, Python creates it for us. (Thanks, Python!)

Line 4 defines the cursor object. We will use this object to run SQL commands and perform CRUD operations.

Note

We can use any variable name we want for the connection object, but popular choices include conn, connection, database, and db.

Similarly, we can name the cursor object whatever we want, as long as our choice follows proper Python naming conventions.

Go ahead and run main.py. Notice that practice.db appears in the file tree!

We now have a database inside our project directory.

Opening practice.db in the workspace shows an empty file. This isn’t surprising, since we just created it. However, our database now exists and is ready to receive some information.

Tip

Take a moment to save and commit your changes!

24.4.3. Add a New Table

The next step is to add a table to our database. We will do this with the cursor object.

cursor uses a special method to run SQL commands. The general syntax is:

cursor.execute(sql_query)

sql_query must be a string or a string variable. What we put in that string depends on the action we want to perform. When we call the .execute() method, it converts the string into the SQL language and runs the command.

Let’s use .execute() to create a table called students in practice.db. The table will hold student names and their graduation year.

Try It!

1. In main.py define a variable to hold the SQL command. Be sure to type the string exactly as shown below.

   import sqlite3
   
   database = sqlite3.connect('practice.db')
   cursor = database.cursor()
   
   sql_query = "CREATE TABLE students (last_name TEXT, first_name TEXT, grad_year INT)"
   

   Reading the string in line 6 describes what we want the query to do. It should create a table called students, which has three columns. These will store a student’s last name, first name, and graduation year.

   Python and the SQL language sometimes use different names for the same data type. Notice that last_name and first_name are both set as TEXT instead of str, but they mean the same thing. grad_year is set as an integer.

   A deep dive into all the SQL data types is beyond the scope of this course. We focus on only a few, and we will pick them up as needed.

2. Once you assign the sql_query string, the next step is to make it happen! Add one more statement to main.py, then save.

   sql_query = "CREATE TABLE students (last_name TEXT, first_name TEXT, grad_year INT)"
   
   cursor.execute(sql_query)
   

3. Run main.py, aaannnnnddd… nothing seems to happen! However, as long as no error message appears, your code ran just fine. Checking the file tree shows that changes did take place in both main.py and practice.db.

   

   The M stands for “Modified”, and it indicates which files were changed.

Open practice.db in the workspace. You might see a message telling you that VS Code cannot display the contents of the file.

Click open it anyway to show mostly unreadable text.

VS Code can’t render a clean view of the database (yet).

While this doesn’t seem useful at first, it DOES prove that the .execute statement changed the practice.db file. On the next page, we will install an extension in VS Code that lets us view the contents of a database.

24.4.4. Table Creation Error

If you haven’t already done so, run main.py again. You will receive an error message.

Traceback (most recent call last):
File "main.py", line 8, in <module>
   cursor.execute(sql_query)
sqlite3.OperationalError: table students already exists

The last line tells us why the program crashed. The students table already exists in the database. Running the CREATE TABLE command a second time does NOT overwrite the original table. This is a nice safety feature. We want to protect the database against accidental deletions. However, we also want to rerun our code.

When we run our program, we want to create the students table if it isn’t in the database, but ignore the command if the table is already present. Fortunately, we can modify the sql_query string to deal with this.

Try It!

1. Update sql_query like this:

   sql_query = "CREATE TABLE IF NOT EXISTS students (last_name TEXT, first_name TEXT, grad_year INT)"
   

2. The IF NOT EXISTS syntax works like the not in keywords in Python.

   'a' not in 'Rutabagas'  # Returns False, because 'a' is in 'Rutabagas'
   42 not in [10, 8, 5]    # Returns True, because the list is missing 42
   

3. IF NOT EXISTS students checks to see if the a table called students is NOT in the database. If True, the table is added. If False the command is ignored.

4. Run main.py again. You should not see an error message.

On the next page, we will add some rows to the students table and then practice retrieving that data.

Be sure to save and commit your work before moving on!