Python sqlite3 Module Tutorial (With Practical Example)

SQLite is a simple, lightweight database system that stores data in a single file. Unlike other database systems that require setting up a server, SQLite is embedded within the application that uses it. This makes it easy to use and ideal for smaller applications, prototypes, and when you need a simple database solution without much overhead.

Python includes the sqlite3 module in its standard library, which means you don’t have to install anything extra to use SQLite with Python.

What We’ll Build

Now, imagine you have a cool app on your phone that helps you keep track of all your tasks. Well, we’re going to make something like that using Python. This program will be like your personal task organizer. You can add tasks, mark them as done, change them, or even delete them.

How It Works

Our to-do list manager will use a SQLite database to store all your tasks. Each task will have its unique ID number, a name, a description, and a status (like whether it’s done or not). With Python code, we’ll be able to do four main things with this database:

Create: This means adding new tasks to your list.
Read: This means getting tasks from the database, either all of them at once or just the ones you’re interested in.
Update: This is when you want to change something about a task, like its name or whether it’s done.
Delete: And of course, delete tasks from the list when you’re done with them.

Making a to-do list manager might seem simple, but it’s actually a really great way to see how powerful databases can be in real life.

We’ll guide you through each step of making this to-do list manager, explaining everything in simple terms. That way, not only will you have a cool tool to organize your tasks, but you’ll also understand how sqlite3 module works in Python.

Creating a Database and Table

To create a to-do list manager using Python and SQLite, first you need to import the sqlite3 module in your Python script, and you’re all set to start working with SQLite databases.

import sqlite3

Now, let’s create a new SQLite database to store our tasks. We’ll call it todo_list.db. This database file will contain all the information about our tasks, like their titles, descriptions, and statuses.

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')  # Replace 'todo_list.db' with your preferred database name

Next, we need to create a table inside our database to organize our tasks. We’ll call this table tasks. Each row in this table will represent a single task, and it will have columns for the task’s ID, title, description, and status.

# Getting ready to chat with our database
cursor = conn.cursor()

# Creating a cool 'tasks' table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT,
        status TEXT DEFAULT 'Pending'
    )
''')

# Saving our table setup
conn.commit()

Explanation:

We first prepare our database connection using conn = sqlite3.connect('todo_list.db'). If the database file doesn’t exist, it will create one for us.

Then, we create a cursor object cursor to interact with the database. The cursor allows us to execute SQL commands.

With the CREATE TABLE command, we create a table named tasks. It has four columns:

id : A unique identifier for each task (primary key).
title : The title of the task (mandatory).
description : A description of the task (optional).
status : The status of the task, defaulting to ‘Pending’ (can be updated later).

Finally, we use conn.commit() to save our changes to the database. This ensures that our table creation operation is finalized and committed to the database file.

Output:

And there you have it! We’ve successfully created a new database called todo_list.db, and created a table named tasks to store all our tasks

The output is visualized using a tool like DB Browser for SQLite, which we’ll explore in detail later on.

Adding Data to Your Table

Now that we’ve set up our database and created a table to store our tasks, let’s learn how to add data to it. There are a few different ways we can do this.

Method 1: Inserting One Row at a Time

The simplest way to add data is by inserting one row at a time. We use the INSERT INTO command followed by the values we want to insert. Each time we run this command, one new row is added to our table.

Example:

# Getting ready to chat with our database
cursor = conn.cursor()

# Adding a task using INSERT INTO
cursor.execute('''
    INSERT INTO tasks (title, description, status) 
    VALUES ('Finish Homework', 'Complete math assignment', 'Pending')
''')

# Saving our task addition
conn.commit()

Explanation:

We start by creating a cursor object named cursor.

The INSERT INTO command is used to add new data (in this case, a task) to our tasks table.

We specify the columns into which we want to insert data (title, description, and status) and provide the corresponding values for each column.

In this example, we’re adding a task titled “Finish Homework” with a description “Complete math assignment” and a status of “Pending”.

After executing the INSERT INTO command, we need to save our changes to the database using the conn.commit() method.

Output:

Method 2: Inserting Multiple Rows in One Go

If we have several tasks to add, we can insert them all at once using the executemany() method. This method takes a list of tuples, where each tuple represents a row of data.

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Adding multiple tasks at once using executemany
tasks_to_add = [
    ('Go for a run', 'Morning jog in the park', 'Pending'),
    ('Buy groceries', 'Get milk, bread, and eggs', 'Pending'),
    ('Call grandma', 'Wish her a happy birthday', 'Pending')
]

cursor.executemany('''
    INSERT INTO tasks (title, description, status) 
    VALUES (?, ?, ?)
