如何使用MySQLdb高效开发Django站点？Longtext字段存读疑问

Hey there! Let's break down how to streamline your Django development workflow while using MySQLdb to handle JSON-stored dictionaries (instead of relying solely on Django ORM). Here are practical, actionable tips to work efficiently:

1. Encapsulate Database Operations in Reusable Utilities #

Don't clutter your views with raw MySQLdb code—wrap your JSON save/load logic into dedicated utility functions or a custom data access layer. This keeps your code DRY, easier to maintain, and reduces repetition across views.

Example utility (utils/db_json_ops.py):

import MySQLdb
import json
import logging
from django.conf import settings

logger = logging.getLogger(__name__)

def save_dict_to_table(table_name: str, json_field: str, data_dict: dict, **extra_fields):
    """Save a dictionary as JSON to a longtext field"""
    conn = MySQLdb.connect(
        host=settings.DATABASES['default']['HOST'],
        user=settings.DATABASES['default']['USER'],
        passwd=settings.DATABASES['default']['PASSWORD'],
        db=settings.DATABASES['default']['NAME'],
        charset='utf8mb4'
    )
    cursor = conn.cursor()
    
    # Build SQL query dynamically
    all_fields = [json_field] + list(extra_fields.keys())
    all_values = [json.dumps(data_dict)] + list(extra_fields.values())
    placeholders = ", ".join(["%s"] * len(all_values))
    
    sql = f"INSERT INTO {table_name} ({', '.join(all_fields)}) VALUES ({placeholders})"
    
    try:
        cursor.execute(sql, all_values)
        conn.commit()
        logger.info(f"Successfully saved JSON data to {table_name}")
    except Exception as e:
        conn.rollback()
        logger.error(f"Failed to save data: {str(e)}")
        raise e
    finally:
        cursor.close()
        conn.close()

def load_dict_from_table(table_name: str, json_field: str, filter_field: str, filter_value) -> dict | None:
    """Load and parse JSON from a longtext field into a dictionary"""
    conn = MySQLdb.connect(
        host=settings.DATABASES['default']['HOST'],
        user=settings.DATABASES['default']['USER'],
        passwd=settings.DATABASES['default']['PASSWORD'],
        db=settings.DATABASES['default']['NAME'],
        charset='utf8mb4'
    )
    cursor = conn.cursor()
    
    sql = f"SELECT {json_field} FROM {table_name} WHERE {filter_field} = %s"
    cursor.execute(sql, (filter_value,))
    result = cursor.fetchone()
    
    cursor.close()
    conn.close()
    
    if result:
        try:
            return json.loads(result[0])
        except json.JSONDecodeError as e:
            logger.error(f"Invalid JSON in {table_name}: {str(e)}")
            return None
    return None

Then use it in views like this:

# views.py
from django.http import JsonResponse
from utils.db_json_ops import save_dict_to_table, load_dict_from_table

def save_user_preferences(request):
    if request.method == "POST":
        preferences = {"theme": "dark", "notifications": True}
        save_dict_to_table("user_settings", "preferences", preferences, user_id=123)
        return JsonResponse({"status": "success"})

def get_user_preferences(request, user_id):
    preferences = load_dict_from_table("user_settings", "preferences", "user_id", user_id)
    return JsonResponse(preferences or {"status": "no_data"})

2. Use Django Models for Table Structure Management #

Even if you're using raw MySQLdb for data operations, define a Django model that maps to your table. This lets you use Django's migration system to safely manage schema changes (instead of writing raw SQL for table creation/alteration).

Example model:

# models.py
from django.db import models

class UserSettings(models.Model):
    user_id = models.IntegerField(primary_key=True)
    preferences = models.TextField()  # Maps to MySQL longtext

    class Meta:
        db_table = "user_settings"

Run python manage.py makemigrations and python manage.py migrate to create/update the table—this ensures your schema stays consistent across environments.

3. Add Validation for JSON Data #

Avoid storing invalid JSON by validating your dictionaries before saving. Use Django's serializers or libraries like jsonschema to enforce structure:

from rest_framework import serializers

class PreferencesSerializer(serializers.Serializer):
    theme = serializers.ChoiceField(choices=["light", "dark", "system"])
    notifications = serializers.BooleanField()

def save_user_preferences(request):
    if request.method == "POST":
        data = request.POST.dict()
        serializer = PreferencesSerializer(data=data)
        if serializer.is_valid():
            save_dict_to_table("user_settings", "preferences", serializer.validated_data, user_id=123)
            return JsonResponse({"status": "success"})
        return JsonResponse({"errors": serializer.errors}, status=400)

4. Cache Parsed JSON for Performance #

If some JSON data doesn't change frequently, cache the parsed dictionary using Django's built-in cache framework (e.g., Redis, Memcached). This avoids repeated database calls and JSON parsing:

from django.core.cache import cache

def get_user_preferences(request, user_id):
    cache_key = f"user_preferences_{user_id}"
    preferences = cache.get(cache_key)
    
    if not preferences:
        preferences = load_dict_from_table("user_settings", "preferences", "user_id", user_id)
        cache.set(cache_key, preferences, 3600)  # Cache for 1 hour
    
    return JsonResponse(preferences or {"status": "no_data"})

5. Consider Django's JSONField (If Feasible) #

If you're using MySQL 5.7+ or MariaDB 10.2+, Django's ORM supports JSONField natively. This eliminates the need for manual json.dumps()/json.loads() and lets you query JSON data directly via ORM:

# models.py
from django.db import models

class UserSettings(models.Model):
    user_id = models.IntegerField(primary_key=True)
    preferences = models.JSONField()  # Automatically handles JSON serialization

# Usage in views
def save_preferences(request):
    UserSettings.objects.update_or_create(
        user_id=123,
        defaults={"preferences": {"theme": "dark", "notifications": True}}
    )

def get_preferences(request, user_id):
    settings = UserSettings.objects.get(user_id=user_id)
    return JsonResponse(settings.preferences)

If your "multi-key" issue refers to composite primary keys, look into third-party packages like django-composite-foreignkey—this lets you work with composite keys within Django's ORM, reducing the need for raw MySQLdb calls.

内容的提问来源于stack exchange，提问作者user9534288