4-数据库操作

输入数据

控制台输入

python manage.py shell进入虚拟环境

1、save插入



>>>from firstApp.models import Topic,Comment


>>>from django.contrib.auth.models import User #save插入方法


>>>user=User.objects.get(username='xym')


>>>topic=Topic(title='four topic',content='this is the four topic',user=user)


>>>topic.save()


>>>comment=Comment(content='so bad',up=123,down=321,topics_id=topic)


>>>comment.save()




2、create插入




>>> user1=User.objects.get(username='xym')
>>> topic_2=Topic.objects.create(title='second topic',content='this is the second topic',user=user1)
>>> coment=Comment.objects.create(content='so good!',up=99,down=33,topics=topic_2)




 


查询数据


1、get（）查询：返回单实例对象




 


 


 查询时支持用pk代表主键名称 Topic.objects.get(pk=1)效果一样的


2、get_or_greate()查询：返回tuple对象。当有信息时，会返回查到的信息，并且返回false；没有信息时，则会创建信息，返回true（需要信息完整）




>>> Topic.objects.get_or_create(id=1,title='first topic')
(<Topic: 1:first topic>, False)
>>> Topic.objects.get_or_create(id=1)
(<Topic: 1:first topic>, False)
>>> Topic.objects.get_or_create(id=3)
(<Topic: 3:three>, False)


>>> Topic.objects.get_or_create(id=4,title='four topic',content='this is the four topic!',user=user)
(<Topic: 4:four topic>, True)










 


3、QuerySet的查询方法：返回可以是零个，一个或者多个


一、all（）方法




>>> Topic.objects.all()
<QuerySet [<Topic: 4:four topic>, <Topic: 3:three>, <Topic: 2:second topic>, <Topic: 1:first topic>]>



>>> print(Topic.objects.all().query)#返回查询过程


SELECT `firstApp_topic`.`id`,


         `firstApp_topic`.`create_time`,


         `firstApp_topic`.`update_time`,


         `firstApp_topic`.`title`,


         `firstApp_topic`.`content`,


         `firstApp_topic`.`is_online`,


         `firstApp_topic`.`user_id`


FROM `firstApp_topic`


ORDER BY  `firstApp_topic`.`create_time` DESC




 


二、reverse方法：逆序打印




>>> Topic.objects.reverse()
<QuerySet [<Topic: 1:first topic>, <Topic: 2:second topic>, <Topic: 3:three>, <Topic: 4:four topic>]>



>>> print(Topic.objects.reverse().query)
SELECT `firstApp_topic`.`id`,
         `firstApp_topic`.`create_time`,
         `firstApp_topic`.`update_time`,
         `firstApp_topic`.`title`,
         `firstApp_topic`.`content`,
         `firstApp_topic`.`is _online`,
         `firstApp_topic`.`user_id`
FROM `firstApp_topic`
ORDER BY  `firstApp_topic`.`create_time` ASC




 


三、filter过滤方法：相当于sql语句中的where；连接符为双下划线




>>> Topic.objects.filter(id__lte=3)
<QuerySet [<Topic: 3:three>, <Topic: 2:second topic>, <Topic: 1:first topic>]>






查询关键字
含义
相当于where




gt　　
大于
>




gte
大于等于
>=




lt
小于
<




lte
小于等于
<=




exact
等于
=




iexact
忽略大小写的等于
like xyz




in
是否在集合中
in (a,b)




contains;icontains(忽略大小写)
是否包含
like binary %a%




startswith；istartswith（忽略大小写）
以，，，开头
like binary a%




endswith；iendswith（忽略大小写）
以，，，结尾
like binary %a








 


四、exclude反向过滤：




>>> Topic.objects.exclude(id__lte=3)
<QuerySet [<Topic: 4:four topic>]>




五、链式查询




>>> Topic.objects.filter(id__lte=3).exclude(title__contains='tle')
<QuerySet [<Topic: 3:three>, <Topic: 2:second topic>, <Topic: 1:first topic>]>
>>> Topic.objects.filter(id__lte=3).exclude(title__contains='tle').filter(content__contains='first')
<QuerySet [<Topic: 1:first topic>]>

查询过程


print(Topic.objects.filter(id__lte=3).exclude(title__contains='tle').filter(content__contains='first').query)




SELECT `firstApp_topic`.`id`,
         `firstApp_topic`.`create_time`,
         `firstApp_topic`.`update_time`,
         `firstApp_topic`.`title`,
         `firstApp_topic`.`content`,
         `firstApp_topic`.`is _online`,
         `firstApp_topic`.`user_id`
FROM `firstApp_topic`
WHERE (`firstApp_topic`.`id` <= 3
        AND NOT (`firstApp_topic`.`title` LIKE BINARY %tle%)
        AND `firstApp_topic`.` content` LIKE BINARY %first%)
ORDER BY  `firstApp_topic`.`create_time` DESC






 


六、value返回结果：返回指定字段字典数据




>>> Comment.objects.values('id','content')
<QuerySet [{'id': 3, 'content': 'very good!!'}, {'id': 2, 'content': 'good!'}, {'id': 1, 'content': 'very good!'}]>




