Of course! Using the LIKE operator in Python with MySQL is a very common task. It allows you to perform pattern matching in your SQL queries, similar to regular expressions but with a simpler syntax.

Here's a comprehensive guide covering the basics, advanced patterns, and best practices.

The Basic LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

• (percent sign) represents zero, one, or multiple characters.
• _ (underscore) represents a single character.

The Setup (Prerequisites)

First, you need to have a MySQL database and a Python environment with the mysql-connector-python library installed.

Install the library:

pip install mysql-connector-python

Create a sample table and insert data: Let's create a table called employees.

CREATE DATABASE company_db;
USE company_db;
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    email VARCHAR(100)
);
INSERT INTO employees (first_name, last_name, department, email) VALUES
('John', 'Doe', 'Engineering', 'john.doe@example.com'),
('Jane', 'Smith', 'Marketing', 'jane.smith@example.com'),
('Peter', 'Jones', 'Engineering', 'peter.j@work.com'),
('Mary', 'Williams', 'Sales', 'mary.w@example.com'),
('David', 'Brown', 'Engineering', 'david.brown@example.com'),
('Susan', 'Miller', 'HR', 'susan.miller@company.com');

Python Examples with LIKE

Now, let's connect to this database from Python and run various LIKE queries.

Example 1: Finding Names Starting with 'J'

We want to find all employees whose first name starts with 'J'.

• SQL Pattern: 'J%'
• Python Code:

import mysql.connector
from mysql.connector import Error
def find_names_starting_with_j():
    try:
        # Establish the connection
        connection = mysql.connector.connect(
            host='localhost',      # or your host IP
            database='company_db',
            user='your_username',   # replace with your username
            password='your_password' # replace with your password
        )
        if connection.is_connected():
            cursor = connection.cursor()
            # The SQL query with the LIKE operator
            sql_query = "SELECT first_name, last_name FROM employees WHERE first_name LIKE %s"
            # The pattern to search for. Note the use of a tuple.
            search_pattern = "J%"
            # Execute the query
            cursor.execute(sql_query, (search_pattern,))
            # Fetch all the records
            records = cursor.fetchall()
            print(f"Found {cursor.rowcount} employees with first name starting with 'J':")
            for row in records:
                print(f" - {row[0]} {row[1]}")
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
    finally:
        # Closing the database connection.
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed.")
# Run the function
find_names_starting_with_j()

Output:

Found 2 employees with first name starting with 'J':
 - John Doe
 - Jane Smith
MySQL connection is closed.

Example 2: Finding Names Ending with 's'

We want to find all employees whose last name ends with 's'.

• SQL Pattern: '%s'
• Python Code:

def find_names_ending_with_s():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='company_db',
            user='your_username',
            password='your_password'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            sql_query = "SELECT first_name, last_name FROM employees WHERE last_name LIKE %s"
            search_pattern = "%s" # Ends with 's'
            cursor.execute(sql_query, (search_pattern,))
            records = cursor.fetchall()
            print(f"\nFound {cursor.rowcount} employees with last name ending with 's':")
            for row in records:
                print(f" - {row[0]} {row[1]}")
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
    finally:
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()
find_names_ending_with_s()

Output:

Found 1 employees with last name ending with 's':
 - Jones Peter
MySQL connection is closed.

(Note: The output shows 'Peter Jones' because the order of columns in the result is first_name, last_name.)

Example 3: Finding Names Containing 'ar'

We want to find all employees whose first or last name contains the substring 'ar'.

• SQL Pattern: '%ar%'
• Python Code:

def find_names_containing_ar():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='company_db',
            user='your_username',
            password='your_password'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            sql_query = "SELECT first_name, last_name FROM employees WHERE first_name LIKE %s OR last_name LIKE %s"
            search_pattern = "%ar%"
            cursor.execute(sql_query, (search_pattern, search_pattern)) # Pass the pattern twice
            records = cursor.fetchall()
            print(f"\nFound {cursor.rowcount} employees with 'ar' in their name:")
            for row in records:
                print(f" - {row[0]} {row[1]}")
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
    finally:
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()
find_names_containing_ar()

