django怎么执行sql语句
1、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查询，执行它，并返回一个django.db.models.query。RawQuerySet实例。这个RawQuerySet实例可以像普通的QuerySet一样遍历，以提供对象实例。
2、（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)
3、（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
4、（3）执行带参数SQL
字符串用%s占位符
字典用%(key)s占位符
>>> lname = 'Doe'
>>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])
5、（4）严禁使用字符串拼接
>>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
>>> Person.objects.raw(query)
6、（5）参数不能用引号包裹‘’
>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'"
7、2、通过connection.cursor()执行SQL
对象django.db.connection表示默认的数据库连接。要使用数据库连接，请调用connection.cursor()来获得一个游标对象。然后调用cursor.execute(sql， [params])方法以执行sql
cursor.fetchone()或cursor.fetchall()以返回结果行。
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])
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
row = cursor.fetchone()
return row
8、（1）传递百分比参数需要写两个百分号
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])
9、（2）cursor执行不会返回列名
用字典或命名元组
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 namedtuple
def namedtuplefetchall(cursor):
"Return all rows from a cursor as a namedtuple"
desc = cursor.description
nt_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}]