执行SQL代码
Django有两种方法执行原始SQL语句
- Manager.raw() 返回model实例
- 直接执行原始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.
执行一个原始SQL语句，返回一个RawQuerySet的实例，一个iterated实例。
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模型指向的数据表app_label+下划线+model小写名
raw()自动映射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 （意味着可以借用这个model的属性，套上其他额配套数据）
>>> 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.查找的时候必须要带上主键，其他的字段可以不用返回。
>>>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] #先找出全部在切片 不符合数据查询优化
>>> 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 执行纯粹的SQL语句
from django.db import connection
def my_custom_sql(self):
with connection.cursor() as cursor: #获取光标
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz]) #执行SQL语句
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
row = cursor.fetchone() #获取一条记录 cursor.fetchall()获取所有记录
return row