''', tasks_to_add)

conn.commit()

Output:

Method 3: Using Variables to Insert Data

We can also use variables to insert data into our table. This is handy when we have the data stored in variables and want to insert them dynamically.

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Using variables to insert data
new_task_title = 'Study Python'
new_task_description = 'Practice loops and functions'
new_task_status = 'Pending'

cursor.execute('''
    INSERT INTO tasks (title, description, status) 
    VALUES (?, ?, ?)
''', (new_task_title, new_task_description, new_task_status))

conn.commit()

Output:

Method 4: Inserting Data Using a Dictionary

Another handy way to insert data is by using a dictionary. This method allows you to specify the column names and values more explicitly.

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Using a dictionary to insert data
new_task = {
    'title': 'Finish Project',
    'description': 'Complete project presentation slides',
    'status': 'Pending'
}

cursor.execute('''
    INSERT INTO tasks (title, description, status) 
    VALUES (:title, :description, :status)
''', new_task)

conn.commit()

In this method, we make a dictionary called new_task that holds all the information about the task. After that, we use placeholders like (:title, :description, :status) in our SQL command. Then, we use the execute() method and give it the dictionary new_task. SQLite looks at the keys in the dictionary and puts the matching values into the table.

Output:

Understanding Primary Keys

A primary key is a column in a table that holds unique values for each row. It’s like the task’s special badge, ensuring that no two tasks share the same ID. In our 'tasks' table , we’ve set up the id column as our primary key.

# Adding a task using INSERT INTO
cursor.execute('''
    INSERT INTO tasks (title, description, status) 
    VALUES ('Finish Homework', 'Complete math assignment', 'Pending')
