Visitar URL original
Python SQLite - WHERE Clause - GeeksforGeeks

Python SQLite - WHERE Clause

Last Updated : 4 Nov, 2026

WHERE clause evaluates a condition for each row and includes only records for which the condition is true. Rows producing false or NULL are excluded from the result.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

  • SELECT specifies the columns to retrieve.
  • FROM identifies the table.
  • WHERE defines the condition used to filter records.

Creating Sample Table

The following example creates a students table and inserts sample records. An in-memory database is used so the example does not create a database file.

Python 
import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER,
        department TEXT
    )
""")

cursor.executemany(
    """
    INSERT INTO students (id, name, age, department)
    VALUES (?, ?, ?, ?)
    """,
    [
        (1, "Rohit", 21, "IT"),
        (2, "Nitin", 21, "IT"),
        (3, "Virat", 30, "CIVIL"),
        (4, "Rohit", 32, "COMP")
    ]
)

connection.commit()

Explanation:

  • sqlite3.connect(":memory:") creates a temporary database that exists while the connection remains open.
  • CREATE TABLE defines the columns used in the examples.
  • executemany() inserts multiple records using parameter placeholders.
  • commit() saves the inserted data within the current database connection.

Using WHERE Clause

Example 1: Retrieve Records Matching a Condition. Suppose we need to retrieve students belonging to the IT department.

Python 
cursor.execute(
    """
    SELECT id, name, age, department
    FROM students
    WHERE department = ?
    ORDER BY id
    """,
    ("IT",)
)

print(cursor.fetchall())

Output

[(1, 'Rohit', 21, 'IT'), (2, 'Nitin', 21, 'IT')]

  • Explanation:
  • WHERE department = ? selects records whose department matches the supplied value.
  • tuple ("IT",) supplies the value for the placeholder.
  • fetchall() returns all matching rows as a list of tuples.
  • ORDER BY id sorts the returned records by their IDs.

Example 2: Filter Records Using Comparison Operators. Comparison operators help select records according to numerical or other comparable values.

Python 
cursor.execute(
    """
    SELECT name, age
    FROM students
    WHERE age >= ?
    ORDER BY id
    """,
    (30,)
)

print(cursor.fetchall())

Output

[('Virat', 30), ('Rohit', 32)]

Explanation:

  • condition age >= ? selects students aged 30 or older.
  • Value 30 is passed separately as a query parameter.
  • Only records satisfying the condition appear in the result.
Comment

Explore