Django and MySQL

Complete Step-by-Step: Django CRUD Project with MySQL

---

Step 1: Why switch to MySQL

• SQLite is simple but not scalable or suitable for production.

• MySQL allows multi-user access, security, and real-world job relevance.

---

Step 2: Install MySQL and MySQL client

1. Download & install MySQL Community Server: https://dev.mysql.com/downloads/mysql/

2. Start MySQL service.

3. Install Python MySQL client:

pip install mysqlclient

• Why: Django uses mysqlclient to communicate with MySQL.

---

Step 3: Create MySQL Database and User

CREATE DATABASE student_db;
CREATE USER 'student_user'@'localhost' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON student_db.* TO 'student_user'@'localhost';
FLUSH PRIVILEGES;

• Why: Dedicated DB and user ensures security and organization.

---

Step 4: Configure Django to use MySQL

1. Open settings.py and replace DATABASES:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'student_db',
        'USER': 'student_user',
        'PASSWORD': 'password123',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

---

Step 5: Backup SQLite (Optional)

python manage.py dumpdata > db_backup.json

• Why: Keeps old data safe in case you want to restore.

---

Step 6: Delete SQLite DB and migrate to MySQL

rm db.sqlite3
python manage.py makemigrations
python manage.py migrate

• Optional: Restore old data:

python manage.py loaddata db_backup.json

• Why: Applies your models to MySQL instead of SQLite.

---

Step 7: Test CRUD in MySQL

• Run server:

python manage.py runserver

• Access /admin or your CRUD pages and verify all operations work.

---

Step 8: Improve your CRUD Project

8a: Add Messages

• Why: User sees feedback on actions.

• How: In views.py:

from django.contrib import messages

messages.success(request, "Student added successfully!")

• Add {% if messages %} in templates to show messages.

---

8b: Add Pagination

• Why: Makes lists manageable.

• How: In views.py:

from django.core.paginator import Paginator

students = Student.objects.all()
paginator = Paginator(students, 10)  # 10 per page
page_number = request.GET.get('page')
page_obj = paginator.get_page(page_number)

• In template: loop over page_obj and add page navigation links.

---

8c: Add Authentication

• Why: Only logged-in users can access CRUD pages.

• How:

from django.contrib.auth.decorators import login_required

@login_required
def student_list(request):
    ...

• Add login/logout pages using Django auth views.

---

8d: Add User Roles

• Why: Different permissions for staff/admin.

• How: Use is_staff or is_superuser in views and templates:

if request.user.is_staff:
    # show extra buttons

---

8e: Add File Uploads (Optional)

• Why: Upload student photos.

• How: In models.py:

photo = models.ImageField(upload_to='photos/', blank=True, null=True)

• Update settings.py:

MEDIA_URL = '/media/'
MEDIA_ROOT = BASE_DIR / 'media'

• Add enctype="multipart/form-data" to form and handle uploads in views.

---

Step 9: Optimize Queries

• Use select_related for ForeignKey fields:

students = Student.objects.select_related('course').all()

• Add indexes on frequently searched fields:

fullname = models.CharField(max_length=100, db_index=True)

---

Step 10: Deploy Project

1. Choose hosting: Render, PythonAnywhere, Railway.

2. Connect MySQL production database.

3. Serve static & media files.

4. Set DEBUG=False in settings.py.

5. Push project to GitHub for portfolio.

---

Step 11: Next Projects (After This)

1. Library Management System

2. Employee Management System

3. Blog/Content Management System

4. E-commerce / Inventory System

• Each adds more advanced MySQL and Django skills.

---

✅ Summary of Full Step-by-Step Path:

1. Install MySQL + MySQL client

2. Create database and user

3. Configure Django settings.py

4. Backup SQLite (optional)

5. Delete SQLite DB

6. Run migrations in MySQL

7. Test CRUD in MySQL

8. Improve CRUD (messages, pagination, auth, roles, uploads)

9. Optimize queries (select_related, indexes)

10. Deploy project

11. Continue with advanced projects