''')

You might have noticed that we didn’t specify an ID when adding the task. That’s because SQLite takes care of it for us! When we use the INSERT INTO command, SQLite creates a unique ID for the new task, making sure every task has its special identification number.

Retrieving Data Using SELECT Command

Now that we’ve stored some tasks in our SQLite table, let’s learn how to fetch that data back out. We’ll use a simple SQL command called SELECT to get our tasks from the database. Here’s how you can do it:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

# Getting ready to chat with our database
cursor = conn.cursor()

# Fetching all tasks using SELECT
cursor.execute('SELECT * FROM tasks')
tasks = cursor.fetchall()

# Displaying our fetched tasks
for task in tasks:
    print(task)

Explanation:

First, we create a cursor object cursor to interact with the database.

Next, by using the SELECT * FROM tasks command, we retrieve all rows from the tasks table and store them in the tasks variable using cursor.fetchall().

Finally, we iterate over the tasks list and print each task to display them on the screen.

Output:

(1, 'Finish Homework', 'Complete math assignment', 'Pending')
(2, 'Go for a run', 'Morning jog in the park', 'Pending')
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Fetching All Rows and Specific Columns

Sometimes, you only need certain columns from your table. You can specify which ones you want in the SELECT command. For example, if you only want the title and status columns from the tasks table, you can do this:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching all rows
cursor.execute('SELECT * FROM tasks')
all_tasks = cursor.fetchall()

# Fetching specific columns
cursor.execute('SELECT title, status FROM tasks')
specific_columns = cursor.fetchall()

# Displaying the fetched data
print("All tasks:")
for task in all_tasks:
    print(task)

print("\nSpecific columns (Title and Status):")
for task in specific_columns:
    print(task)

In this example:

SELECT * FROM tasks : Fetches all columns for all tasks.

SELECT title, status FROM tasks : Selects only the title and status columns for all tasks.

Output:

All tasks:
(1, 'Finish Homework', 'Complete math assignment', 'Pending')
(2, 'Go for a run', 'Morning jog in the park', 'Pending')
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Specific columns (Title and Status):
('Finish Homework', 'Pending')
('Go for a run', 'Pending')
('Buy groceries', 'Pending')
('Call grandma', 'Pending')
('Study Python', 'Pending')
('Finish Project', 'Pending')

Fetching Data Conditionally Using WHERE

When you want to fetch specific data from your SQLite database based on certain conditions, you can use the WHERE clause in your SELECT statement. This helps you filter rows based on particular criteria.

For instance, if you want to fetch tasks with the title – 'Study Python', you can do this:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks that are 'Pending'
cursor.execute("SELECT * FROM tasks WHERE title='Study Python'")
pending_tasks = cursor.fetchall()

# Displaying pending tasks
print("\nPending tasks:")
for task in pending_tasks:
    print(task)

Output:

Pending tasks:
(5, 'Study Python', 'Practice loops and functions', 'Pending')

Updating Data with UPDATE Command

Now, suppose you need to mark a task as completed or fix a typo in the title. In SQLite, you can change existing data in your database using the UPDATE command. This allows you to update specific columns in one or more rows based on certain conditions. Let’s see how it works with an example:

Example:

import sqlite3  # Import the sqlite3 module for database operations

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')  # Connect to the 'todo_list.db' database file

cursor = conn.cursor()  # Create a cursor object to interact with the database

# Updating a task's status from 'Pending' to 'Completed'
cursor.execute("UPDATE tasks SET status='Completed' WHERE id=1")  # Update the status of a task with ID 1 to 'Completed'

# Fetching and displaying the updated task
cursor.execute("SELECT * FROM tasks WHERE id=1")  # Select the task with ID 1 from the 'tasks' table
updated_task = cursor.fetchone()  # Fetch the selected task
print("\nUpdated task:")
print(updated_task)  # Print the details of the updated task

conn.commit()  # Save the changes to the database

Output:

Updated task:
(1, 'Finish Homework', 'Complete math assignment', 'Completed')

While UPDATE is powerful, we need to use it wisely. Always double-check the WHERE condition to ensure we’re modifying the correct task.

Removing Records with DELETE

If you want to delete specific records from a table, you can use the DELETE FROM command followed by the WHERE clause to specify which rows to remove. Suppose you don’t want to go to the park for the morning jog, you can remove that task by using its id. Here’s how you can do it:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Deleting a task with a specific ID
cursor.execute("DELETE FROM tasks WHERE id=2")

# Fetching and displaying the remaining tasks
cursor.execute("SELECT * FROM tasks")
remaining_tasks = cursor.fetchall()
print("\nRemaining tasks after deletion:")
for task in remaining_tasks:
    print(task)

conn.commit()

Output:

Remaining tasks after deletion:
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

In cases when you need to clear out an entire table, you can use the DELETE FROM command without any conditions. This will wipe out all the rows in the table.

Where Clause and Filtering Data

When you work with databases, you often filter data to get specific information. The WHERE clause allows you to retrieve records that match certain conditions. This feature is handy when you want to get only the relevant data.

Imagine we want to see only the tasks that are still pending. Here’s how we can use the WHERE clause:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

# Preparing to interact with the database
cursor = conn.cursor()

# Fetching tasks that are 'Pending'
cursor.execute("SELECT * FROM tasks WHERE status='Pending'")  # Execute a SQL SELECT command to get all rows where status is 'Pending'
pending_tasks = cursor.fetchall()  # Fetch all rows that match the condition and store them in the 'pending_tasks' variable

# Displaying pending tasks
print("Pending tasks:")
for task in pending_tasks:  # Loop through each task in the 'pending_tasks' list
    print(task)  # Print each task

Output:

Pending tasks:
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

You can use various conditions in the WHERE clause to filter data based on different criteria. Here are some examples:

Equality: WHERE status='Pending' fetches rows where the status is ‘Pending’.
Inequality: WHERE status!='Completed' fetches rows where the status is not ‘Completed’.

Applying Conditions with AND & OR Operators

By using the AND and OR operators, you can refine your data queries. These operators allow you to combine conditions to get more specific results.

Using AND Operator

The AND operator is like a strict filter. It requires that all conditions specified in the query must be true for a row to be included in the result set. Let’s say you want to find tasks that are ‘Pending’ AND have IDs less than 4. Here’s how you can use the AND operator:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks that are 'Pending' and have IDs less than 4
cursor.execute("SELECT * FROM tasks WHERE status='Pending' AND id < 4")
filtered_tasks = cursor.fetchall()

# Displaying filtered tasks
print("Filtered tasks with 'Pending' status and IDs less than 4:")
for task in filtered_tasks:
    print(task)

Output:

Filtered tasks with 'Pending' status and IDs less than 4:
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')

Using OR Operator

Now, let’s talk about the OR operator. This one broadens your search. It requires that at least one of the conditions you specify must be true for a row to be included in the results. Here’s how it works:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks that are either 'Completed' or have IDs greater than 4
cursor.execute("SELECT * FROM tasks WHERE status='Completed' OR id > 4")
flexible_tasks = cursor.fetchall()

# Displaying flexible tasks
print("\nFlexible tasks that are 'Completed' or have IDs greater than 4:")
for task in flexible_tasks:
    print(task)

Output:

Flexible tasks that are 'Completed' or have IDs greater than 4:
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Combining AND and OR Operators

You can make your SQLite queries even more powerful by using both the AND and OR operators together. It will help you refine your searches and create more complex conditions.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks that are 'Pending' and have IDs less than 4 OR tasks that are 'Completed'
cursor.execute("SELECT * FROM tasks WHERE (status='Pending' AND id < 4) OR status='Completed'")
combo_tasks = cursor.fetchall()

# Displaying combo tasks
print("\nCombo tasks: 'Pending' and IDs less than 4 OR 'Completed':")
for task in combo_tasks:
    print(task)

Output:

Combo tasks: 'Pending' and IDs less than 4 OR 'Completed':
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')

Sorting Data with ORDER BY Clause

The ORDER BY clause helps you sort your query results and allows you to arrange the rows in your result set based on the values in one or more columns.

Sorting by Single Column

When you want to sort your data by just one column, you use the ORDER BY clause followed by the column name. You can choose to sort in ascending order (default) or descending order by adding ASC or DESC after the column name. Here’s an example:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching all tasks and sorting them by title in ascending order
cursor.execute("SELECT * FROM tasks ORDER BY title ASC")
sorted_tasks = cursor.fetchall()

# Displaying sorted tasks
print("Sorted tasks by title in ascending order:")
for task in sorted_tasks:
    print(task)

In this example, we’re sorting our tasks based on the title column in ascending order.

Output:

Sorted tasks by title in ascending order:
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')
(5, 'Study Python', 'Practice loops and functions', 'Pending')

Sorting by Multiple Columns

To sort your data by multiple columns, you simply list those columns in the ORDER BY clause, separated by commas. SQLite sorts the rows based on the first column specified, and if there are ties, it sorts by the second column, and so on.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching all tasks and sorting them by status in ascending order, then by ID in descending order
cursor.execute("SELECT * FROM tasks ORDER BY status ASC, id DESC")
multi_sorted_tasks = cursor.fetchall()

# Displaying multi-sorted tasks
print("\nTasks sorted by status in ascending order, then by ID in descending order:")
for task in multi_sorted_tasks:
    print(task)

In this example, we’re sorting first by status in ascending order and then by id in descending order.

Output:

Tasks sorted by status in ascending order, then by ID in descending order:
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')

IN & NOT IN Clause

The IN and NOT IN clauses are handy tools that help you filter data based on a list of values. Let’s learn how to use these clauses.

Using the IN Clause

The IN clause is used to check if a column’s value matches any value in a list. This is useful when you want to filter rows that match one of several possible values.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with 'Pending' or 'Completed' status
cursor.execute("SELECT * FROM tasks WHERE status IN ('Pending', 'Completed')")
included_tasks = cursor.fetchall()

# Displaying included tasks
print("Tasks with 'Pending' or 'Completed' status:")
for task in included_tasks:
    print(task)

Output:

Tasks with 'Pending' or 'Completed' status:
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Using the NOT IN Clause

The NOT IN clause works similarly to the IN clause, but it filters out rows that match any value in the specified list. This is useful when you want to exclude certain values.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with statuses other than 'Pending' or 'Complete'
cursor.execute("SELECT * FROM tasks WHERE status NOT IN ('Pending', 'Complete')")
excluded_tasks = cursor.fetchall()

# Displaying excluded tasks
print("\nTasks with statuses other than 'Pending' or 'Complete':")
for task in excluded_tasks:
    print(task)

Output:

Tasks with statuses other than 'Pending' or 'Complete':
(1, 'Finish Homework', 'Complete math assignment', 'Completed')

IS & IS NOT Clause

Before we learn about the IS and IS NOT clauses, it’s important to understand what NULL means in SQLite. NULL represents a missing, undefined, or unknown value. It’s different from zero or an empty string. You cannot use regular comparison operators (=, !=) to check for NULL.

The IS and IS NOT clauses help you handle NULL values in your database.

Using the IS Clause

The IS clause checks if a column’s value is NULL.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with a missing description
cursor.execute("SELECT * FROM tasks WHERE description IS NULL")
missing_description_tasks = cursor.fetchall()

# Displaying tasks with missing descriptions
print("Tasks with a missing description:")
for task in missing_description_tasks:
    print(task)

Output:

Tasks with a missing description:

Using the IS NOT Clause

The IS NOT clause checks if a column’s value is not NULL. Here’s how you can use it:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with a description present
cursor.execute("SELECT * FROM tasks WHERE description IS NOT NULL")
description_present_tasks = cursor.fetchall()

# Displaying tasks with descriptions present
print("\nTasks with a description present:")
for task in description_present_tasks:
    print(task)

Output:

Tasks with a description present:
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Utilizing the BETWEEN Clause

The BETWEEN is a helpful clause for filtering data that falls within a specific range. This can be numbers, dates, or any other type of data. It’s great for when you need to find records that meet certain conditions within a set range

The basic syntax looks like this:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here, value1 and value2 are the boundaries of the range you want to check. Remember one thing, that the BETWEEN clause includes both the value1 and value2 in the result set.

Example:

Let’s say you want to display tasks with IDs between 2 and 4. Here’s how you can do it:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with IDs between 2 and 4
cursor.execute("SELECT * FROM tasks WHERE id BETWEEN 2 AND 4")
selected_tasks = cursor.fetchall()

# Displaying selected tasks
print("Tasks with IDs between 2 and 4:")
for task in selected_tasks:
    print(task)

Output:

Tasks with IDs between 2 and 4:
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')

Pattern Matching with LIKE Clause

The LIKE clause in SQLite helps you search for patterns in text data. It allows you to find rows where a column’s value matches a specified pattern. This is useful when you need to perform searches that are not exact but follow a certain format or pattern.

To define these patterns, you must use some special symbols called wildcards, which allow you to construct flexible search criteria. You use these wildcards as placeholders, you will get a better understanding of this in examples.

Wildcards with LIKE

You can use two main wildcards with the LIKE clause to define patterns:

% (Percent Sign) : This wildcard represents zero, one, or multiple characters. It can be placed anywhere in the pattern and can match any sequence of characters, including no characters at all.

_ (Underscore) : This wildcard represents exactly one character. It is used when you need to match a single character at a specific position in the pattern.

Searching for Specific Words

Let’s say you want to find all tasks where the title starts with “Finish”. You can use the % wildcard to match any characters that come after “Finish”.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with titles starting with 'Finish'
cursor.execute("SELECT * FROM tasks WHERE title LIKE 'Finish%'")
matching_tasks = cursor.fetchall()

# Displaying tasks with titles starting with 'Finish'
print("Tasks with titles starting with 'Finish':")
for task in matching_tasks:
    print(task)

Output:

Tasks with titles starting with 'Finish':
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Exploring Single Character Patterns

Now, let’s search for tasks with titles where the second character is 'i':

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with titles where the second character is 'i'
cursor.execute("SELECT * FROM tasks WHERE title LIKE '_i%'")
second_char_i_tasks = cursor.fetchall()

# Displaying tasks with titles where the second character is 'i'
print("\nTasks with titles where the second character is 'i':")
for task in second_char_i_tasks:
    print(task)

In this example, we’re fetching tasks where the title has 'i' as the second character, _ is the placeholder for the first character, and the % wildcard allows for zero or more characters afterward.

Output:

Tasks with titles where the second character is 'i':
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Combining Wildcards

You can combine % and _ wildcards for more complex patterns. Suppose, we want to display tasks where the third character of a title is 'n', and 'P' appears after the third character.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with titles where the third character is 'n' and 'P' appears after the third character
cursor.execute("SELECT * FROM tasks WHERE title LIKE '__n%P%'")
tasks_with_pattern = cursor.fetchall()

# Displaying tasks with the specified title pattern
print("Tasks with titles matching the pattern '__n%P%':")
for task in tasks_with_pattern:
    print(task)

Output:

Tasks with titles matching the pattern '__n%P%':
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Case-Insensitive Search

By default, LIKE is case-insensitive, meaning it does not differentiate between uppercase and lowercase letters.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with titles containing 'Python'
cursor.execute("SELECT * FROM tasks WHERE title LIKE '%Python%'")
python_tasks = cursor.fetchall()

# Displaying tasks with titles containing 'Python'
print("\nTasks with titles containing 'Python':")
for task in python_tasks:
    print(task)

# Fetching tasks with title ending with 'pRoJect'
cursor.execute("SELECT * FROM tasks WHERE title LIKE '%pRoJect'")
project_tasks = cursor.fetchall()

# Displaying tasks with title ending with 'pRoJect'
print("\nTasks with title ending with 'pRoJect':")
for task in project_tasks:
    print(task)

Output:

Tasks with titles containing 'Python':
(5, 'Study Python', 'Practice loops and functions', 'Pending')

Tasks with title ending with 'pRoJect':
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Pattern Matching with GLOB Clause

The GLOB clause is another way to search for patterns in text data. It’s similar to the LIKE clause but uses different wildcards and is case-sensitive.

Wildcards with GLOB Clause

GLOB supports the following wildcards to define patterns:

*(Asterisk) : Matches zero, one, or more characters.

? (Question Mark) : Matches exactly one character.

[charlist] : Matches any single character in the list.

[^charlist] : Matches any single character not in the list.

Matching Any Sequence of Characters

The * wildcard in the GLOB clause matches any sequence of characters, including zero characters. It’s similar to % in the LIKE clause.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with titles starting with 'Finish'
cursor.execute("SELECT * FROM tasks WHERE title GLOB 'Finish*'")
matching_tasks = cursor.fetchall()

# Displaying tasks with matching titles
print("Tasks with titles starting with 'Finish':")
for task in matching_tasks:
    print(task)

Output:

Tasks with titles starting with 'Finish':
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Matching Any Single Character

The ? wildcard is used as the placeholder for exactly one character.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with titles where the second character is 'i'
cursor.execute("SELECT * FROM tasks WHERE title GLOB '?i*'")
single_char_tasks = cursor.fetchall()

# Displaying tasks with matching titles
print("\nTasks with titles where the second character is 'i':")
for task in single_char_tasks:
    print(task)

In this example, we’re fetching tasks where the title has 'i' as the second character, and the '*' matches any sequence of characters after 'i'.

Output:

Tasks with titles where the second character is 'i':
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Matching Any Single Character in a Range

The [charlist] matches any single character within the specified list or range. It’s useful for more precise searches.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with titles where the second character is either 'i' or 'u'
cursor.execute("SELECT * FROM tasks WHERE title GLOB '?[iu]*'")
char_range_tasks = cursor.fetchall()

# Displaying tasks with matching titles
print("\nTasks with titles where the second character is either 'i' or 'u':")
for task in char_range_tasks:
    print(task)

In this example, we’re fetching tasks where the title has either ‘i’ or ‘u’ as the second character.

Output:

Tasks with titles where the second character is either 'i' or 'u':
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Matching Any Single Character Not in a Range

The [^charlist] matches any single character not within the specified list or range. It’s useful for excluding certain characters. Suppose we need to fetch tasks where the title’s second character should not be a vowel.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks with titles where the second character is not a vowel
cursor.execute("SELECT * FROM tasks WHERE title GLOB '?[^aeiou]*'")
not_vowel_tasks = cursor.fetchall()

# Displaying tasks with matching titles
print("\nTasks with titles where the second character is not a vowel:")
for task in not_vowel_tasks:
    print(task)

Output:

Tasks with titles where the second character is not a vowel:
(5, 'Study Python', 'Practice loops and functions', 'Pending')

Limiting and Paging Data

When you’re working with lots of data, it’s essential to retrieve just the right amount to avoid overwhelming your system. The LIMIT clause can help you with that, it lets you restrict the number of rows returned by a query.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching only the first 3 tasks
cursor.execute("SELECT * FROM tasks LIMIT 3")
limited_tasks = cursor.fetchall()

# Displaying limited tasks
print("First 3 tasks:")
for task in limited_tasks:
    print(task)

In this example, we’re instructing the database to return only the first 3 rows.

Output:

First 3 tasks:
(1, 'Finish Homework', 'Complete math assignment', 'Completed')
(3, 'Buy groceries', 'Get milk, bread, and eggs', 'Pending')
(4, 'Call grandma', 'Wish her a happy birthday', 'Pending')

Paging Through Data

Now, imagine your task list has hundreds or thousands of entries. It’s not practical to load them all at once. Paging allows you to split the data into smaller chunks or “pages”. You can then navigate through these pages, similar to flipping through pages in a book.

To implement paging and retrieve a specific range of rows, we can combine LIMIT clause with OFFSET which specifies the starting point for retrieving rows.

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching tasks starting from the 4th row, with a limit of 3
cursor.execute("SELECT * FROM tasks LIMIT 3 OFFSET 3")
paged_tasks = cursor.fetchall()

# Displaying paged tasks
print("\nNext 3 tasks (starting from the 4th row):")
for task in paged_tasks:
    print(task)

Here, LIMIT 3 means you want to see 3 tasks per page, and OFFSET 3 means you’re skipping the first 3 tasks (which were on the first page) to get to the second page.

Output:

Next 3 tasks (starting from the 4th row):
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Dynamic Paging with Variables

Dynamic paging enables users to navigate through data pages efficiently. This is often done by calculating the offset based on the current page number and page size. Here’s how you can implement dynamic paging using variables:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Assuming page_number and items_per_page are variables
page_number = 2
items_per_page = 3

# Calculating the offset
offset = (page_number - 1) * items_per_page

# Fetching tasks for the specified page
cursor.execute(f"SELECT * FROM tasks LIMIT {items_per_page} OFFSET {offset}")
dynamic_paged_tasks = cursor.fetchall()

# Displaying dynamically paged tasks
print(f"\nTasks for Page {page_number} with {items_per_page} items per page:")
for task in dynamic_paged_tasks:
    print(task)

Output:

Tasks for Page 2 with 3 items per page:
(5, 'Study Python', 'Practice loops and functions', 'Pending')
(6, 'Finish Project', 'Complete project presentation slides', 'Pending')

Altering Table Structure

Altering the structure of a table in SQLite allows you to adjust your database to fit changing needs.

Adding a New Column

When you realize you need to store extra details, you can add an extra column in your table. Let’s add a new column, say due_date, to our 'tasks' table:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Adding a new column 'due_date' to the 'tasks' table
cursor.execute("ALTER TABLE tasks ADD COLUMN due_date DATE")

# Confirming the alteration
conn.commit()

Output:

Removing a Column

If you have a column that’s no longer useful or necessary, you can remove it from the table. Let’s remove the description column:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Removing the 'description' column from the 'tasks' table
cursor.execute("ALTER TABLE tasks DROP COLUMN description")

# Confirming the alteration
conn.commit()

Output:

Working with Functions for Database Operations

In programming, functions are like mini-programs within our code. They help us break down tasks into smaller, more manageable chunks. We can create functions for various database operations: creating, inserting, retrieving, updating, and deleting data, which makes our code cleaner, more organized, and easier to reuse.

Creating a Table

Our first function create_tasks_table() checks if the 'tasks' table exists; if not, it creates one. This ensures we have a proper table structure to hold our tasks.

# Function to create a 'tasks' table if it doesn't exist
def create_tasks_table():
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            description TEXT,
            status TEXT DEFAULT 'Pending'
        )
    ''')
    conn.commit()

