SELECT statement retrieves data from one or more SQLite tables. Python's sqlite3 module executes these queries and provides methods such as fetchall(), fetchmany(), and fetchone() to retrieve the results.

Syntax

SELECT column1, column2
FROM table_name;

To retrieve every column, use the asterisk (*) symbol.

SELECT * FROM table_name;

Creating Sample Database

Before retrieving records, create a database and insert a few sample records. The following example creates a students table and adds three records.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    score INTEGER NOT NULL
)
""")

cursor.execute("DELETE FROM students")

cursor.executemany(
    "INSERT INTO students (name, score) VALUES (?, ?)",
    [
        ("Aman", 85),
        ("Priya", 92),
        ("Rahul", 78)
    ]
)

connection.commit()
connection.close()

Explanation:

• sqlite3.connect() creates or opens the students.db database.
• CREATE TABLE IF NOT EXISTS creates the table if it does not already exist.
• executemany() inserts multiple records using parameter placeholders.
• commit() saves the inserted records, and close() releases the database connection.

1. Reading All Rows Using fetchall()

This method retrieves all remaining rows from the query result and returns them as a list. Each row is represented by a tuple by default.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

cursor.execute("SELECT * FROM students ORDER BY id")

rows = cursor.fetchall()

for row in rows:
    print(row)

connection.close()

Output

Explanation:

• execute() runs the SELECT query.
• fetchall() retrieves all matching rows.
• for loop prints each record separately.
• ORDER BY id ensures the results appear in ascending ID order.

2. Selecting Specific Columns

To retrieve only the required fields, specify their names instead of using an asterisk.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

cursor.execute("SELECT name, score FROM students ORDER BY id")

for row in cursor:
    print(row)

connection.close()

Output

Explanation:

• SELECT name, score retrieves only these two columns.
• Iterating directly over the cursor processes each returned row without first storing every row in a separate list.

3. Reading Limited Number of Rows Using fetchmany()

This method retrieves the next specified number of rows from the result set. It returns a list containing up to the requested number of records.

Syntax

cursor.fetchmany(size)

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

cursor.execute("SELECT * FROM students ORDER BY id")

rows = cursor.fetchmany(2)

for row in rows:
    print(row)

connection.close()

Output

Explanation:

• fetchmany(2) retrieves up to two rows.
• Returned records are stored in a list.
• Calling fetchmany() again on the same query result continues from the next unread row.

4. Reading One Row Using fetchone()

It retrieves the next available row from the result set. It returns a tuple by default, or None when no rows remain.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

cursor.execute("SELECT * FROM students ORDER BY id")

row = cursor.fetchone()
print(row)

connection.close()

Output

(1, 'Aman', 85)

Explanation:

• fetchone() retrieves only the next row.
• Repeated calls advance through the result set.
• If the query returns no matching rows, or all rows have already been fetched, the method returns None.

5. Filtering, Sorting and Limiting Results

SELECT can be combined with other SQL clauses to retrieve specific records. These clauses should be discussed briefly here because separate GeeksforGeeks articles cover them in detail.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

minimum_score = 85

cursor.execute(
    """
    SELECT name, score
    FROM students
    WHERE score >= ?
    ORDER BY score DESC
    LIMIT 3
    """,
    (minimum_score,)
)

for row in cursor:
    print(row)

connection.close()

Output

Explanation:

• WHERE score >= ? filters students whose scores are at least 85.
• ORDER BY score DESC sorts qualifying records from highest to lowest score.
• LIMIT 3 allows a maximum of three results.
• Question-mark placeholder safely binds the supplied value to the query instead of inserting it through string formatting.