1、Manager.raw(raw_query,?params=None,?translations=None)

>>> for p in Person.objects.raw('SELECT * FROM myapp_person'): ... print(p) John Smith Jane Jones

這個方法接受一個原始的SQL查詢，執(zhí)行它，并返回一個django.db.models.query。RawQuerySet實例。這個RawQuerySet實例可以像普通的QuerySet一樣遍歷，以提供對象實例。

（1）字段匹配

>>> Person.objects.raw('''SELECT first AS first_name, ... last AS last_name, ... bd AS birth_date, ... pk AS id, ... FROM some_other_table''')>>> name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'} >>> Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)

（2）即使沒有顯示表明查詢字段，也可以獲取

>>> for p in Person.objects.raw('SELECT id, first_name FROM myapp_person'): ... print(p.first_name, # This will be retrieved by the original query ... p.last_name) # This will be retrieved on demand ... John Smith Jane Jones

（3）執(zhí)行帶參數(shù)SQL

字符串用%s占位符

字典用%(key)s占位符

>>> lname = 'Doe' >>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])

（4）嚴禁使用字符串拼接

>>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname >>> Person.objects.raw(query)

（4）參數(shù)不能用引號包裹

>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'"

2、通過connection.cursor()執(zhí)行SQL

對象django.db.connection表示默認的數(shù)據(jù)庫連接。要使用數(shù)據(jù)庫連接，請調(diào)用connection.cursor()來獲得一個游標對象。然后調(diào)用cursor.execute(sql， [params])方法以執(zhí)行sql

cursor.fetchone()或cursor.fetchall()以返回結(jié)果行。

from django.db import connectiondef my_custom_sql(self):with connection.cursor() as cursor:cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])row = cursor.fetchone()return row

（1）傳遞百分比參數(shù)需要寫兩個百分號

cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])

（2）cursor執(zhí)行不會返回列名

用字典或命名元組

def dictfetchall(cursor):"Return all rows from a cursor as a dict"columns = [col[0] for col in cursor.description]return [dict(zip(columns, row))for row in cursor.fetchall()] ? from collections import namedtupledef namedtuplefetchall(cursor):"Return all rows from a cursor as a namedtuple"desc = cursor.descriptionnt_result = namedtuple('Result', [col[0] for col in desc])return [nt_result(*row) for row in cursor.fetchall()] >>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2"); >>> dictfetchall(cursor) [{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]

來源:https://www.cnblogs.com/SunQi-Tony/p/9985616.html

總結(jié)

以上是生活随笔為你收集整理的Django运行SQL语句的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。