MySQL Usage Guide

🗃️ Using MySQL with Django

This guide explains how to configure MySQL as the database backend in a Django project, and how to perform basic database operations using raw SQL within Django.

---

🔧 1. Install MySQL and Required Packages

Install the MySQL client library:

pip install mysqlclient

If needed:

• Ubuntu/Debian:

  sudo apt-get install python3-dev default-libmysqlclient-dev build-essential

• macOS:

  brew install mysql

---

⚙️ 2. Django Settings Configuration

In settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'your_db_name',
        'USER': 'your_mysql_user',
        'PASSWORD': 'your_mysql_password',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

---

🛠️ 3. Create a Table

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS book (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255),
            author VARCHAR(255),
            published_year INT
        );
    """)

---

📝 4. Insert Sample Data

with connection.cursor() as cursor:
    cursor.execute("""
        INSERT INTO book (title, author, published_year)
        VALUES (%s, %s, %s);
    """, ["Learning SQL", "Alice", 2023])

---

✏️ 5. Update Existing Data

with connection.cursor() as cursor:
    cursor.execute("""
        UPDATE book
        SET author = %s
        WHERE title = %s;
    """, ["Alice Cooper", "Learning SQL"])

---

🔍 6. Query Data (Select)

with connection.cursor() as cursor:
    cursor.execute("""
        SELECT title, author, published_year
        FROM book
        WHERE published_year >= %s;
    """, [2022])
    rows = cursor.fetchall()
    for row in rows:
        print(row)

---

❌ 7. Delete Data

with connection.cursor() as cursor:
    cursor.execute("""
        DELETE FROM book
        WHERE title = %s;
    """, ["Learning SQL"])

---

🔗 8. Join Example

with connection.cursor() as cursor:
    cursor.execute("""
        SELECT b.title, a.name
        FROM book b
        JOIN author a ON b.author = a.id;
    """)
    results = cursor.fetchall()

---

🌀 9. Transaction Example

from django.db import transaction

try:
    with transaction.atomic():
        with connection.cursor() as cursor:
            cursor.execute("INSERT INTO book (title, author, published_year) VALUES (%s, %s, %s);", 
                           ["Transactional Book", "Bob", 2024])
            cursor.execute("UPDATE book SET published_year = %s WHERE title = %s;", 
                           [2025, "Transactional Book"])
except Exception as e:
    print("Transaction failed:", e)

---

📌 Notes

• Always use parameterized queries (%s) to avoid SQL injection.
• Use with connection.cursor() to ensure proper resource cleanup.
• Table and column names are case-sensitive in some MySQL configurations—be consistent.
• You can define SQL scripts in Django migrations using the RunSQL operation.