Django ORM基本操作: # 1.增删改查 create、delete、update、all、filter # 2. 一般: #id>1 models.UserInfo.objects.filter(id__gt=1) #id>=1 models.UserInfo.objects.filter(id__gte=1) #id<1 models.UserInfo.objects.filter(id__lt=1) #id<=1 models.UserInfo.objects.filter(id__lte=1) #id in [1,2,3] models.UserInfo.objects.filter(id__in=[1,2,3]) #id in [1,2] ,切片,要头不要尾 models.UserInfo.objects.filter(id__range=[1,3]) #以什么开头 models.UserInfo.objects.filter(name__startswith='xxxx') #包含 models.UserInfo.objects.filter(name__contains='xxxx') #不包括id=1 models.UserInfo.objects.exclude(id=1) # 3. 排序 #正序 user_list = models.UserInfo.objects.all().order_by('id') #反序,当id一样的情况下,name正序排序 user_list = models.UserInfo.objects.all().order_by('-id','name') # 4. 分组 from django.db.models import Count,Sum,Max,Min #以ut_id为组,查询ut_id和组内id个数。(简记:以部门为组,查询部门的id和有多少人) #v.query是打印出sql语句 v =models.UserInfo.objects.values('ut_id').annotate(xxxx=Count('id')) print(v.query)#select ut_id,count(id) as xxxx from UserInfo group_by ut_id v =models.UserInfo.objects.values('ut_id').annotate(xxxx=Count('id')).filter(xxxx__gt=2) print(v.query) v =models.UserInfo.objects.filter(id__gt=2).values('ut_id').annotate(xxxx=Count('id')).filter(xxxx__gt=2) print(v.query) # 5. F,更新时用于获取原来的值,原来的age都加1 from django.db.models import F models.UserInfo.objects.all().update(age=F("age")+1) # 6. Q,用于构造复杂查询条件 # 应用一: from django.db.models import Q #id>1 models.UserInfo.objects.filter(Q(id__gt=1)) #id=8 或 id=2 models.UserInfo.objects.filter(Q(id=8) | Q(id=2)) #id=8 和 id=2 models.UserInfo.objects.filter(Q(id=8) & Q(id=2)) # 应用二: q1 = Q() q1.connector = 'OR' q1.children.append(('id__gt', 1)) q1.children.append(('id', 10)) q1.children.append(('id', 9)) q2 = Q() q2.connector = 'OR' q2.children.append(('c1', 1)) q2.children.append(('c1', 10)) q2.children.append(('c1', 9)) q3 = Q() q3.connector = 'AND' q3.children.append(('id', 1)) q3.children.append(('id', 2)) q1.add(q3,'OR') con = Q() con.add(q1, 'AND') con.add(q2, 'AND') models.UserInfo.objects.filter(con) (id>1 or id=10 or id=9 or (id=1 and id=2)) and (c1=1 or c1=10 or c1=9) # 7. extra, 额外查询条件以及相关表,排序 models.UserInfo.objects.extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None) # a. 映射 # select # select_params=None # select 此处 from 表 # b. 条件 # where=None # params=None, # select * from 表 where 此处 # c. 表 # tables # select * from 表,此处 # d. 排序 # order_by=None # select * from 表 order by 此处 models.UserInfo.objects.extra( select={'newid':'select count(1) from app01_usertype where id>%s'}, select_params=[1,], where = ['age>%s'], params=[18,], order_by=['-age'], tables=['app01_usertype'] ) #相当于 """ select app01_userinfo.id, (select count(1) from app01_usertype where id>1) as newid from app01_userinfo,app01_usertype where app01_userinfo.age > 18 order by app01_userinfo.age desc """ # 8. 原生SQL语句,记得在settings.py中设置好DATABASES,
和在__init__.py中把MySQLdb修改为通过pymysql模块来连接数据库。
有个问题:结果是元组,无法在模板里面进行类似row.id这样的替换,
这种替换是需要字典或者字典列表才可以进行的,而且
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)报错,
所以无法获取字典或者字典列表这样的结果。
from django.db import connection, connections cursor = connection.cursor() # connection是默认数据库 cursor = connections['db2'].cursor()#指定数据库 cursor.execute("""SELECT * from auth_user where id = %s""", [1]) row = cursor.fetchone()#结果是元组:(k,v) row_list = cursor.fetchall()#结果是元组((k1,v1),(k2,v2)) # 9. 其他操作 http://www.cnblogs.com/wupeiqi/articles/6216618.html