SQLite Python: Querying Data

Summary: in this tutorial, you will learn how to query data from a table in SQLite database using Python.

How to query data from a table in Python

To query data in an SQLite database from Python, you use these steps:

First, import sqlite3 module:

import sqlite3Code language: JavaScript (javascript)

Second, create a database connection to a SQLite database file by calling the connect() function of the sqlite3 module:

conn = sqlite3.connect(database)

Third, create a Cursor object by calling the cursor() method of the Connection object:

cur = conn.cursor()

Fourth, execute a SELECT statement:

cur.execute('select * from table')Code language: JavaScript (javascript)

In this syntax, you need to replace the SELECT statement with the actual one.

Fifth, call the fetchall() method of the Cursor object to fetch the rows returned by the SELECT statement:

rows = cur.fetchall()

The fetchall() returns a list of tuples, each tuple contains field values of a row.

If the query returns one row, you can use the fetchone() method instead:

row = cur.fetchone()

The fetchone() method returns a single row as a tuple.

If you want to select a specified number of rows, you can use the fetchmany() method:

rows = fetchmany(size)

In this syntax, the size specifies the number of rows you want to fetch.

Sixth, close the database connection:

conn.close()Code language: CSS (css)

Finally, iterate over the rows and process each of them individually:

for row in rows:
   print(row)Code language: PHP (php)

Here are the complete steps:

import sqlite3

conn = sqlite3.connect(database)
cur = conn.cursor()
cur.execute('SELECT * FROM table')
rows = cur.fetchall()
conn.close()
for row in rows:
   print(row)Code language: JavaScript (javascript)

Since the Connection object is a context manager, you can use the with statement to automatically close it:

import sqlite3

with sqlite3.connect(database) as conn:
    cur = conn.cursor()
    cur.execute('SELECT * FROM sample_table')
    rows = cur.fetchall()
    for row in rows:
        print(row)
Code language: JavaScript (javascript)

Since an error may occur while querying the database, you can handle them using a try-except statement:

import sqlite3

try:
    with sqlite3.connect(database) as conn:
        cur = conn.cursor()
        cur.execute('SELECT * FROM table')
        rows = cur.fetchall()
        for row in rows:
            print(row)
except sqlite3.Error as e:
    print(e)Code language: PHP (php)

Querying all rows from a table example

We’ll use the tasks table created in the previous tutorial:

The following program shows how to query all rows from the tasks table:

import sqlite3

try:
    with sqlite3.connect('my.db') as conn:
        cur = conn.cursor()
        cur.execute('select id, name, priority from tasks')
        rows = cur.fetchall()
        for row in rows:
            print(row)
except sqlite3.OperationalError as e:
    print(e)Code language: PHP (php)

Output:

(1, 'Analyze the requirements of the app', 3)
(2, 'Confirm with user about the top requirements', 1)Code language: JavaScript (javascript)

The output shows two rows from the tasks table.

Notice that each row is a tuple that contains the selected columns. To access an individual column, you use this syntax:

row[index]Code language: CSS (css)

For example, the following program displays all task titles:

import sqlite3

try:
    with sqlite3.connect('my.db') as conn:
        cur = conn.cursor()
        cur.execute('select id, name, priority from tasks')
        rows = cur.fetchall()
        for row in rows:
            print(row[1])
except sqlite3.OperationalError as e:
    print(e)Code language: PHP (php)

Output:

Analyze the requirements of the app
Confirm with user about the top requirementsCode language: JavaScript (javascript)

In this example, the row[1] returns the second column of the selected rows.

Querying data with parameters

To bind variables to a query, you can use the placeholder (?) in the SELECT statement. For example, the following query retrieves a task with a specified id:

SELECT id, title 
FROM tasks 
WHERE id = ?Code language: SQL (Structured Query Language) (sql)

In this syntax, the question mark (?) is a placeholder that will be replaced by an actual value passed to the execute() method:

cur.execute(SELECT id, title FROM tasks WHERE id = ?, (id,))

Notice that you pass a tuple that includes the id value as the second argument to the execute() method.

The following program illustrates how to retrieve the task with id 1:

import sqlite3

try:
    with sqlite3.connect('my.db') as conn:
        cur = conn.cursor()
        cur.execute('SELECT id, name, priority FROM tasks WHERE id =?', (1,))
        row = cur.fetchone()
        if row:
            print(row)
except sqlite3.OperationalError as e:
    print(e)Code language: PHP (php)

Output:

(1, 'Analyze the requirements of the app', 3)Code language: JavaScript (javascript)

To make the code reusable, you can define a function as follows:

import sqlite3

def get_task_by_id(id: int) -> tuple:
    try:
        with sqlite3.connect('my.db') as conn:
            cur = conn.cursor()
            cur.execute('select id, name, priority from tasks where id =?', (id,))
            row = cur.fetchone()
            return row, None
    except sqlite3.OperationalError as e:
        return None, e      

if __name__ == '__main__':
    task, error = get_task_by_id(1)
    if error is not None:
        print(f'Error: {error}')
    else:
        print(task)Code language: PHP (php)

Summary

• Use the fetchall() method of the cursor object to return all rows of a query.
• Use the fetchone() method to return the next row returned by a query.
• Use the fetchmany() method to return some rows from a query.