Python MySQL Where

To filter data using a WHERE clause in MySQL with Python, you can follow the steps below. The WHERE clause is used to filter records based on a specified condition.

1. Install MySQL Connector

If you haven’t installed the MySQL connector yet, use this command:

pip install mysql-connector-python

2. Connect to the MySQL Database

You need to connect to the database and execute queries using the WHERE clause.

Example Python Script to Select Data Using WHERE Clause

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",         # Change this to your MySQL host if needed
  user="yourusername",       # Change this to your MySQL username
  password="yourpassword",   # Change this to your MySQL password
  database="mynewdatabase"   # Change this to your database name
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to select data using the WHERE clause
sql = "SELECT * FROM customers WHERE address = %s"
val = ("123 Elm Street",)

# Execute the query with the WHERE clause
mycursor.execute(sql, val)

# Fetch all matching rows
result = mycursor.fetchall()

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

Explanation:

• mycursor.execute(sql, val): Executes the SQL query. The %s is used as a placeholder for the value. The val tuple contains the actual value ("123 Elm Street") that replaces the placeholder.
• mycursor.fetchall(): Fetches all rows that match the condition specified in the WHERE clause.

Example Output:

If there’s a customer with the address “123 Elm Street”, the output will look like this:

(1, 'John Doe', '123 Elm Street')

3. Using Multiple Conditions in WHERE

You can also use multiple conditions with the AND or OR operators:

Example Using AND

sql = "SELECT * FROM customers WHERE name = %s AND address = %s"
val = ("John Doe", "123 Elm Street")

mycursor.execute(sql, val)

# Fetch all matching rows
result = mycursor.fetchall()

for row in result:
    print(row)

Example Using OR

sql = "SELECT * FROM customers WHERE name = %s OR address = %s"
val = ("John Doe", "456 Maple Avenue")

mycursor.execute(sql, val)

# Fetch all matching rows
result = mycursor.fetchall()

for row in result:
    print(row)

4. Using LIKE for Pattern Matching

You can use the LIKE operator in the WHERE clause for pattern matching. This is useful for partial string matches.

sql = "SELECT * FROM customers WHERE address LIKE %s"
val = ("%Elm%",)  # This will match any address containing "Elm"

mycursor.execute(sql, val)

# Fetch all matching rows
result = mycursor.fetchall()

for row in result:
    print(row)

5. Full Example: Using the WHERE Clause with Filtering

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mynewdatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# Select customers based on a specific condition
sql = "SELECT name, address FROM customers WHERE address = %s"
val = ("123 Elm Street",)

# Execute the query with the WHERE clause
mycursor.execute(sql, val)

# Fetch all matching rows
result = mycursor.fetchall()

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

# Close the connection
mydb.close()

6. Closing the Connection

Always close the connection after you’re done:

mydb.close()

This script will allow you to filter data using the WHERE clause, and you can use logical operators like AND, OR, and pattern matching with LIKE for more complex filtering conditions.