Of course! Executing SQL commands in Python using the MySQL library is a fundamental task. The most common and recommended library for this is mysql-connector-python, which is the official driver from Oracle.

Here’s a comprehensive guide covering the essentials, from basic setup to best practices.

Step 1: Install the MySQL Connector

First, you need to install the library. Open your terminal or command prompt and run:

pip install mysql-connector-python

Step 2: Basic Structure of an Execution Script

Every database operation in Python follows a general pattern:

1. Import the library.
2. Establish a connection to the database.
3. Create a cursor object. This is what you use to execute commands.
4. Execute your SQL query.
5. Fetch the results (if it was a SELECT query).
6. Commit the transaction (if it was an INSERT, UPDATE, or DELETE query).
7. Close the cursor and the connection.

Let's break this down with code examples.

Example 1: Executing a SELECT Query (Reading Data)

This is the most common operation. We'll query a table and print the results.

Setup: Create a Sample Table

First, let's create a table in your MySQL database. You can run this SQL directly in your MySQL client (like MySQL Workbench or DBeaver).

CREATE DATABASE IF NOT EXISTS my_company;
USE my_company;
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Engineering', 80000.00),
('Jane', 'Smith', 'Engineering', 95000.00),
('Peter', 'Jones', 'Marketing', 60000.00),
('Mary', 'Williams', 'Sales', 75000.00);

Python Code to Query the Table

import mysql.connector
from mysql.connector import Error
def get_employees_by_department(department_name):
    """
    Connects to the MySQL database and retrieves all employees from a specific department.
    """
    connection = None  # Initialize connection to None
    cursor = None      # Initialize cursor to None
    try:
        # 1. Establish a connection
        # Replace with your actual database credentials
        connection = mysql.connector.connect(
            host='localhost',
            database='my_company',
            user='your_username',
            password='your_password'
        )
        if connection.is_connected():
            db_info = connection.get_server_info()
            print(f"Successfully connected to MySQL Server version {db_info}")
            # 2. Create a cursor
            # A dictionary cursor is useful because it lets you access columns by name
            cursor = connection.cursor(dictionary=True)
            # 3. Execute the query
            # Use a placeholder %s to prevent SQL injection
            sql_query = "SELECT * FROM employees WHERE department = %s"
            cursor.execute(sql_query, (department_name,))
            # 4. Fetch the results
            records = cursor.fetchall()
            print(f"\nFound {cursor.rowcount} employees in the '{department_name}' department:")
            for record in records:
                print(record)
    except Error as e:
        print(f"Error while connecting to MySQL or executing query: {e}")
    finally:
        # 5. Clean up resources
        if connection and connection.is_connected():
            if cursor:
                cursor.close()
            connection.close()
            print("\nMySQL connection is closed.")
# --- Run the function ---
get_employees_by_department('Engineering')

Expected Output:

Successfully connected to MySQL Server version 8.0.33
Found 2 employees in the 'Engineering' department:
{'id': 1, 'first_name': 'John', 'last_name': 'Doe', 'department': 'Engineering', 'salary': Decimal('80000.00')}
{'id': 2, 'first_name': 'Jane', 'last_name': 'Smith', 'department': 'Engineering', 'salary': Decimal('95000.00')}
MySQL connection is closed.

Example 2: Executing INSERT, UPDATE, DELETE (Modifying Data)

For queries that change data, you must call connection.commit() to save the changes to the database. The changes are only in a "transaction" until you commit.

Important: Using Parameters to Prevent SQL Injection

Never use Python string formatting (like f"..." or ) to insert values directly into a query. This is a major security risk called SQL Injection. Always use the %s placeholder syntax, as shown below.

import mysql.connector
from mysql.connector import Error
def add_employee(first_name, last_name, department, salary):
    """Inserts a new employee into the database."""
    connection = None
    cursor = None
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='my_company',
            user='your_username',
            password='your_password'
        )
        cursor = connection.cursor()
        # The SQL query with placeholders
        sql_insert_query = "INSERT INTO employees (first_name, last_name, department, salary) VALUES (%s, %s, %s, %s)"
        # The data to be inserted, as a tuple
        employee_data = (first_name, last_name, department, salary)
        # Execute the query
        cursor.execute(sql_insert_query, employee_data)
        # The crucial step: Commit the transaction to save the changes
        connection.commit()
        print(f"{cursor.rowcount} record inserted successfully into employees table.")
    except Error as e:
        print(f"Failed to insert record into table: {e}")
        # Optionally, you can roll back the transaction if something goes wrong
        # if connection:
        #     connection.rollback()
    finally:
        if connection and connection.is_connected():
            if cursor:
                cursor.close()
            connection.close()
            print("MySQL connection is closed.")
# --- Run the function ---
add_employee('Susan', 'Miller', 'HR', 65000.00)

Best Practices: Using with Statements

Manually closing cursors and connections can be tedious and error-prone. A better way is to use the mysql.connector.connection.MySQLConnection object as a context manager (with statement). It automatically handles closing the connection when the block is exited.

import mysql.connector
from mysql.connector import Error
def update_employee_salary(employee_id, new_salary):
    """Updates the salary of a specific employee."""
    try:
        # The 'with' statement handles connection closing automatically
        with mysql.connector.connect(
            host='localhost',
            database='my_company',
            user='your_username',
            password='your_password'
        ) as connection:
            with connection.cursor() as cursor:
                sql_update_query = "UPDATE employees SET salary = %s WHERE id = %s"
                data_to_update = (new_salary, employee_id)
                cursor.execute(sql_update_query, data_to_update)
                connection.commit() # Still need to commit for DML statements
                print(f"{cursor.rowcount} record(s) updated successfully.")
    except Error as e:
        print(f"Error updating employee salary: {e}")
# --- Run the function ---
update_employee_salary(1, 85000.00)

Advanced: Executing Multiple Statements at Once (executemany)

If you need to insert many rows, using a loop is inefficient. The executemany() method is designed for this. It executes the same query multiple times with different parameters.

import mysql.connector
from mysql.connector import Error
def add_multiple_employees(employee_list):
    """Inserts a list of employees into the database efficiently."""
    try:
        with mysql.connector.connect(
            host='localhost',
            database='my_company',
            user='your_username',
            password='your_password'
        ) as connection:
            with connection.cursor() as cursor:
                sql_insert_query = "INSERT INTO employees (first_name, last_name, department, salary) VALUES (%s, %s, %s, %s)"
                # executemany takes the query and a list of tuples
                cursor.executemany(sql_insert_query, employee_list)
                connection.commit()
                print(f"{cursor.rowcount} records inserted successfully.")
    except Error as e:
        print(f"Failed to insert multiple records: {e}")
# --- Run the function ---
new_employees = [
    ('David', 'Brown', 'Engineering', 110000.00),
    ('Emily', 'Davis', 'Marketing', 62000.00),
    ('Frank', 'Wilson', 'Sales', 78000.00)
]
add_multiple_employees(new_employees)

Summary of Key Methods