原生查询

方式一：

包含主键

from together.models import *
for d in Student.objects.raw('select * from together_student'):
    print d
    
Student:泰迪
Student:汪汪
Student:旺旺大礼包
Student:隔壁老王
Student:小迷妹
Student:皮卡丘

不包含主键

for d in Student.objects.raw('select sname,score from together_student'):
    print d   
    
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "E:\workspace\pyCharmProject\test5\venv\lib\site-packages\django\db\models\query.py", line 1248, in __iter__
    raise InvalidQuery('Raw query must include the primary key')
InvalidQuery: Raw query must include the primary key

注意：从上我们可以看出，利用Student.objects.raw语句原生sql查询，在查询的列段不包含主键的时候会报错；

方式二：

包含主键

from django.db import connection
cursor = connection.cursor()
cursor.execute('select * from together_student')
<django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x00000000046C44C8>
datas = cursor.fetchall()
print datas
[(1, u'\u6cf0\u8fea', 66, 2), (2, u'\u6c6a\u6c6a', 77, 2), (3, u'\u65fa\u65fa\u5927\u793c\u5305', 99, 2), (4, u'\u9694\u58c1\u8001\u738b', 88, 2), (5, u'\u5c0f\u8ff7\u59b9', 88, 3), (6, u'\u76ae\u5361\u4e18', 79, 3)]
cursor.close()

不包含主键

cursor = connection.cursor()
cursor.execute('select sname,score from together_student')
<django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x00000000047EB318>
ds = cursor.fetchall()
print ds
[(u'\u6cf0\u8fea', 66), (u'\u6c6a\u6c6a', 77), (u'\u65fa\u65fa\u5927\u793c\u5305', 99), (u'\u9694\u58c1\u8001\u738b', 88), (u'\u5c0f\u8ff7\u59b9', 88), (u'\u76ae\u5361\u4e18', 79)]
cursor.close()

跟上一种方式相比，这一种方式不会报错

获取一条记录

cursor = connection.cursor()
cursor.execute('select * from together_student where sno = 2')
<django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x00000000046C44C8>
cursor.fetchone()
(2, u'\u6c6a\u6c6a', 77, 2)
cursor.close()

Q查询

查看底层sql

def showsql():
    from django.db import connection
    print connection.queries[-1]['sql']

条件与操作

from django.db.models import Q,F
Student.objects.filter(Q(sno=1)&Q(sname='泰迪'))
<QuerySet [<Student: Student:泰迪>]>
Student.objects.filter(Q(sno=1)&Q(sname='泰迪1'))
<QuerySet []>
showsql()
SELECT "together_student"."sno", "together_student"."sname", "together_student"."score", "together_student"."clazz_id" FROM "together_student" WHERE ("together_student"."sno" = 1 AND "together_student"."sname" = '泰迪1') LIMIT 21

Student.objects.get(Q(sno=1)&Q(sname='泰迪'))
<Student: Student:泰迪>
showsql()
SELECT "together_student"."sno", "together_student"."sname", "together_student"."score", "together_student"."clazz_id" FROM "together_student" WHERE ("together_student"."sno" = 1 AND "together_student"."sname" = '泰迪')

注意：从底层sql可以看出，没有特殊要求的话，尽量使用filter，性能高些；

条件或操作

Student.objects.filter(Q(sno=1)|Q(sname='小迷妹'))
<QuerySet [<Student: Student:泰迪>, <Student: Student:小迷妹>]>
showsql()
SELECT "together_student"."sno", "together_student"."sname", "together_student"."score", "together_student"."clazz_id" FROM "together_student" WHERE ("together_student"."sno" = 1 OR "together_student"."sname" = '小迷妹') LIMIT 21

条件非操作

Student.objects.filter(~Q(sname='汪汪'))
<QuerySet [<Student: Student:泰迪>, <Student: Student:旺旺大礼包>, <Student: Student:隔壁老王>, <Student: Student:小迷妹>, <Student: Student:皮卡丘>]>
showsql()
SELECT "together_student"."sno", "together_student"."sname", "together_student"."score", "together_student"."clazz_id" FROM "together_student" WHERE NOT ("together_student"."sname" = '汪汪') LIMIT 21
Student.objects.filter(~Q(Q(sno=1)|Q(sname='小迷妹')))
<QuerySet [<Student: Student:汪汪>, <Student: Student:旺旺大礼包>, <Student: Student:隔壁老王>, <Student: Student:皮卡丘>]>
showsql()
SELECT "together_student"."sno", "together_student"."sname", "together_student"."score", "together_student"."clazz_id" FROM "together_student" WHERE NOT (("together_student"."sno" = 1 OR "together_student"."sname" = '小迷妹')) LIMIT 21

F查询

学号大于3的学生成绩加10分

Student.objects.filter(Q(sno__gt=3))
<QuerySet [<Student: Student:隔壁老王>, <Student: Student:小迷妹>, <Student: Student:皮卡丘>]>
showsql()
SELECT "together_student"."sno", "together_student"."sname", "together_student"."score", "together_student"."clazz_id" FROM "together_student" WHERE "together_student"."sno" > 3 LIMIT 21
Student.objects.filter(Q(sno__gt=3)).update(score=F('score')+10)
3
showsql()
UPDATE "together_student" SET "score" = ("together_student"."score" + 10) WHERE "together_student"."sno" > 3

学号大于3的学生入学日期提前2天

import datetime
Student.objects.filter(Q(sno__gt=3)).update(created=F('created')+datetime.timedelta(days=-2))

over......