Connecting Python to MySQL: `mysql-connector`

🐍 Connecting Python to MySQL with mysql-connector-python

Establishing a connection between Python applications and MySQL databases is a common requirement for data-driven projects. The mysql-connector-python library, developed by Oracle, provides a native pure Python solution for this interaction.

Prerequisites

• MySQL Server: A running MySQL server instance (local or remote) with accessible credentials.
• Python 3: Ensure Python 3 is installed on your system.
• Database and User: A MySQL database created and a user with appropriate permissions to access it.

Installation: Resolving "Module Not Found"

The most frequent cause of a ModuleNotFoundError despite "importing" is an incorrect installation or environmental issue.

• Install the Connector:
The mysql-connector-python library needs to be installed in your Python environment. Use pip:

pip install mysql-connector-python

pip install mysql-connector-python[protobuf]

• Virtual Environments (Crucial for "Module Not Found"):
If you're using virtual environments (highly recommended), ensure you activate the correct environment before running pip install. If you install globally and try to run your script in an inactive virtual environment, the module won't be found there.

# Create a virtual environment (if not already done)
    python -m venv myenv

    # Activate the virtual environment
    # On Windows:
    # myenv\Scripts\activate
    # On macOS/Linux:
    # source myenv/bin/activate

    # Now install the connector within the active virtual environment
    pip install mysql-connector-python

    # Verify installation
    pip show mysql-connector-python

Basic Connection

Once installed, connecting to your MySQL database involves importing mysql.connector and using its connect() method.

import mysql.connector
import os # For environment variables

# Database credentials (use environment variables for security in production)
DB_HOST = os.environ.get("MYSQL_HOST", "localhost")
DB_USER = os.environ.get("MYSQL_USER", "root")
DB_PASSWORD = os.environ.get("MYSQL_PASSWORD", "your_password") # CHANGE THIS!
DB_NAME = os.environ.get("MYSQL_DB_NAME", "your_database")

try:
    # Establish the connection
    cnx = mysql.connector.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME
    )

    if cnx.is_connected():
        print(f"Successfully connected to MySQL database: {DB_NAME}")
        # Perform operations here
        # ...

except mysql.connector.Error as err:
    if err.errno == mysql.connector.errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == mysql.connector.errorcode.ER_BAD_DB_ERROR:
        print(f"Database {DB_NAME} does not exist")
    else:
        print(err)
finally:
    if 'cnx' in locals() and cnx.is_connected():
        cnx.close()
        print("MySQL connection closed.")

Performing Database Operations (CRUD)

All database operations are performed using a cursor object, which is created from the connection.

1. Creating a Table

# ... (connection setup from above) ...
try:
    cnx = mysql.connector.connect(
        host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME
    )
    cursor = cnx.cursor()

    create_table_query = """
    CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price DECIMAL(10, 2) NOT NULL
    )
    """
    cursor.execute(create_table_query)
    cnx.commit() # Commit changes for DDL (Data Definition Language)
    print("Table 'products' created or already exists.")

except mysql.connector.Error as err:
    print(f"Error creating table: {err}")
finally:
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'cnx' in locals() and cnx.is_connected():
        cnx.close()

2. Inserting Data

Always use parameterized queries (%s placeholders) to prevent SQL injection.

# ... (connection setup) ...
try:
    cnx = mysql.connector.connect(
        host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME
    )
    cursor = cnx.cursor()

    # Single insert
    insert_query = "INSERT INTO products (name, price) VALUES (%s, %s)"
    product_data = ("Laptop", 1200.50)
    cursor.execute(insert_query, product_data)
    cnx.commit()
    print(f"Inserted 1 product. Last inserted ID: {cursor.lastrowid}")

    # Multiple inserts (executemany)
    products_to_add = [
        ("Mouse", 25.00),
        ("Keyboard", 75.99),
        ("Monitor", 300.00)
    ]
    cursor.executemany(insert_query, products_to_add)
    cnx.commit()
    print(f"Inserted {cursor.rowcount} more products.")

except mysql.connector.Error as err:
    print(f"Error inserting data: {err}")
finally:
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'cnx' in locals() and cnx.is_connected():
        cnx.close()

3. Reading Data

