Of course! Let's dive into PyMySQL, the most popular and recommended library for connecting to a MySQL database from Python.

What is PyMySQL?

PyMySQL is a pure-Python MySQL client library. It means it's written entirely in Python, making it easy to install and use without needing any external C libraries or MySQL client binaries. It's a standard-compliant MySQL driver that implements the Python Database API Specification v2.0 (DB-API 2.0).

Installation

First, you need to install the library. The easiest way is using pip.

pip install PyMySQL

Basic Connection

To connect to a MySQL server, you need the following information:

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

Here's how to establish a connection:

import pymysql
# --- Connection Details ---
# Replace with your actual database credentials
db_config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',  # Optional, can be omitted
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor  # This is very useful!
}
try:
    # --- Establish the connection ---
    connection = pymysql.connect(**db_config)
    print("Successfully connected to the database!")
except pymysql.MySQLError as e:
    print(f"Error connecting to MySQL: {e}")
finally:
    # --- Close the connection ---
    # The connection should always be closed, even if an error occurs.
    if 'connection' in locals() and connection.open:
        connection.close()
        print("Connection closed.")

Important: The cursorclass=pymysql.cursors.DictCursor argument is a highly recommended feature. It makes your results return as Python dictionaries instead of tuples, which makes the code much more readable because you can access columns by name (e.g., row['name']) instead of by index (e.g., row[1]).

Performing CRUD Operations

Once connected, you use a cursor to execute SQL commands.

a) Creating Data (INSERT)

You should always use parameterized queries to prevent SQL injection attacks.

import pymysql
db_config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}
try:
    connection = pymysql.connect(**db_config)
    with connection.cursor() as cursor:
        # --- SQL query with placeholders (%s) ---
        # NEVER use string formatting (f-strings or +) to insert user data directly.
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        # --- Data to be inserted ---
        # Note the comma in (data,) to make it a tuple
        data = ('user@example.com', 'hashed_password_123')
        # --- Execute the query ---
        cursor.execute(sql, data)
    # --- Commit the transaction ---
    # Without this, the changes will not be saved to the database.
    connection.commit()
    print(f"Successfully inserted 1 record. Last inserted ID: {cursor.lastrowid}")
except pymysql.MySQLError as e:
    print(f"Error: {e}")
    # --- Rollback the transaction in case of error ---
    if 'connection' in locals() and connection.open:
        connection.rollback()
finally:
    if 'connection' in locals() and connection.open:
        connection.close()

b) Reading Data (SELECT)

import pymysql
db_config = { ... } # Same as before
try:
    connection = pymysql.connect(**db_config)
    with connection.cursor() as cursor:
        # --- SQL query to select all users ---
        sql = "SELECT `id`, `email` FROM `users`"
        # --- Execute the query ---
        cursor.execute(sql)
        # --- Fetch all results ---
        # fetchone() gets the next single row.
        # fetchmany(size) gets the next 'size' rows.
        results = cursor.fetchall()
        print("Found users:")
        for row in results:
            print(f"  ID: {row['id']}, Email: {row['email']}")
except pymysql.MySQLError as e:
    print(f"Error: {e}")
finally:
    if 'connection' in locals() and connection.open:
        connection.close()

c) Updating Data (UPDATE)

The pattern is very similar to INSERT.

import pymysql
db_config = { ... } # Same as before
try:
    connection = pymysql.connect(**db_config)
    with connection.cursor() as cursor:
        # --- SQL query to update a user's email ---
        sql = "UPDATE `users` SET `email` = %s WHERE `id` = %s"
        # --- Data for the update ---
        data = ('new_email@example.com', 1) # Update user with id=1
        # --- Execute and commit ---
        cursor.execute(sql, data)
        connection.commit()
        print(f"Successfully updated {cursor.rowcount} record(s).")
except pymysql.MySQLError as e:
    print(f"Error: {e}")
    if 'connection' in locals() and connection.open:
        connection.rollback()
finally:
    if 'connection' in locals() and connection.open:
        connection.close()

d) Deleting Data (DELETE)

The pattern is similar to UPDATE.

import pymysql
db_config = { ... } # Same as before
try:
    connection = pymysql.connect(**db_config)
    with connection.cursor() as cursor:
        # --- SQL query to delete a user ---
        sql = "DELETE FROM `users` WHERE `id` = %s"
        # --- Data for the delete ---
        data = (1,) # Delete user with id=1. Note the comma for the tuple.
        # --- Execute and commit ---
        cursor.execute(sql, data)
        connection.commit()
        print(f"Successfully deleted {cursor.rowcount} record(s).")
except pymysql.MySQLError as e:
    print(f"Error: {e}")
    if 'connection' in locals() and connection.open:
        connection.rollback()
finally:
    if 'connection' in locals() and connection.open:
        connection.close()

Best Practices: Using Context Managers

Manually calling connection.close() can be error-prone. A better way is to use a with statement, which automatically handles closing the connection for you.

import pymysql
db_config = { ... } # Your config
# The 'with' statement will automatically close the connection
with pymysql.connect(**db_config) as connection:
    with connection.cursor() as cursor:
        sql = "SELECT * FROM `users`"
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:
            print(row)
# No need to call connection.close() here, it's done automatically!

Alternatives to PyMySQL

While PyMySQL is excellent, other libraries are also popular, each with its own strengths.

Summary

For any new Python project that needs to connect to MySQL, PyMySQL is a fantastic choice. It's modern, secure (when used with parameterized queries), and integrates seamlessly with Python's with statement for clean and safe resource management.

Key Takeaways:

1. Install: pip install PyMySQL
2. Connect: Use pymysql.connect() with your credentials.
3. Use a Cursor: Get a cursor with connection.cursor() to execute queries.
4. Always Commit: Call connection.commit() after INSERT, UPDATE, or DELETE to save changes.
5. Use with Statements: They handle closing connections and cursors automatically.
6. Prevent SQL Injection: Always use %s placeholders and pass data as a second argument to cursor.execute().