How to Perform Fuzzy Searches in MySQL with Python’s PyMySQL and mysql‑connector

Method 1: Using the PyMySQL library

When performing fuzzy queries in MySQL from Python, you typically use the pymysql or mysql-connector-python libraries to connect and execute queries. The following example shows how to install PyMySQL, connect, run a LIKE query, and process results.

1.1 Install PyMySQL

First ensure the pymysql package is installed; if not, install it via pip:

pip install pymysql

1.2 Write Python code for fuzzy query

import pymysql

# Database connection configuration
config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

# Connect to the database
connection = pymysql.connect(**config)

try:
    with connection.cursor() as cursor:
        # Use LIKE for fuzzy matching; % is the wildcard
        sql = "SELECT * FROM articles WHERE content LIKE %s"
        search_term = '%Python%'
        cursor.execute(sql, (search_term,))
        results = cursor.fetchall()
        for row in results:
            print(f"ID: {row['id']}, Title: {row['title']}, Content: {row['content'][:50]}...")
finally:
    connection.close()

1.3 Notes

Replace your_username, your_password, and your_database with your actual credentials.

Use % as the wildcard in LIKE; you can adjust the pattern (e.g., Python% or %Python) to match prefixes or suffixes.

The parameter must be passed as a single‑element tuple, e.g., (search_term,).

Use fetchall() to retrieve all rows; fetchone() or fetchmany(size) can retrieve partial results.

A try...finally block ensures the connection is closed even if an error occurs.

Method 2: Using the mysql‑connector‑python library

The official MySQL connector for Python can also perform fuzzy queries. Install it via pip if needed.

2.1 Install mysql-connector-python

pip install mysql-connector-python

2.2 Write Python code for fuzzy query

import mysql.connector

# Database connection configuration
config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database'
}

# Connect to the database
cnx = mysql.connector.connect(**config)

try:
    cursor = cnx.cursor(dictionary=True)
    query = "SELECT * FROM articles WHERE content LIKE %s"
    search_term = '%Python%'
    cursor.execute(query, (search_term,))
    results = cursor.fetchall()
    for row in results:
        print(f"ID: {row['id']}, Title: {row['title']}, Content: {row['content'][:50]}...")
finally:
    if cursor:
        cursor.close()
    if cnx.is_connected():
        cnx.close()

2.3 Notes

Replace the placeholder credentials with your actual MySQL credentials.

The connector’s default settings usually suffice; you can add charset or cursor options if needed.

Close both cursor and connection to release resources.

Using a context manager ( with) can automate cleanup, but the example shows explicit closing.

Always use parameterized queries to avoid SQL injection.