MySQL LIKE (Python Programming)

Why This Matters

In Python programming, interacting with databases is essential for developing web applications and data analysis projects. The LIKE operator in MySQL is a crucial tool for searching and filtering data within tables. Understanding its usage helps you write more efficient queries, avoid errors that could lead to incorrect results or slow performance, and make your code more powerful and flexible.

Prerequisites

To follow this lesson, you should have a basic understanding of Python programming, SQL syntax, and be familiar with working with databases using the mysql-connector-python library. If you're new to these topics, we recommend checking out our lessons on Python, SQL, and database connections in Python before diving into this one.

Python Prerequisites

• Understanding of variables, data types, functions, and control structures such as loops and conditionals
• Familiarity with file I/O operations using built-in libraries like os and sys

SQL Prerequisites

• Basic understanding of SQL syntax, including SELECT, FROM, WHERE, JOIN, GROUP BY, and ORDER BY statements
• Knowledge of database entities such as tables, columns, rows, and primary keys

MySQL-Connector-Python Prerequisites

• Familiarity with installing and using third-party libraries in Python
• Understanding of how to establish a connection to a MySQL database and execute SQL queries using the mysql-connector-python library

Core Concept

The MySQL LIKE operator is used for pattern matching within strings. It allows you to search for specific patterns or substrings within a column of data. The basic syntax for using the LIKE operator in MySQL is:

SELECT column_name FROM table_name WHERE column_name LIKE 'pattern';

Replace column_name, table_name, and 'pattern' with your specific values. The pattern can include wildcard characters, such as % (percent sign) and _ (underscore), to match multiple or single characters respectively.

Wildcard Characters

• %: Matches any sequence of characters (zero or more). For example, 'user%' would match 'user1', 'user22', 'users', etc.
• _: Matches exactly one character. For example, 'us_r' would match 'user' but not 'users'.

Escaping Wildcard Characters (Expanded)

To use wildcard characters as part of the pattern without interpreting their special meaning, you can escape them by prefixing with a backslash (\). For example:

SELECT column_name FROM table_name WHERE column_name LIKE '\%user\%';

Worked Example

Let's assume we have a simple MySQL database named example_db, with a table called users. The table contains the following data:

| id | name | email |

|----|-------|---------------------|

| 1 | John | john@example.com |

| 2 | Jane | jane@example.com |

| 3 | Bob | bob@example.org |

| 4 | Alice | alice@example.net |

| 5 | Mike | mike@example.edu |

To find all users with email addresses ending in .com, we can use the following SQL query:

SELECT * FROM users WHERE email LIKE '%.com';

Running this query will return:

| id | name | email |

|----|-------|---------------------|

| 1 | John | john@example.com |

| 2 | Jane | jane@example.com |

Python Code Walkthrough (Expanded)

To execute the above SQL query using Python, you can use the mysql-connector-python library:

import mysql.connector
from mysql.connector import Error

def create_connection():
connection = None
try:
connection = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="example_db"
)
print("Connection to MySQL DB successful")
except Error as e:
print(f"The error '{e}' occurred")

return connection

def execute_query(connection, query):
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
print("Query executed successfully")
except Error as e:
print(f"The error '{e}' occurred")

def fetch_all(cursor):
rows = cursor.fetchall()
for row in rows:
print(row)

connection = create_connection()
query = "SELECT * FROM users WHERE email LIKE '%.com';"
execute_query(connection, query)
fetch_all(connection.cursor())

Common Mistakes

1. Forgetting to escape wildcard characters within the pattern:

SELECT column_name FROM table_name WHERE column_name LIKE '%user%'; // Incorrect

Correct usage:

SELECT column_name FROM table_name WHERE column_name LIKE '\%user\%';

1. Using = instead of LIKE for exact matches:

SELECT column_name FROM table_name WHERE column_name = 'pattern'; // Incorrect

Correct usage:

SELECT column_name FROM table_name WHERE column_name LIKE 'pattern';

1. Not accounting for case sensitivity when searching:

SELECT column_name FROM table_name WHERE column_name LIKE '%user%'; // Case sensitive

To make the search case-insensitive, add LOWER() function around the pattern:

SELECT column_name FROM table_name WHERE LOWER(column_name) LIKE LOWER('%user%');

1. Using incorrect wildcard characters:

SELECT column_name FROM table_name WHERE column_name LIKE 'pat_n'; // Incorrect underscore usage

Correct usage for single character wildcards:

