SQLite WHERE Clause with Syntax and Python Examples

SQLite WHERE Clause

Description

The WHERE clause in SQLite is used to filter records based on specified conditions. It is commonly applied to SELECT, UPDATE, and DELETE statements. By using the WHERE clause, you can extract only the data that meets specific criteria, making queries more efficient and targeted.

Syntax:

-- SELECT with WHERE
SELECT column1, column2 
FROM table_name
WHERE condition;

-- UPDATE with WHERE
UPDATE table_name
SET column1 = value1
WHERE condition;

-- DELETE with WHERE
DELETE FROM table_name
WHERE condition;

Operators in WHERE Clause

The WHERE clause supports various operators to filter data:

• Comparison Operators: =, !=, >, <, >=, <=
• Logical Operators: AND, OR, NOT
• Pattern Matching: LIKE, GLOB
• Range Check: BETWEEN
• Set Membership: IN
• Null Check: IS NULL, IS NOT NULL

Examples:

Example 1: SELECT with WHERE

Code:

# Import the SQLite module
import sqlite3

# Connect to SQLite database (or create if it doesn't exist)
connection = sqlite3.connect("example.db")

# Create a cursor object to execute SQL commands
cursor = connection.cursor()

# Create a table named "products" if it doesn't already exist
cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,  # Auto-incremented primary key
        name TEXT NOT NULL,      # Product name
        price REAL               # Product price
    )
""")

# Insert data into the table
cursor.executemany(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    [("Laptop", 1200.50), ("Smartphone", 800.00), ("Tablet", 300.00)]
)

# Commit changes
connection.commit()

# Fetch products with a price greater than 500
cursor.execute("SELECT * FROM products WHERE price > ?", (500,))
results = cursor.fetchall()

# Print the results
for row in results:
    print(row)

# Close the connection
connection.close()

Explanation:

1. Creating and Populating a Table: The products table is created, and sample data is inserted.

2. Using WHERE Clause: The WHERE price > 500 filters products costing more than $500.

3. Fetching Results: The filtered rows are retrieved using fetchall() and printed.

Example 2: UPDATE with WHERE

Code:

# Import SQLite module
import sqlite3

# Connect to SQLite database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Update the price of the product named 'Tablet'
cursor.execute("UPDATE products SET price = ? WHERE name = ?", (350.00, "Tablet"))

# Commit changes
connection.commit()

# Verify the update
cursor.execute("SELECT * FROM products WHERE name = ?", ("Tablet",))
print(cursor.fetchone())

# Close the connection
connection.close()

Explanation:

1. Updating a Row: The UPDATE statement modifies the price of the product named 'Tablet' using the WHERE clause.

2. Verifying Changes: The updated row is fetched and printed.

Example 3: DELETE with WHERE

Code:

# Import SQLite module
import sqlite3

# Connect to SQLite database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Delete products with a price less than 400
cursor.execute("DELETE FROM products WHERE price < ?", (400,))

# Commit changes
connection.commit()

# Verify deletion
cursor.execute("SELECT * FROM products")
print(cursor.fetchall())

# Close the connection
connection.close()

Explanation:

1. Deleting Rows: The DELETE statement removes products priced under $400 using the WHERE clause.

2. Verifying Deletion: Remaining rows are printed to confirm successful deletion.

Why use the WHERE Clause?

• Filters data for more precise queries.
• Limits updates or deletions to specific records.
• Improves database query performance by reducing unnecessary data processing.

Practical Guides to SQLite Snippets and Examples.