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')
(, False)
>>> Topic.objects.get_or_create(id=1)
(, False)
>>> Topic.objects.get_or_create(id=3)
(, False)

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

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

一、all（）方法

>>> Topic.objects.all()
, , , ]>

>>> 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()
, , , ]>


>>> 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)
, , ]>

查询关键字	含义	相当于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)
]>

五、链式查询

>>> Topic.objects.filter(id__lte=3).exclude(title__contains='tle')
, , ]>
>>> Topic.objects.filter(id__lte=3).exclude(title__contains='tle').filter(content__contains='first')
]>

查询过程

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')
'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')
'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.comment_set.all()
]>
>>> topic.comment_set.filter(content='very good!')
]>

二、夸关联关系查询

>>> print(Comment.objects.filter(topics__title__contains='first'))
]>
>>> 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'))

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

>>> from django.db.models import Q
>>> Comment.objects.filter(Q(up__gt=66)|Q(down__lt=33))
, , ]>

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里面的值进行分组
'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

PythonDjango Python