SELECT column_name FROM table_name WHERE column_name LIKE '_user%';

1. Using the LIKE operator with numbers or dates (Note that this is not a mistake, but it's important to understand its limitations):

SELECT column_name FROM table_name WHERE column_name LIKE 'pattern'; // Incorrect for numbers or dates

For number and date comparisons, you should use other SQL operators like =, <, >, etc.

Practice Questions

1. Write an SQL query to find all users with email addresses containing the word 'example'.

SELECT * FROM users WHERE email LIKE '%example%';

1. Write a Python script to execute the above SQL query and fetch all results:

import mysql.connector
from mysql.connector import Error

def create_connection():
connection = None
try:
connection = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="example_db"
)
print("Connection to MySQL DB successful")
except Error as e:
print(f"The error '{e}' occurred")

return connection

def execute_query(connection, query):
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
print("Query executed successfully")
except Error as e:
print(f"The error '{e}' occurred")

def fetch_all(cursor):
rows = cursor.fetchall()
for row in rows:
print(row)

connection = create_connection()
query = "SELECT * FROM users WHERE email LIKE '%example%';"
execute_query(connection, query)
fetch_all(connection.cursor())

1. Write an SQL query to find all users with names starting with 'J' and email addresses containing the word 'example'.

SELECT * FROM users WHERE name LIKE 'J%' AND email LIKE '%example%';

1. Write a Python script to execute the above SQL query and fetch all results:

import mysql.connector
from mysql.connector import Error

def create_connection():
connection = None
try:
connection = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="example_db"
)
print("Connection to MySQL DB successful")
except Error as e:
print(f"The error '{e}' occurred")

return connection

def execute_query(connection, query):
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
print("Query executed successfully")
except Error as e:
print(f"The error '{e}' occurred")

def fetch_all(cursor):
rows = cursor.fetchall()
for row in rows:
print(row)

connection = create_connection()
query = "SELECT * FROM users WHERE name LIKE 'J%' AND email LIKE '%example%';"
execute_query(connection, query)
fetch_all(connection.cursor())

1. Write an SQL query to find all users with names starting with 'A' or 'B', and email addresses ending in .com.

SELECT * FROM users WHERE (name LIKE 'A%' OR name LIKE 'B%') AND email LIKE '%.com';

1. Write a Python script to execute the above SQL query and fetch all results:

import mysql.connector
from mysql.connector import Error

def create_connection():
connection = None
try:
connection = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="example_db"
)
print("Connection to MySQL DB successful")
except Error as e:
print(f"The error '{e}' occurred")

return connection

def execute_query(connection, query):
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
print("Query executed successfully")
except Error as e:
print(f"The error '{e}' occurred")

def fetch_all(cursor):
rows = cursor.fetchall()
for row in rows:
print(row)

connection = create_connection()
query = "SELECT * FROM users WHERE (name LIKE 'A%' OR name LIKE 'B%') AND email LIKE '%.com';"
execute_query(connection, query)
fetch_all(connection.cursor())

FAQ

Can I use the LIKE operator with numbers or dates?

No, the LIKE operator is designed for string comparisons only. For number and date comparisons, you should use other SQL operators like =, <, >, etc.

How can I make the search case-insensitive using Python?

You can make the search case-insensitive by adding the LOWER() function around the pattern in both the SQL query and the Python script:

SELECT column_name FROM table_name WHERE LOWER(column_name) LIKE LOWER('%user%');

query = "SELECT * FROM users WHERE LOWER(email) LIKE LOWER('%example%');"
execute_query(connection, query)
fetch_all(connection.cursor())

How can I search for a specific pattern at the beginning or end of a string using wildcards?

You can use the LIKE operator with the wildcard characters % and _ to search for patterns at the beginning, middle, or end of strings. To find a pattern at the beginning of a string, use the wildcard character %:

SELECT column_name FROM table_name WHERE column_name LIKE 'pattern%';

To find a pattern at the end of a string, use the wildcard character % after the pattern:

SELECT column_name FROM table_name WHERE column_name LIKE '%pattern';

How can I search for a specific pattern exactly once within a string using wildcards?

You can use the underscore (_) wildcard character to match exactly one character in your pattern:

SELECT column_name FROM table_name WHERE column_name LIKE 'pattern\_';

How can I search for a specific pattern that may appear multiple times within a string using wildcards?

You can use the percent sign (%) wildcard character to match any sequence of characters (zero or more):

SELECT column_name FROM table_name WHERE column_name LIKE '%pattern%';
``