Output:

Found 2 employees with 'ar' in their name:
 - Mary Williams
 - Susan Miller
MySQL connection is closed.

Example 4: Finding Names with a Specific Second Character

We want to find all employees whose first name has 'a' as the second character (e.g., Ja...).

• SQL Pattern: 'J_a%'
• Python Code:

def find_names_with_specific_second_char():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='company_db',
            user='your_username',
            password='your_password'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            sql_query = "SELECT first_name, last_name FROM employees WHERE first_name LIKE %s"
            search_pattern = "J_a%" # Second character is 'a'
            cursor.execute(sql_query, (search_pattern,))
            records = cursor.fetchall()
            print(f"\nFound {cursor.rowcount} employees with 'a' as the second character in their first name:")
            for row in records:
                print(f" - {row[0]} {row[1]}")
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
    finally:
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()
find_names_with_specific_second_char()

Output:

Found 1 employees with 'a' as the second character in their first name:
 - Jane Smith
MySQL connection is closed.

Escaping Special Characters ( and _)

What if you want to search for a literal or _ in your string? For example, finding an email address that contains the literal string user@example.com.

You need to use the ESCAPE clause. You choose a special escape character (often \ or ) and tell MySQL to treat any or _ following it as a literal character.

Scenario: Find all employees with an email containing example.com.

• SQL Pattern: '%example\.com%' (using \ as the escape character)
• Python Code:

def find_emails_with_literal_dot():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='company_db',
            user='your_username',
            password='your_password'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            # The query uses the ESCAPE clause
            sql_query = "SELECT first_name, email FROM employees WHERE email LIKE %s ESCAPE '\\'"
            # The pattern must also be prepared with the escape character
            search_pattern = "%example\\.com%"
            cursor.execute(sql_query, (search_pattern,))
            records = cursor.fetchall()
            print(f"\nFound {cursor.rowcount} employees with 'example.com' in their email:")
            for row in records:
                print(f" - {row[0]}: {row[1]}")
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
    finally:
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()
find_emails_with_literal_dot()

Output:

Found 2 employees with 'example.com' in their email:
 - John: john.doe@example.com
 - Jane: jane.smith@example.com
MySQL connection is closed.

Security: Preventing SQL Injection

Notice how we pass the search pattern as a parameter to the cursor.execute() method:

# GOOD - Parameterized Query
cursor.execute("SELECT * FROM employees WHERE first_name LIKE %s", ("J%",))
# BAD - String Concatenation (Vulnerable to SQL Injection)
# pattern = "J%"
# query = "SELECT * FROM employees WHERE first_name LIKE '" + pattern + "'"
# cursor.execute(query)

Never build your SQL query by concatenating strings with user input. This is a classic SQL injection vulnerability. The mysql-connector-python library automatically sanitizes parameters passed to execute(), making it safe.

Case-Insensitive LIKE

By default, LIKE is case-sensitive on many systems. To make it case-insensitive, you have two main options:

Option A: Use the LOWER() function (Recommended)

This is the most portable and reliable method.

sql_query = "SELECT first_name, last_name FROM employees WHERE LOWER(first_name) LIKE %s"
search_pattern = "%joh%" # Will match 'John', 'john', 'JOHN', etc.
cursor.execute(sql_query, (search_pattern,))

Option B: Change the MySQL Collation

You can change the table or column collation to a case-insensitive one (e.g., utf8mb4_general_ci where ci stands for case-insensitive). This is a database-level setting and affects all queries on that column.

-- To change an existing column
ALTER TABLE employees MODIFY first_name VARCHAR(50) COLLATE utf8mb4_general_ci;

After doing this, a standard LIKE query will become case-insensitive.