Of course! Here is a comprehensive guide to querying a MySQL database using Python with the most common libraries.

We'll cover two primary libraries:

1. mysql-connector-python (Official Oracle Driver): This is the modern, officially supported driver from Oracle. It's a great choice for new projects.
2. PyMySQL (Third-Party Driver): A lightweight and popular pure-Python implementation of the MySQL client-server protocol. It's very easy to install and use.

Step 1: Installation

First, you need to install the library you want to use. Open your terminal or command prompt and run one of the following commands:

For the official mysql-connector-python:

pip install mysql-connector-python

For PyMySQL:

pip install pymysql

Step 2: Database Connection

Before you can query, you need to establish a connection to your MySQL server. You'll need the following information:

• Hostname (e.g., localhost or an IP address)
• Username
• Password
• Database name (optional, you can connect without it and switch later)

Example with mysql-connector-python

import mysql.connector
from mysql.connector import Error
try:
    # Establish the connection
    connection = mysql.connector.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database_name'
    )
    if connection.is_connected():
        db_info = connection.get_server_info()
        print(f"Successfully 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}")
finally:
    # Closing the connection
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed.")

Example with PyMySQL

The connection logic is very similar.

import pymysql
try:
    # Establish the connection
    connection = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database_name',
        cursorclass=pymysql.cursors.DictCursor  # This is optional but useful
    )
    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `version()`"
        cursor.execute(sql)
        result = cursor.fetchone()
        print(f"Server version: {result['version()']}")
except Error as e:
    print(f"Error while connecting to MySQL: {e}")
finally:
    # Closing the connection
    if 'connection' in locals() and connection.open:
        connection.close()
        print("MySQL connection is closed.")

Step 3: Executing Queries (The Core)

Once connected, you can execute queries using a cursor. The process is generally the same for both libraries:

1. Create a cursor object.
2. Define your SQL query as a string.
3. Use cursor.execute() to run the query.
4. For SELECT queries, use cursor.fetchall() to get all results, or cursor.fetchone() to get the first one.
5. For INSERT, UPDATE, or DELETE, use connection.commit() to save the changes.

Selecting Data (Read)

This is the most common operation. You fetch data and then process it.

# Assuming 'connection' is already established and open
try:
    with connection.cursor() as cursor:
        # The SQL query with a placeholder for the variable
        sql_query = "SELECT id, name, email FROM users WHERE is_active = %s"
        active_status = 1
        # Execute the query with the parameter
        cursor.execute(sql_query, (active_status,))
        # Fetch all the records
        results = cursor.fetchall()
        print(f"Found {len(results)} active users:")
        for row in results:
            # Note: Using DictCursor (from PyMySQL example) lets you access by column name
            # Otherwise, you'd use row[0], row[1], etc.
            print(f"  ID: {row['id']}, Name: {row['name']}, Email: {row['email']}")
except Error as e:
    print(f"Error executing query: {e}")

Security Note: Notice the use of %s as a placeholder and passing the variable in a tuple (active_status,). This is parametrized querying and is the standard way to prevent SQL injection attacks. Never use f-strings or string formatting to insert variables directly into your SQL query.

Inserting Data (Create)

When you modify data, you must call connection.commit() to make the changes permanent.

# Assuming 'connection' is already established and open
try:
    with connection.cursor() as cursor:
        # SQL query for inserting a new record
        sql_insert = "INSERT INTO users (name, email, is_active) VALUES (%s, %s, %s)"
        new_user_data = ('Jane Doe', 'jane.doe@example.com', 1)
        # Execute the query
        cursor.execute(sql_insert, new_user_data)
        # Get the ID of the last inserted row
        user_id = cursor.lastrowid
        print(f"Successfully inserted new user with ID: {user_id}")
        # Commit the transaction to save the changes
        connection.commit()
except Error as e:
    print(f"Error inserting data: {e}")
    # Optionally, rollback the transaction if something went wrong
    connection.rollback()

Updating Data (Update)

The process is similar to inserting.

# Assuming 'connection' is already established and open
try:
    with connection.cursor() as cursor:
        # SQL query for updating a record
        sql_update = "UPDATE users SET email = %s WHERE id = %s"
        update_data = ('jane.d.updated@example.com', 2) # Assuming user with id=2 exists
        cursor.execute(sql_update, update_data)
        # Check how many rows were affected
        print(f"{cursor.rowcount} record(s) updated.")
        # Commit the transaction
        connection.commit()
except Error as e:
    print(f"Error updating data: {e}")
    connection.rollback()

Deleting Data (Delete)

Use with extreme caution!

# Assuming 'connection' is already established and open
try:
    with connection.cursor() as cursor:
        # SQL query for deleting a record
        sql_delete = "DELETE FROM users WHERE id = %s"
        user_to_delete = 3 # Assuming user with id=3 exists
        cursor.execute(sql_delete, (user_to_delete,))
        print(f"{cursor.rowcount} record(s) deleted.")
        # Commit the transaction
        connection.commit()
except Error as e:
    print(f"Error deleting data: {e}")
    connection.rollback()

Best Practice: Using Context Managers (with statement)

The with statement is highly recommended. It ensures that your database resources (like the cursor) are automatically closed, even if errors occur. The pymysql.connect() call itself can be used as a context manager to handle connection closing.

Here is a complete, clean example using PyMySQL and context managers:

import pymysql
import pymysql.cursors
# Database configuration
db_config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database_name',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}
def get_all_users():
    """Connects to the database and retrieves all users."""
    try:
        # The 'with' statement handles closing the connection automatically
        with pymysql.connect(**db_config) as connection:
            with connection.cursor() as cursor:
                # Read a single record
                sql = "SELECT id, name, email FROM users"
                cursor.execute(sql)
                result = cursor.fetchall()
                return result
    except Error as e:
        print(f"Error fetching users: {e}")
        return None
# --- Usage ---
users = get_all_users()
if users:
    for user in users:
        print(f"User ID: {user['id']}, Name: {user['name']}, Email: {user['email']}")

Summary: Which Library to Choose?

For most new projects, either is a great choice. `