執(zhí)行SQL代碼
Django有兩種方法執(zhí)行原始SQL語句
- Manager.raw() 返回model實(shí)例
- 直接執(zhí)行原始SQL
Manager
Manager.raw(raw_query, params=None, translations=None)
This method takes a raw SQL query, executes it, and returns a django.db.models.query.RawQuerySet.
執(zhí)行一個(gè)原始SQL語句，返回一個(gè)RawQuerySet的實(shí)例关摇，一個(gè)iterated實(shí)例。
class Person(models.Model):
first_name = models.CharField(...)
last_name = models.CharField(...)
birth_date = models.DateField(...)
>>> for p in Person.objects.raw('SELECT * FROM myapp_person'): print(p)
>>>John Smith
>>>Jane Jones
>>>myapp_person是Person模型指向的數(shù)據(jù)表app_label+下劃線+model小寫名
raw()自動(dòng)映射model中的字段
>>> Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')
>>> Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person')
這種匹配是通過name的身害，這意味著可以通過SQL中AS語句去映射model中的fields （意味著可以借用這個(gè)model的屬性坛悉，套上其他額配套數(shù)據(jù)）
>>> 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)
####There is only one field that you can’t leave out - the primary key field.查找的時(shí)候必須要帶上主鍵顺献，其他的字段可以不用返回。
>>>for p in Person.objects.raw('select id ,nam from doc_author'):print(p)
>>>Joe
John
Quexitao
Heminhuang
Mark Lutz
Weiwei
raw()支持索引(index)
>>>first_person = Person.objects.raw('SELECT * FROM myapp_person')[0] #先找出全部在切片 不符合數(shù)據(jù)查詢優(yōu)化
>>> first_person = Person.objects.raw('SELECT * FROM myapp_person LIMIT 1')[0]
傳值給raw()
>>> lname = 'Doe'
>>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])
這樣做容易被攻擊
>>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
>>> Person.objects.raw(query)
Executing custom SQL directly 執(zhí)行純粹的SQL語句
from django.db import connection
def my_custom_sql(self):
with connection.cursor() as cursor: #獲取光標(biāo)
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz]) #執(zhí)行SQL語句
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
row = cursor.fetchone() #獲取一條記錄 cursor.fetchall()獲取所有記錄
return row
