Detailed method of using MySQL fuzzy query in Python

1. Method 1: Methods using pymysql library

When using MySQL for fuzzy queries in Python, we usually usepymysqlormysql-connector-pythonSuch a library connects to the MySQL database and executes queries. The following is a usepymysqlDetailed examples of fuzzy queries, including installing libraries, connecting to databases, executing queries, and processing results.

1.1 Install pymysql library

First, make sure we have installed itpymysqllibrary. If not, you can install it through pip:

bashCopy the code

pip install pymysql

1.2 Write Python code for fuzzy query

import pymysql  
  
# Database connection configurationconfig = {  
    'host': 'localhost',  
    'user': 'your_username',  
    'password': 'your_password',  
    'database': 'your_database',  
    'charset': 'utf8mb4',  
    'cursorclass':   
}  
  
# Connect to the databaseconnection = (**config)  
  
try:  
    with () as cursor:  
        # Write SQL query statements and use LIKE for fuzzy query        # Suppose we have a table called "articles" with a "content" field and we want to query articles containing the "Python" keyword        sql = "SELECT * FROM articles WHERE content LIKE %s"  
        # In LIKE query, % is a wildcard character, representing any number of characters (including zero characters)        # We need to provide a string containing % for % to build a LIKE query        search_term = '%Python%'  
        (sql, (search_term,))  
  
        # Get all query results        results = ()  
  
        # Process query results        for row in results:  
            # row is a dictionary containing each column of the query result and its corresponding value            print(f"ID: {row['id']}, Title: {row['title']}, Content: {row['content'][:50]}...")  # Print only the first 50 characters of the content as an example  
finally:  
    # Close the database connection    ()

1.3 Things to note

(1) Pleaseyour_username、your_password、your_databaseReplace with the actual username, password, and database name of our MySQL database.

(2) In the above example, we used%LIKE queries are constructed as wildcards.%Python%Will match any string containing "Python", regardless of whether it has other characters before or after it. If we just want to match strings that start with "Python", we can usePython%; Just want to match strings ending with "Python", you can use%Python。

(3) When executing the query, we use a tuple(search_term,)to pass parameters to()method. Note that this tuple has only one element, but also requires a comma to identify it as a tuple, not a normal expression in parentheses.

（4）fetchall()Method is used to get all rows of the query result. If we only need to get some results, we can usefetchone()orfetchmany(size)method.

(5) After processing the database operations, make sure to close the database connection to free up resources. In this example, we use atry...finallyBlock to ensure that the connection can be closed even in the event of an exception.

2. Method 2: Method using mysql-connector-python library

In addition to usingpymysqlIn addition to fuzzy query of MySQL, the library can also use itmysql-connector-pythonLibrary, this is the Python connector provided by MySQL. The following is usedmysql-connector-pythonSample code for fuzzy queries:

2.1 Installing the mysql-connector-python library

If not installedmysql-connector-python, can be installed through pip:

bashCopy the code

pip install mysql-connector-python

2.2 Write Python code for fuzzy query

import   
  
# Database connection configurationconfig = {  
    'host': 'localhost',  
    'user': 'your_username',  
    'password': 'your_password',  
    'database': 'your_database'  
}  
  
# Connect to the databasecnx = (**config)  
  
try:  
    cursor = (dictionary=True)  # Use dictionary cursors to get results as dictionary  
    # Write SQL query statements and use LIKE for fuzzy query    # Suppose we have a table called "articles" with a "content" field and we want to query articles containing the "Python" keyword    query = ("SELECT * FROM articles WHERE content LIKE %s")  
    search_term = '%Python%'  # In LIKE query, % is a wildcard  
    # Note: Parameterized queries in mysql-connector-python need to ensure that % is part of the query string    # Therefore, we directly construct the complete LIKE expression string    (query, (search_term,))  
  
    # Get all query results    results = ()  
  
    # Process query results    for row in results:  
        print(f"ID: {row['id']}, Title: {row['title']}, Content: {row['content'][:50]}...")  # Print only the first 50 characters of the content as an example  
finally:  
    # Close cursor and connection    if cursor:  
        ()  
    if cnx.is_connected():  
        ()

2.3 Things to note

(1) Also need toyour_username、your_password、your_databaseReplace with the actual username, password, and database name of our MySQL database.

(2)()In, we did not specify the character set and cursor type directly, becausemysql-connector-pythonThe default configuration is usually good enough. However, we can add these configuration options if needed.

(3) Use()and()to ensure that both the cursor and the connection are closed correctly.

（4）mysql-connector-pythonAlso supported for the use of context managers (i.e.withstatement) to automatically manage cursors and connection closures, but this requires creating a new cursor class or using a specific context manager. In the example above, we manually closed them to show basic resource management.

(5) When processing database queries, be sure to pay attention to the risks of SQL injection. By using parameterized queries (as shown in the above example), we can ensure that user input is escaped correctly, thus preventing SQL injection attacks.

This is the article about the method of using MySQL fuzzy query in Python. For more related methods of using MySQL fuzzy query in Python, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!