Inserting Data

The insert_task() function adds a new task to our 'tasks' table with the provided title, description, and an optional status. Using placeholders ? and passing values separately helps prevent SQL injection.

# Function to insert a task into the 'tasks' table
def insert_task(title, description, status='Pending'):
    cursor.execute('''
        INSERT INTO tasks (title, description, status) 
        VALUES (?, ?, ?)
    ''', (title, description, status))
    conn.commit()

Retrieving Data

The fetch_all_tasks() function fetches all tasks from the 'tasks' table and returns them. This enables us to retrieve and work with our tasks elsewhere in our code.

# Function to fetch all tasks
def fetch_all_tasks():
    cursor.execute('SELECT * FROM tasks')
    return cursor.fetchall()

Updating Data

The update_task_status() function modifies the status of a task with a specific task_id to a new new_status.

# Function to update the status of a task
def update_task_status(task_id, new_status):
    cursor.execute('''
        UPDATE tasks 
        SET status=? 
        WHERE id=?
    ''', (new_status, task_id))
    conn.commit()

Deleting Data

Lastly, delete_task() removes a task from the 'tasks' table based on its task_id.

# Function to delete a task
def delete_task(task_id):
    cursor.execute('DELETE FROM tasks WHERE id=?', (task_id,))
    conn.commit()

