Of course. Using Python 2.7 and MySQLDB is a common task for maintaining legacy systems. Here's a comprehensive guide covering installation, usage, best practices, and important considerations for Python 2.7.

What is MySQLdb?

MySQLdb is a Python DB-API 2.0 compliant interface for MySQL. It's one of the original and most popular libraries for connecting Python to MySQL, especially in the Python 2.x era.

Key Points:

• Python 2.7 Focused: It was primarily developed for Python 2. It does not work with Python 3.
• C Extension: It's a C extension module, which means it's generally faster than pure Python alternatives.
• API: It implements the Python Database API Specification v2.0 (DB-API 2.0), which is a standard for database access libraries in Python.

Installation

Since MySQLdb is a C extension, you need a C compiler and the necessary MySQL development libraries on your system before you can install it.

Prerequisites (Linux / macOS)

You'll need to install the MySQL development headers and libraries. The package name varies by distribution.

For Debian / Ubuntu:

sudo apt-get update
sudo apt-get install python-dev libmysqlclient-dev

• python-dev: Provides the C header files for Python.
• libmysqlclient-dev: Provides the MySQL client library and header files.

For Red Hat / CentOS / Fedora:

sudo yum install python-devel mysql-devel
# Or on newer systems with dnf:
sudo dnf install python-devel mysql-devel

For macOS (using Homebrew):

brew install mysql

Homebrew usually handles the dependencies automatically.

Installing MySQLdb

Once the prerequisites are installed, you can use pip (Python's package installer) to install MySQLdb.

pip install MySQL-python

Note: The package name on PyPI is MySQL-python, not MySQLdb. This is a common point of confusion.

Basic Usage Example

Here is a simple, complete example of how to connect to a MySQL database, execute a query, and fetch the results.

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
import sys
# --- Database Connection Details ---
# Replace with your actual database credentials
DB_HOST = 'localhost'
DB_USER = 'your_username'
DB_PASS = 'your_password'
DB_NAME = 'your_database'
try:
    # 1. Establish a connection to the database
    # The connection object is the gateway to the database
    conn = MySQLdb.connect(DB_HOST, DB_USER, DB_PASS, DB_NAME)
    # 2. Create a cursor object
    # A cursor is used to execute SQL commands and fetch results
    cursor = conn.cursor()
    # 3. Execute a SQL query
    # We use a placeholder %s to prevent SQL injection
    sql_query = "SELECT id, name, email FROM users WHERE is_active = %s"
    cursor.execute(sql_query, (1,)) # Pass a tuple as the second argument
    # 4. Fetch the results
    # fetchone() gets the next row of a query result set.
    # fetchall() retrieves all (remaining) rows of a query result.
    print "--- Fetching one row at a time ---"
    while True:
        row = cursor.fetchone()
        if row is None:
            break
        print "ID: %s, Name: %s, Email: %s" % row
    # 5. Execute another query (e.g., an INSERT statement)
    print "\n--- Inserting a new user ---"
    insert_query = "INSERT INTO users (name, email, is_active) VALUES (%s, %s, %s)"
    new_user_data = ('Jane Doe', 'jane.doe@example.com', 1)
    # Execute the insert statement
    cursor.execute(insert_query, new_user_data)
    # IMPORTANT: Commit the transaction to save the changes to the database
    conn.commit()
    print "Successfully inserted user with ID: %s" % cursor.lastrowid
except MySQLdb.Error as e:
    # If an error occurs, roll back any potential changes
    print "Error %d: %s" % (e.args[0], e.args[1])
    if 'conn' in locals():
        conn.rollback()
finally:
    # 6. Close the cursor and connection
    # This is crucial to free up resources
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()
    print "\nDatabase connection closed."

Best Practices and Important Considerations

A. Preventing SQL Injection

NEVER use Python's string formatting () or .format() to insert variables directly into a SQL query. This is a massive security risk.

WRONG (Vulnerable to SQL Injection):

user_id = 123
sql = "SELECT * FROM users WHERE id = %s" % user_id # DANGEROUS!
# or
sql = "SELECT * FROM users WHERE id = {}".format(user_id) # ALSO DANGEROUS!
cursor.execute(sql)

RIGHT (Safe): MySQLdb uses a parameter substitution mechanism. Pass the values as a tuple to the execute() method. The library will safely escape them.

user_id = 123
sql = "SELECT * FROM users WHERE id = %s" # Note the placeholder is %s
cursor.execute(sql, (user_id,)) # Pass values as a tuple

B. Handling Transactions

By default, MySQLdb operates in auto-commit mode. This means every INSERT, UPDATE, or DELETE statement is immediately committed to the database. For many applications, you want to group multiple operations into a single transaction.

To do this, turn off auto-commit and manually commit when your logic is complete.

conn = MySQLdb.connect(...)
conn.autocommit(False) # Turn off auto-commit
try:
    cursor = conn.cursor()
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    conn.commit() # Commit both changes together
    print "Transaction successful."
except MySQLdb.Error as e:
    conn.rollback() # Roll back if any error occurs
    print "Transaction failed. Rolled back."
finally:
    cursor.close()
    conn.close()

C. Character Sets and Unicode (Very Important in Python 2.7)

Python 2.7 has a strong distinction between str (bytes) and unicode (text). MySQL stores data in a character set (like utf8 or latin1). You must handle this correctly to avoid UnicodeDecodeError.

The best practice is to:

1. Set the charset in the connection string.
2. Ensure your MySQL database and tables use utf8.
3. When fetching data, decode it to unicode if it contains non-ASCII characters.

conn = MySQLdb.connect(
    host='localhost',
    user='user',
    passwd='password',
    db='db',
    charset='utf8mb4', # Use utf8mb4 to support full Unicode, including emojis
    use_unicode=True   # This makes MySQLdb return strings as unicode objects
)
cursor = conn.cursor()
# When fetching, you get unicode objects
cursor.execute("SELECT name FROM users")
for row in cursor.fetchall():
    user_name = row[0]
    print type(user_name) # <type 'unicode'>
    print user_name
# When inserting, you should pass unicode strings
new_name = u'José María' # The 'u' prefix makes it a unicode string
cursor.execute("INSERT INTO users (name) VALUES (%s)", (new_name,))
conn.commit()

Alternatives to MySQLdb for Python 2.7

While MySQLdb is great, it's no longer actively maintained. For new projects or even existing ones, you might consider more modern alternatives that are still compatible with Python 2.7.

mysql-connector-python

Oracle's official MySQL driver. It's pure Python, so it's slower than MySQLdb but easier to install (no C compiler needed) and actively maintained.

Installation:

pip install mysql-connector-python

Usage: The API is very similar to DB-API 2.0.

import mysql.connector
conn = mysql.connector.connect(
    host='localhost',
    user='user',
    password='password',
    database='db'
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
print cursor.fetchall()
conn.close()

PyMySQL

Another excellent pure-Python library. It's lightweight and a popular choice. It also supports Python 3, making it a good option if you plan to migrate later.

Installation:

pip install PyMySQL

Usage:

import pymysql
conn = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='db',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor # Returns rows as dictionaries
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
print cursor.fetchall()
conn.close()

Summary: Which to Use?

For maintaining an existing Python 2.7 application, stick with MySQLdb. For any new development, even if it's in Python 2.7, it's wise to choose a more modern and actively maintained library like mysql-connector-python or PyMySQL.