在Django中如何執(zhí)行原生SQL查詢詳解

更新時間：2025年10月21日 10:02:18 作者：唐古烏梁海

Django框架作為Python中一個強大的Web開發(fā)框架,提供了豐富的ORM功能,使得開發(fā)者能夠通過Python對象來操作數(shù)據(jù)庫,這篇文章主要介紹了在Django中如何執(zhí)行原生SQL查詢的相關(guān)資料,需要的朋友可以參考下

在Django中，執(zhí)行原生SQL查詢

1. 使用raw()方法

raw() 方法允許你執(zhí)行原生SQL查詢并返回模型實例：

from myapp.models import Person

# 基本查詢
people = Person.objects.raw('SELECT * FROM myapp_person WHERE age > %s', [18])

# 遍歷結(jié)果
for person in people:
    print(person.name, person.age)

# 帶參數(shù)的查詢
people = Person.objects.raw(
    'SELECT * FROM myapp_person WHERE age BETWEEN %s AND %s',
    [20, 30]
)

2. 使用數(shù)據(jù)庫連接直接執(zhí)行SQL

基本用法：

from django.db import connection

def my_custom_sql():
    with connection.cursor() as cursor:
        # 執(zhí)行查詢
        cursor.execute("SELECT * FROM myapp_person WHERE age > %s", [25])
        
        # 獲取所有結(jié)果
        rows = cursor.fetchall()
        
        # 或者逐行獲取
        # row = cursor.fetchone()
        # rows = cursor.fetchmany(size=10)
    
    return rows

返回字典格式的結(jié)果：

from django.db import connection

def dictfetchall(cursor):
    """將游標結(jié)果轉(zhuǎn)換為字典列表"""
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

def my_custom_sql():
    with connection.cursor() as cursor:
        cursor.execute("SELECT id, name, age FROM myapp_person")
        results = dictfetchall(cursor)
    return results

3. 使用多個數(shù)據(jù)庫

如果你的項目配置了多個數(shù)據(jù)庫：

from django.db import connections

def query_multiple_dbs():
    # 使用默認數(shù)據(jù)庫
    with connections['default'].cursor() as cursor:
        cursor.execute("SELECT * FROM table1")
        # 處理結(jié)果
    
    # 使用其他數(shù)據(jù)庫
    with connections['other_db'].cursor() as cursor:
        cursor.execute("SELECT * FROM table2")
        # 處理結(jié)果

4. 執(zhí)行寫操作（INSERT/UPDATE/DELETE）

from django.db import connection

def update_data():
    with connection.cursor() as cursor:
        # INSERT
        cursor.execute(
            "INSERT INTO myapp_person (name, age) VALUES (%s, %s)",
            ['John', 30]
        )
        
        # UPDATE
        cursor.execute(
            "UPDATE myapp_person SET age = %s WHERE name = %s",
            [31, 'John']
        )
        
        # DELETE
        cursor.execute(
            "DELETE FROM myapp_person WHERE age < %s",
            [18]
        )

5. 事務處理

from django.db import transaction, connection

@transaction.atomic
def complex_operation():
    with connection.cursor() as cursor:
        try:
            # 一系列SQL操作
            cursor.execute("UPDATE accounts SET balance = balance - %s", [100])
            cursor.execute("UPDATE accounts SET balance = balance + %s", [100])
        except Exception as e:
            # 出錯時會自動回滾
            print(f"Error: {e}")

6. 實用的封裝函數(shù)

from django.db import connection

def execute_query(query, params=None, return_dict=True):
    """
    執(zhí)行SQL查詢的通用函數(shù)
    """
    with connection.cursor() as cursor:
        cursor.execute(query, params or [])
        
        if query.strip().upper().startswith('SELECT'):
            if return_dict:
                columns = [col[0] for col in cursor.description]
                return [dict(zip(columns, row)) for row in cursor.fetchall()]
            else:
                return cursor.fetchall()
        else:
            # 對于非SELECT查詢，返回受影響的行數(shù)
            return cursor.rowcount

# 使用示例
results = execute_query(
    "SELECT name, age FROM myapp_person WHERE age > %s",
    [25]
)

注意事項

SQL注入防護：始終使用參數(shù)化查詢，不要直接拼接字符串

# ? 安全：使用參數(shù)化查詢
cursor.execute("SELECT * FROM users WHERE username = %s", [username])

# ? 危險：字符串拼接（SQL注入風險）
cursor.execute(f"SELECT * FROM users WHERE username = '{username}'")

# ? 危險：直接傳遞用戶輸入
cursor.execute("SELECT * FROM users WHERE username = %s" % username)