MySQL - Python Syntax

Modern Python and MySQL Integration

Connecting your Python applications to a MySQL database is a fundamental skill for any developer. This is achieved using a ‘connector’ or ‘driver’, which is a specialized library that implements Python’s standard Database API Specification v2.0 (DB-API 2.0). This API ensures that interacting with different databases from Python feels consistent and predictable.

Setting Up Your Development Environment

Before writing code, it’s crucial to set up a clean and isolated development environment. This prevents conflicts between project dependencies.

Step 1: Create a Virtual Environment

Using a virtual environment is a non-negotiable best practice. Open your terminal in your project folder and run:

# For macOS/Linux
python3 -m venv venv
source venv/bin/activate

# For Windows
python -m venv venv
.\venv\Scripts\activate

You’ll see (venv) prepended to your command prompt, indicating the virtual environment is active.

Step 2: Install the MySQL Connector

The official connector from Oracle is mysql-connector-python. Install it using pip:

pip install mysql-connector-python

Alternative: Many developers prefer PyMySQL (pip install PyMySQL) as it’s a pure Python implementation and can be easier to install in some environments. The API is nearly identical, making it easy to switch.

The Database Interaction Lifecycle

Interacting with a database in Python follows a clear pattern:

1. Connect: Establish a connection to the MySQL server.
2. Cursor: Create a cursor object from the connection. A cursor is like a controller that executes queries and fetches results.
3. Execute: Use the cursor to execute SQL queries, using placeholders for data to prevent SQL injection.
4. Fetch: Retrieve the results from the executed query if it was a SELECT statement.
5. Commit/Rollback: If you made changes (INSERT, UPDATE, DELETE), you must commit() them to save them permanently. If an error occurs, rollback() to undo changes in the current transaction.
6. Close: Close the cursor and the connection to release database resources.

Key Functions

Function	Description
connect()	Establishes a connection to the MySQL server. Takes host, user, password, etc., as arguments.
connection.cursor()	Creates a cursor object. You can create multiple cursors from one connection.
cursor.execute(query, params)	Executes an SQL query. The params argument (a tuple or list) is used for safe parameter substitution.
cursor.fetchone()	Fetches the next single row from a result set as a tuple.
cursor.fetchall()	Fetches all remaining rows from a result set as a list of tuples.
connection.commit()	Saves all data changes made since the last commit.
connection.rollback()	Discards all data changes made since the last commit.
cursor.close() / connection.close()	Closes the cursor and connection. It’s crucial to do this to free up server resources.

Complete CRUD Example with Best Practices

This example demonstrates a full Create, Read, Update, and Delete (CRUD) cycle, incorporating essential best practices like secure connection handling and parameterized queries.

Security Alert: Never hard-code credentials (username, password) in your source code. Use environment variables or a secure configuration system. This example uses os.getenv() as a demonstration.

import mysql.connector
from mysql.connector import errorcode
import os

# --- Configuration ---
def get_db_connection():
    """Creates and returns a database connection."""
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', 'password'),
            database='your_database' # Replace with your DB name
        )
        return connection
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)
        return None

# --- Main Application Logic ---
def main():
    """Runs the main CRUD operations."""
    connection = get_db_connection()
    if not connection:
        return

    cursor = connection.cursor()

    try:
        # 1. CREATE: Set up a table
        print("\n--- Creating 'products' table ---")
        cursor.execute("DROP TABLE IF EXISTS products")
        cursor.execute("""
            CREATE TABLE products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                price DECIMAL(10, 2) NOT NULL
            )
        """)
        print("Table 'products' created successfully.")

        # 2. INSERT: Add new data using parameterized queries
        print("\n--- Inserting new products ---")
        new_products = [
            ('Laptop', 1200.50),
            ('Mouse', 25.00),
            ('Keyboard', 75.99)
        ]
        insert_query = "INSERT INTO products (name, price) VALUES (%s, %s)"
        cursor.executemany(insert_query, new_products)
        connection.commit() # Commit the inserts
        print(f"{cursor.rowcount} products inserted.")

        # 3. READ: Select and display data
        print("\n--- Reading all products ---")
        cursor.execute("SELECT id, name, price FROM products")
        for (id, name, price) in cursor:
            print(f"ID: {id}, Name: {name}, Price: ${price:.2f}")

        # 4. UPDATE: Modify an existing record
        print("\n--- Updating product price ---")
        update_query = "UPDATE products SET price = %s WHERE name = %s"
        cursor.execute(update_query, (29.99, 'Mouse'))
        connection.commit() # Commit the update
        print("Mouse price updated.")

        # 5. DELETE: Remove a record
        print("\n--- Deleting a product ---")
        delete_query = "DELETE FROM products WHERE name = %s"
        cursor.execute(delete_query, ('Keyboard',))
        connection.commit() # Commit the delete
        print("Keyboard deleted.")

        # Verify final state
        print("\n--- Final product list ---")
        cursor.execute("SELECT id, name, price FROM products")
        rows = cursor.fetchall()
        for row in rows:
            print(row)

    except mysql.connector.Error as err:
        print(f"An error occurred: {err}")
        connection.rollback() # Rollback changes on error
    finally:
        # 6. CLOSE: Always close cursor and connection
        print("\n--- Closing resources ---")
        cursor.close()
        connection.close()
        print("Connection closed.")

if __name__ == '__main__':
    main()