MySQL WHERE Clause in Python

The WHERE clause in SQL is used to filter records based on specified conditions. In Python, you can use the mysql-connector-python library to execute SELECT, UPDATE, DELETE, or any other SQL statement that includes the WHERE clause.

1. Install MySQL Connector

If you haven't already installed the MySQL connector, install it using:

pip install mysql-connector-python

2. Connect to MySQL Database

First, establish a connection to your MySQL database.

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase"  # Change to your database name)# Create a cursor objectcursor = conn.cursor()print("Connected to MySQL database successfully!")

3. Create Sample Table and Insert Data

Let’s create a sample table employees and insert some data to demonstrate using the WHERE clause.

# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()

4. Using WHERE Clause with SELECT Query

4.1 SELECT with WHERE Clause

The WHERE clause is used to filter records based on a condition. For example, to get the details of employees who are older than 30:

# SQL query to select employees older than 30select_query = "SELECT * FROM employees WHERE age > 30"cursor.execute(select_query)# Fetch and print the resultsresults = cursor.fetchall()print("Employees older than 30:")for row in results:    print(row)

This query will retrieve only the records where the employee's age is greater than 30.

4.2 Using WHERE with Multiple Conditions (AND)

You can combine multiple conditions using the AND operator. For example, to select employees who are older than 30 and work in the IT department:

# SQL query to select employees older than 30 and working in IT departmentselect_query = "SELECT * FROM employees WHERE age > 30 AND department = 'IT'"cursor.execute(select_query)# Fetch and print the resultsresults = cursor.fetchall()print("\nEmployees older than 30 and working in IT department:")for row in results:    print(row)

4.3 Using WHERE with OR Clause

You can use the OR operator to select records that match one of the conditions. For example, to select employees who are either in the HR department or older than 30:

# SQL query to select employees in HR department or older than 30select_query = "SELECT * FROM employees WHERE department = 'HR' OR age > 30"cursor.execute(select_query)# Fetch and print the resultsresults = cursor.fetchall()print("\nEmployees in HR or older than 30:")for row in results:    print(row)

4.4 Using WHERE with IN Clause

You can also filter results by checking if a column value matches any value in a list. This is done using the IN clause. For example, to select employees who work in either HR or IT:

# SQL query to select employees in HR or IT departmentselect_query = "SELECT * FROM employees WHERE department IN ('HR', 'IT')"cursor.execute(select_query)# Fetch and print the resultsresults = cursor.fetchall()print("\nEmployees in HR or IT department:")for row in results:    print(row)

4.5 Using WHERE with LIKE Clause

You can use the LIKE clause to match patterns in string data. For example, to select employees whose names start with the letter "A":

# SQL query to select employees whose names start with 'A'select_query = "SELECT * FROM employees WHERE name LIKE 'A%'"cursor.execute(select_query)# Fetch and print the resultsresults = cursor.fetchall()print("\nEmployees whose name starts with 'A':")for row in results:    print(row)

5. Using WHERE Clause with UPDATE

You can use the WHERE clause to update specific records. For example, to update the department of the employee named Alice:

# SQL query to update department for employee 'Alice'update_query = "UPDATE employees SET department = 'Human Resources' WHERE name = 'Alice'"cursor.execute(update_query)# Commit the transactionconn.commit()print("Alice's department updated successfully!")

6. Closing the Connection

After performing the operations, make sure to close the cursor and connection.

cursor.close()conn.close()print("MySQL connection closed.")

Final Complete Code

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase")cursor = conn.cursor()# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()# SELECT with WHERE clause (employees older than 30)select_query = "SELECT * FROM employees WHERE age > 30"cursor.execute(select_query)print("Employees older than 30:")results = cursor.fetchall()for row in results:    print(row)# SELECT with AND condition (older than 30 and in IT)select_query = "SELECT * FROM employees WHERE age > 30 AND department = 'IT'"cursor.execute(select_query)print("\nEmployees older than 30 and working in IT department:")results = cursor.fetchall()for row in results:    print(row)# SELECT with OR condition (HR or older than 30)select_query = "SELECT * FROM employees WHERE department = 'HR' OR age > 30"cursor.execute(select_query)print("\nEmployees in HR or older than 30:")results = cursor.fetchall()for row in results:    print(row)# SELECT with IN clause (HR or IT department)select_query = "SELECT * FROM employees WHERE department IN ('HR', 'IT')"cursor.execute(select_query)print("\nEmployees in HR or IT department:")results = cursor.fetchall()for row in results:    print(row)# SELECT with LIKE clause (names starting with 'A')select_query = "SELECT * FROM employees WHERE name LIKE 'A%'"cursor.execute(select_query)print("\nEmployees whose name starts with 'A':")results = cursor.fetchall()for row in results:    print(row)# Update Alice's department to 'Human Resources'update_query = "UPDATE employees SET department = 'Human Resources' WHERE name = 'Alice'"cursor.execute(update_query)conn.commit()print("\nAlice's department updated successfully!")# Close connectioncursor.close()conn.close()

Summary of WHERE Clause Usage

This is how you can use the WHERE clause in MySQL queries with Python! ?