Deleting Entire Tables

Delete a Table means getting rid of it completely from your database. This action is permanent, so you should be certain before doing it. To delete a table, you use the DROP TABLE statement followed by the name of the table you want to delete.

Suppose we want to part ways with the ‘tasks’ table. It served its purpose, and now it’s time to bid it farewell:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Dropping the 'completed_tasks' table
cursor.execute("DROP TABLE IF EXISTS tasks")

# Confirming the deletion
conn.commit()

In this example, we use DROP TABLE IF EXISTS tasks statement to delete the 'tasks' table if it exists. The IF EXISTS part ensures that the statement doesn’t throw an error if the table doesn’t exist.

Confirming the Deletion

Once you’ve executed the DROP TABLE statement, the table, and all its data are gone. However, it’s a good practice to confirm the deletion to ensure it was successful.

To confirm that the 'tasks' table is no longer part of our database, we can list all tables:

Example:

import sqlite3

# Creating a connection to our database file
conn = sqlite3.connect('todo_list.db')

cursor = conn.cursor()

# Fetching all tables in the database
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = cursor.fetchall()

# Displaying the remaining tables
print("Remaining Tables:")
for table in tables:
    print(table[0])

Output:

Remaining Tables:

DB Browser for SQLite

DB Browser for SQLite is a free tool for managing SQLite databases. It’s easy to use and works on Windows, macOS, and Linux. Here’s what you need to know:

