Python SQLite – WHERE Clause

The `WHERE` clause in SQLite is used to filter rows returned by a `SELECT` query based on specified conditions. It allows you to retrieve only the rows that meet the specified criteria. In Python, you can use the `WHERE` clause in conjunction with SQL `SELECT` statements when querying data from SQLite database tables.

Here’s how to use the `WHERE` clause to select specific rows from a table in SQLite using Python:

1. Establish Connection: First, establish a connection to the SQLite database file using the `connect()` function from the `sqlite3` module.

2. Create Cursor: After establishing the connection, create a cursor object using the `cursor()` method.

3. Execute SQL Statement: Use the cursor’s `execute()` method to execute an SQL `SELECT` statement with a `WHERE` clause. Specify the conditions for filtering rows in the `WHERE` clause.

4. Fetch Data: After executing the SQL statement, fetch the selected data from the cursor using one of the fetch methods (`fetchone()`, `fetchall()`, or `fetchmany()`).

5. Process Data: Process the fetched data as needed.

6. Close Cursor and Connection: Finally, close the cursor and connection using the `close()` method to release any resources associated with them.

Here’s an example of how to use the `WHERE` clause to select rows from a table named `employees` where the `salary` is greater than 50000:

“`python
import sqlite3

# Establish connection to SQLite database
connection = sqlite3.connect(‘example.db’)

# Create a cursor object
cursor = connection.cursor()

# Execute SQL statement with WHERE clause
cursor.execute(‘SELECT * FROM employees WHERE salary > ?’, (50000,))

# Fetch all selected data
rows = cursor.fetchall()

# Process fetched data
for row in rows:
print(row)

# Close cursor and connection
cursor.close()
connection.close()
“`

In this example:

– We establish a connection to an SQLite database file named `example.db`.
– We create a cursor object using the `cursor()` method.
– We execute an SQL `SELECT * FROM employees WHERE salary > ?` statement with a `WHERE` clause that specifies the condition `salary > 50000`. We use a placeholder `?` in the SQL statement to represent the value that will be supplied as a parameter.
– We pass the parameter `50000` as a tuple `(50000,)` to the `execute()` method to replace the placeholder in the SQL statement.
– We fetch all selected data using the `fetchall()` method and store it in the `rows` variable.
– We iterate over the rows and print each row.
– Finally, we close the cursor and connection.