# ... (connection setup) ...
try:
    cnx = mysql.connector.connect(
        host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME
    )
    cursor = cnx.cursor(dictionary=True) # Use dictionary=True to get results as dicts

    # Select all products
    select_all_query = "SELECT id, name, price FROM products"
    cursor.execute(select_all_query)
    products = cursor.fetchall()

    print("\nAll Products:")
    for product in products:
        print(f"ID: {product['id']}, Name: {product['name']}, Price: {product['price']}")

    # Select a specific product by ID
    product_id = 2
    select_one_query = "SELECT id, name, price FROM products WHERE id = %s"
    cursor.execute(select_one_query, (product_id,)) # Tuple for single parameter
    single_product = cursor.fetchone()

    if single_product:
        print(f"\nProduct with ID {product_id}:")
        print(f"ID: {single_product['id']}, Name: {single_product['name']}, Price: {single_product['price']}")
    else:
        print(f"\nProduct with ID {product_id} not found.")

except mysql.connector.Error as err:
    print(f"Error reading data: {err}")
finally:
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'cnx' in locals() and cnx.is_connected():
        cnx.close()

4. Updating Data

# ... (connection setup) ...
try:
    cnx = mysql.connector.connect(
        host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME
    )
    cursor = cnx.cursor()

    update_query = "UPDATE products SET price = %s WHERE name = %s"
    new_price = 1250.00
    product_name_to_update = "Laptop"
    cursor.execute(update_query, (new_price, product_name_to_update))
    cnx.commit()
    print(f"Updated {cursor.rowcount} product(s).")

except mysql.connector.Error as err:
    print(f"Error updating data: {err}")
finally:
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'cnx' in locals() and cnx.is_connected():
        cnx.close()

5. Deleting Data

# ... (connection setup) ...
try:
    cnx = mysql.connector.connect(
        host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME
    )
    cursor = cnx.cursor()

    delete_query = "DELETE FROM products WHERE name = %s"
    product_name_to_delete = "Mouse"
    cursor.execute(delete_query, (product_name_to_delete,))
    cnx.commit()
    print(f"Deleted {cursor.rowcount} product(s).")

except mysql.connector.Error as err:
    print(f"Error deleting data: {err}")
finally:
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'cnx' in locals() and cnx.is_connected():
        cnx.close()

Best Practices

• with statement for connections/cursors:

import mysql.connector
    # ... credentials ...

    try:
        with mysql.connector.connect(
            host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME
        ) as cnx:
            with cnx.cursor() as cursor:
                # Your operations here
                cursor.execute("SELECT 1")
                result = cursor.fetchone()
                print(f"Connection active: {result}")
            cnx.commit() # Don't forget to commit
    except mysql.connector.Error as err:
        print(f"Database error: {err}")

• Environment Variables: Always use environment variables for sensitive credentials (passwords, usernames) instead of hardcoding them in your script.
• Parameterized Queries: Always use %s placeholders for dynamic values in SQL queries to prevent SQL injection vulnerabilities.
• Error Handling: Implement robust try...except blocks to catch and handle mysql.connector.Error exceptions.
• Transactions: Use cnx.commit() to save changes (INSERT, UPDATE, DELETE) and cnx.rollback() to undo changes if an error occurs within a transaction.

Troubleshooting ModuleNotFoundError

If you encounter ModuleNotFoundError: No module named 'mysql.connector' after performing the installation steps:

• Re-verify pip install: Run pip install mysql-connector-python again. Check the output for any errors.
• Activate Virtual Environment: If you're using a virtual environment, ensure it's activated (source myenv/bin/activate or myenv\Scripts\activate) before running your script.
• Check Python Interpreter:
Command Line: Ensure you're running your script with the correct Python interpreter. Use python your_script.py (or python3 your_script.py) inside your activated virtual environment.
IDE (e.g., VS Code, PyCharm): Verify that your IDE is configured to use the Python interpreter associated with your virtual environment where the module was installed.
• pip list: Run pip list in your active environment to confirm mysql-connector-python appears in the list.
• Conflicting Installations: Occasionally, multiple Python installations or conflicting packages can cause issues. Try installing in a fresh virtual environment.

#python #mysql #database #connection #mysqlconnector #automation #programming #troubleshooting #sql