Key Features:

User-Friendly Interface: Easy to navigate, even if you’re new to databases.
Database Management: Create, modify, and delete databases, tables, and columns.
Data Editing: View and edit data within the database.
Import and Export: Move data in and out of the database in different formats.
Visual Design: Design tables visually and manage indexes easily.
Basic Data Visualization: Generate simple charts and graphs.

Benefits:

Free and Open-Source: No cost and freely available to everyone.
Easy to Use: Intuitive interface, suitable for beginners and experts alike.
Versatile: Useful for developers, data analysts, and hobbyists.
Community Support: Active community for help and updates.

You can download it from the Official Website: DB Browser for SQLite.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

What We’ll Build

How It Works

Table of Contents

Creating a Database and Table

Output:

Adding Data to Your Table

Method 1: Inserting One Row at a Time

Example:

Output:

Method 2: Inserting Multiple Rows in One Go

Output:

Method 3: Using Variables to Insert Data

Output:

Method 4: Inserting Data Using a Dictionary

Output:

Understanding Primary Keys

Retrieving Data Using SELECT Command

Example:

Output:

Fetching All Rows and Specific Columns

Example:

Output:

Fetching Data Conditionally Using WHERE

Example:

Output:

Updating Data with UPDATE Command

Example:

Output:

Removing Records with DELETE

Example:

Output:

Where Clause and Filtering Data

Example:

Output:

Applying Conditions with AND & OR Operators

Using AND Operator

Example:

Output:

Using OR Operator

Example:

Output:

Combining AND and OR Operators

Example:

Output:

Sorting Data with ORDER BY Clause

Sorting by Single Column

Example:

Output:

Sorting by Multiple Columns

Example:

Output:

IN & NOT IN Clause

Using the IN Clause

Example:

Output:

Using the NOT IN Clause

Example:

Output:

IS & IS NOT Clause

Using the IS Clause

Example:

Output:

Using the IS NOT Clause

Example:

Output:

Utilizing the BETWEEN Clause

Example:

Output:

Pattern Matching with LIKE Clause

Wildcards with LIKE

Searching for Specific Words

Example:

Output:

Exploring Single Character Patterns

Example:

Output:

Combining Wildcards

Example:

Output:

Case-Insensitive Search