[Python] Basics of MySQL Operations: Connection Settings with mysqlclient and Data Retrieval Using the IN Clause

I will explain how to use mysqlclient (import name: MySQLdb), which is the most standard driver when manipulating MySQL databases from Python. I will introduce practical code ranging from managing connection information to advanced SQL execution methods that include list-type data in conditions.

Meaning and Setting of Connection Parameters

Here are the main arguments passed to the connect() function when connecting to a database.

Library Installation

Execute the following command in your Python environment to install the library.

pip install mysqlclient

Practical Sample Code: Searching for Article Data

Here, assuming a blog system, I will implement a process to retrieve article data that is “after a specified ID” and “included in a specific list of category IDs.”

In particular, when passing a Python list (array) to the SQL IN clause, a single %s cannot handle it. Therefore, a technique to dynamically generate placeholders according to the length of the list is required.

import MySQLdb
import os

# Define connection information in a dictionary
# In actual operation, it is recommended to load this from environment variables
DB_CONFIG = {
    "user": "blog_admin",
    "passwd": "secure_password_here",
    "host": "localhost",
    "db": "cms_database",
    "port": 3306,
    "charset": "utf8mb4"
}

def main():
    # Search criteria
    # Search for articles where ID is greater than 100 AND category ID is one of [1, 5, 9]
    min_post_id = 100
    target_category_ids = [1, 5, 9]

    # [Important] Dynamically generate placeholders for the IN clause
    # If the list has 3 elements, it creates the string "%s, %s, %s"
    format_strings = ', '.join(['%s'] * len(target_category_ids))

    # Constructing the SQL statement
    # For security, use placeholders instead of embedding values directly
    sql = f"""
        SELECT 
            id, 
            title, 
            category_id, 
            published_at 
        FROM 
            blog_posts 
        WHERE 
            id > %s 
            AND category_id IN ({format_strings})
    """

    # Preparing parameters
    # Combine the ID condition and the category ID list into a single tuple or list
    # The execute method requires flat (non-nested) arguments
    params = [min_post_id] + target_category_ids

    print(f"Executing Query: {sql}")
    print(f"Parameters: {params}")

    try:
        # Database connection
        # Unpacking the dictionary with **DB_CONFIG and passing it as arguments
        with MySQLdb.connect(**DB_CONFIG) as conn:
            
            # Getting the cursor (Using DictCursor allows dictionary retrieval, but here we use the standard tuple format)
            with conn.cursor() as cur:
                
                # Execute SQL
                cur.execute(sql, params)
                
                # Fetch results
                rows = cur.fetchall()
                
                # Display results
                print(f"\nFound {len(rows)} articles:")
                print("-" * 30)
                for row in rows:
                    # row is a tuple (id, title, category_id, published_at)
                    print(f"ID: {row[0]} | Title: {row[1]} | Date: {row[3]}")

    except MySQLdb.Error as e:
        print(f"Database Error occurred: {e}")
    except Exception as e:
        print(f"An unexpected error occurred: {e}")

if __name__ == "__main__":
    main()

Detailed Explanation of the Code

1. Dictionary Unpacking of Connection Information (**con_info)

Instead of listing arguments like MySQLdb.connect(user="root", passwd="...", ...), we define a dictionary DB_CONFIG and write **DB_CONFIG when calling the function. This expands the dictionary keys and values as arguments. This separates configuration from logic and makes the code easier to read.

2. with Statement and Connection Management

By using the with MySQLdb.connect(...) as conn: block, appropriate termination processing is performed when the process exits the block (or when an error occurs). However, for update processes that require explicit transaction management (commit / rollback), it is sometimes written manually to make the intention clear.

3. Generating Placeholders for the IN Clause

The most important point to note is that writing AND category_id IN (%s) will not correctly handle the list [1, 5, 9]. MySQLdb treats the entire list as a single string, resulting in an SQL syntax error or unintended search results.

The correct procedure is as follows:

1. Prepare as many %s as there are elements in the list (e.g., %s, %s, %s).
2. Embed that string into the SQL statement.
3. Pass the expanded (flattened) list as the second argument to the execute method.

4. SQL Injection Prevention

Embedding numerical values from search conditions directly into the SQL string like f"id > {min_post_id}" poses a security risk. Always use %s placeholders and pass the values as the second argument to the execute method to ensure the driver handles escape processing safely.