Of course! Closing a MySQL database connection in Python is a crucial step for proper resource management. Here’s a detailed guide covering the most common MySQL libraries, from the classic MySQLdb to the modern standard mysql-connector-python.

The Golden Rule

Always close your database connections when you are done with them. This:

1. Frees up server resources on the MySQL server (like memory for the connection thread).
2. Releases the network socket back to the operating system.
3. Prevents your application from running out of available connections if it opens many without closing them.

Using MySQLdb (The Classic Library)

This is the library you specifically asked about. It's a wrapper around the C library libmysqlclient. It's fast but can be a bit tricky to install on some systems (especially Windows and macOS).

How to Close a Connection

You simply call the .close() method on the connection object you received from MySQLdb.connect().

Complete Example

import MySQLdb
# --- 1. Establish a connection ---
# Replace with your actual database credentials
try:
    db = MySQLdb.connect(
        host="localhost",
        user="your_username",
        passwd="your_password",
        db="your_database"
    )
    print("Connection to MySQL database successful!")
    # --- 2. Create a cursor object ---
    # A cursor is used to execute SQL commands
    cursor = db.cursor()
    # --- 3. Execute a query (optional, but good practice) ---
    cursor.execute("SELECT VERSION()")
    version = cursor.fetchone()
    print(f"Database version: {version[0]}")
except MySQLdb.Error as e:
    print(f"Error connecting to MySQL: {e}")
    # If connection fails, there's nothing to close, so we exit
    exit()
finally:
    # --- 4. Close the cursor and connection ---
    # The 'finally' block ensures this code runs whether an error occurred or not.
    if 'cursor' in locals() and cursor:
        cursor.close()
        print("Cursor closed.")
    if 'db' in locals() and db:
        db.close()
        print("Database connection closed.")

Key Points:

• db.close(): This closes the connection to the MySQL server.
• cursor.close(): It's also good practice to close the cursor to free up resources on the client side.
• try...finally: This is the recommended structure. The finally block guarantees that your cleanup code (closing the connection) will run, even if an error occurs during the try block. This prevents "leaked" connections.

Using mysql-connector-python (The Official Oracle Driver)

This is the modern, official driver from Oracle. It's pure Python, making it easier to install (pip install mysql-connector-python) and more portable.

The closing mechanism is very similar to MySQLdb.

Complete Example

import mysql.connector
from mysql.connector import Error
# --- 1. Establish a connection ---
try:
    connection = mysql.connector.connect(
        host='localhost',
        database='your_database',
        user='your_username',
        password='your_password'
    )
    if connection.is_connected():
        db_info = connection.get_server_info()
        print(f"Connected to MySQL Server version {db_info}")
        cursor = connection.cursor()
        cursor.execute("SELECT database();")
        record = cursor.fetchone()
        print(f"You're connected to database: {record[0]}")
except Error as e:
    print(f"Error while connecting to MySQL: {e}")
    exit()
finally:
    # --- 2. Close the cursor and connection ---
    if 'connection' in locals() and connection.is_connected():
        if 'cursor' in locals() and cursor:
            cursor.close()
        connection.close()
        print("MySQL connection is closed.")

Using pymysql (A Popular Pure-Python Alternative)

pymysql is another excellent, pure-Python library. It's a drop-in replacement for MySQLdb and works great with web frameworks like Flask and Django.

The syntax is identical to MySQLdb.

Complete Example

import pymysql
# --- 1. Establish a connection ---
try:
    connection = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database',
        cursorclass=pymysql.cursors.DictCursor  # Optional: fetch results as dictionaries
    )
    print("Connection successful!")
    with connection.cursor() as cursor:
        # --- 2. Execute a query ---
        sql_query = "SELECT * FROM your_table LIMIT 5;"
        cursor.execute(sql_query)
        results = cursor.fetchall()
        for row in results:
            print(row)
except pymysql.MySQLError as e:
    print(f"Error connecting to or querying MySQL: {e}")
finally:
    # --- 3. Close the connection ---
    if 'connection' in locals() and connection.open:
        connection.close()
        print("Connection closed.")

The Best Practice: Using a with Statement

All the modern libraries (mysql-connector-python, pymysql) support using the with statement. This is the cleanest and safest way to handle connections, as it automatically closes the connection for you when the block is exited, even if an error occurs.

Example with mysql-connector-python and with

import mysql.connector
from mysql.connector import Error
try:
    # The 'with' statement handles closing the connection automatically
    with mysql.connector.connect(
        host='localhost',
        database='your_database',
        user='your_username',
        password='your_password'
    ) connection:
        print("Connection established using 'with' statement.")
        with connection.cursor() as cursor:
            cursor.execute("SELECT 'Hello, World!' AS message;")
            result = cursor.fetchone()
            print(result[0]) # Output: Hello, World!
    # The connection is automatically closed here when the 'with' block ends
    print("Connection automatically closed.")
except Error as e:
    print(f"Error: {e}")

Summary and Recommendation

Final Recommendation:

• For new projects, use mysql-connector-python or pymysql.
• Always prefer the with statement if your library supports it. It's more Pythonic, less error-prone, and ensures resources are cleaned up correctly.
• If you are maintaining old code that uses MySQLdb, make sure it's wrapped in a try...finally block to guarantee the connection is closed.