SELECT `firstApp_comment`.`id`,
         `firstApp_comment`.`content`
FROM `firstApp_comment`
ORDER BY  `firstApp_comment`.`create_time` DESC




 




 


七、values_list：返回元组数据




>>> Comment.objects.values_list('id','content')
<QuerySet [(3, 'very good!!'), (2, 'good!'), (1, 'very good!')]>




SELECT `firstApp_comment`.`id`,
         `firstApp_comment`.`content`
FROM `firstApp_comment`
ORDER BY  `firstApp_comment`.`create_time` DESC




 




 4、RawQuerySet查询方法：sql语句查询


一、简单查询




>>> for comm in Comment.objects.raw('select * from firstapp_comment'):
...     print('%d:%s'%(comm.id,comm.content))
...
1:very good!
2:good!
3:very good!!




 


二，params参数：不要拼接sql语句




>>> for topic in Topic.objects.raw('select * from firstapp_topic where title=%s',['first topic']):
...     print(topic)
...
1:first topic




 


5、关联关系查询（前面创建model的时候，comment中引用了topic）


一、反向查询关联关系




>>> topic =Topic.objects.get(id=1)
>>> topic
<Topic: 1:first topic>
>>> topic.comment_set.all()
<QuerySet [<Comment: 1:very good!>]>
>>> topic.comment_set.filter(content='very good!')
<QuerySet [<Comment: 1:very good!>]>




 


二、夸关联关系查询




>>> print(Comment.objects.filter(topics__title__contains='first'))
<QuerySet [<Comment: 1:very good!>]>
>>> print(Comment.objects.filter(topics__title__contains='first').query)




SELECT `firstApp_comment`.`id`,
         `firstApp_comment`.`create_time`,
         `firstApp_comment`.`update_time`,
         `firstApp_comment`.`content`,
         `firstApp_comment`.`topics_id`,
         `first App_comment`.`up`,
         `firstApp_comment`.`down`
FROM `firstApp_comment`
INNER JOIN `firstApp_topic`
    ON (`firstApp_comment`.`topics_id` = `firstApp_topic`.`id`)
WHERE `firs tApp_topic`.`title` LIKE BINARY %first%
ORDER BY  `firstApp_comment`.`create_time` DESC




 




 


6、F查询和Q查询


一、F查询：允许使用运算




>>> from firstApp.models import Topic,Comment
>>> from django.contrib.auth.models import User
>>> from django.db.models import F


>>> Comment.objects.filter(up__lte=F('down'))


<QuerySet [<Comment: 4:lalla!>]>





 


二、Q查询：可以使用&（与）、|（或）、~（非，not）


 




>>> from django.db.models import Q
>>> Comment.objects.filter(Q(up__gt=66)|Q(down__lt=33))
<QuerySet [<Comment: 4:lalla!>, <Comment: 3:very good!!>, <Comment: 1:very good!>]>




SELECT `firstApp_comment`.`id`,
         `firstApp_comment`.`create_time`,
         `firstApp_comment`.`update_time`,
         `firstApp_comment`.`content`,
         `firstApp_comment`.`topics_id`,
         `first App_comment`.`up`,
         `firstApp_comment`.`down`
FROM `firstApp_comment`
WHERE (`firstApp_comment`.`up` > 66
        OR `firstApp_comment`.`down` < 33)
ORDER BY  `firstApp_comment`. `create_time` DESC




 




 


 7、聚合查询和分组查询


1、聚合查询


使用前将需要的函数导入




from django.db.models import Avg,Count,Min,Max,Sum
>>>Comment.objects.filter(topics=1).aggregate(zhichi=Sum('up'))#zhichi是别名，Sum是函数，up是字段
{'zhichi':66}




 


2、分组查询


（1）values在annotate前，会按照values里面的值进行分组，在执行annotate里面的函数


（2）values在annotate后，values仅限制需要的字段




>>> Comment.objects.values('topics_id').annotate(Sum('up'))#当有values时，会先按照values里面的值进行分组
<QuerySet [{'topics_id': 2, 'up__sum': 99}, {'topics_id': 2, 'up__sum': 99}, {'topics_id': 2, 'up__sum': 55}, {'topics_id': 1, 'up__sum': 66}]>




SELECT `firstApp_comment`.`topics_id`,
         SUM(`firstApp_comment`.`up`) AS `up__sum`
FROM `firstApp_comment`
GROUP BY  `firstApp_comment`.`topics_id`, `firstApp_comment`.`cr eate_time`
ORDER BY  `firstApp_comment`.`create_time` DESC




 




 




print( Comment.objects.values('topics_id').annotate(Sum('up')).order_by().query)






SELECT `firstApp_comment`.`topics_id`,
         SUM(`firstApp_comment`.`up`) AS `up__sum`
FROM `firstApp_comment`
GROUP BY  `firstApp_comment`.`topics_id`
ORDER BY  NULL




 






            

            
posted @ 
2019-11-23 21:34 
学习就是进步！ 
阅读(295) 
评论(0) 
 
收藏 
举报