关于在Django中直接编写SQL查询的用途、方法及示例的技术咨询

Great question! Django's ORM is incredibly powerful for most day-to-day tasks, but there are times when writing raw SQL is the right call. Let's break down when you'd want to use it, how to implement it safely, and some practical examples.

When to Use Raw SQL #

Here are the most common scenarios where raw SQL makes more sense than relying solely on the ORM:

• Complex queries the ORM can't handle cleanly: Think advanced window functions, multi-table joins with custom aggregations, or database-specific features (like PostgreSQL's jsonb operations) that the ORM doesn't support natively.
• Performance optimization: Sometimes the ORM generates inefficient SQL for large datasets or complex filters. Writing raw SQL lets you tweak the query exactly how you need it—like adding index hints or cutting unnecessary joins.
• Legacy database integration: If your database has pre-existing views, stored procedures, or tables that don't map neatly to Django models, raw SQL is the simplest way to interact with them.
• High-overhead bulk operations: For massive bulk updates or deletes, raw SQL can be significantly faster than looping through model instances or using the ORM's bulk methods (always test with your dataset first!).

How to Use Raw SQL in Django #

Django provides safe, supported ways to run raw SQL—never concatenate user input directly into your SQL string (that's a critical SQL injection risk!).

1. Model.objects.raw(): Map Results to Model Instances #

This is the most "Django-native" approach. It runs your raw SQL and returns model instances, so you can use all the model methods and properties you're familiar with.

Example: Fetch Users Who Joined in 2024

from django.contrib.auth.models import User

# Get users who joined in 2024 using raw SQL
users = User.objects.raw("""
    SELECT * FROM auth_user
    WHERE date_joined >= '2024-01-01' AND date_joined < '2025-01-01'
""")

# Iterate over results like regular User objects
for user in users:
    print(f"{user.username} joined on {user.date_joined.date()}")

You can also pass parameters safely to avoid injection:

def get_users_by_email_domain(domain):
    # Use %s as placeholders (Django handles database-specific syntax automatically)
    return User.objects.raw(
        "SELECT * FROM auth_user WHERE email LIKE %s",
        [f"%@{domain}"]
    )

2. django.db.connection.cursor(): Direct Database Access #

Use this when you don't need model instances—like for aggregate counts, bulk updates, or calling stored procedures. It gives you a standard database cursor to execute queries.

Example: Get Total Active User Count

from django.db import connection

def get_active_user_count():
    # Use a context manager to handle cursor cleanup automatically
    with connection.cursor() as cursor:
        cursor.execute("SELECT COUNT(*) FROM auth_user WHERE is_active = TRUE")
        # Fetch a single result row
        result = cursor.fetchone()
    return result[0] if result else 0

Example: Bulk Mark Inactive Users

def bulk_mark_inactive(last_login_cutoff):
    with connection.cursor() as cursor:
        cursor.execute(
            """
            UPDATE auth_user
            SET is_active = FALSE
            WHERE last_login < %s
            """,
            [last_login_cutoff]
        )
    # Important: This won't trigger Django's model signals (like post_save)
    # If your app relies on these signals, you'll need to handle them manually

Key Safety & Best Practices #

• Always use parameterized queries: Django's placeholders (%s) handle input escaping automatically—never string-concatenate user input into your SQL.
• Watch for database differences: Raw SQL is database-specific. A query written for PostgreSQL might not work in MySQL or SQLite.
• Model signals don't fire: When using cursor() to update/delete records, Django's model signals (like pre_save or post_delete) won't run. Keep this in mind if your app depends on those signals.

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