Django数据库基本API示例

数据模型创建后，Django会自动给予一套数据库抽象API，允许增删改查对象。在Django实际代码中，这些应该都是写在views.py内的。

官方文档：https://docs.djangoproject.com/zh-hans/4.1/topics/db/queries/

进入交互式命令行

python manage.py shell

导入模型

from polls.models import Choice, Question
from django.utils import timezone

新增记录

q = Question(question_text="What's new?", pub_date=timezone.now())
q.save()
# 等价于
# insert into polls_question (id,question_text,pub_date) value ('1','What's new?','2023-01-04 08:57:52.068086');

所有查询记录

前提是模型实现了__str__()函数，返回的是 self.question_text。如果返回的是所有字段，那么等价于select *

Question.objects.all()
# 等价于
# select question_text from polls_question;

按字段查询

pk意为PRIMARY KEY，等价于当前模型中的 id

Question.objects.filter(id=1) | Question.objects.filter(pk=1)
Question.objects.filter(question_text="What's new?")
# 等价于
select question_text from polls_question where id=1;
select question_text from polls_question where question_text="What's new?";

模糊查找

Question.objects.filter(question_text__startswith='What')
# 等价于
select question_text from polls_question where question_text like 'how%';

获取当前年份并按时间查找

只返回单个结果

current_year = timezone.now().year
Question.objects.get(pub_date__year=current_year)
# 等价于(嵌套子查询)
select question_text from polls_question where year(pub_date) =(select year(curdate()));

通过主表对象给从表添加数据

q = Question.objects.get(pk=1)
q.choice_set.create(choice_text='Not much', votes=0)
# 等价于
insert into polls_choice (id,choice_text,votes,question_id) value ('1','Not much','0','1');

查找记录数量

q.choice_set.count()
# 等价于
select count(*) from polls_choice as c  join polls_question as q on c.question_id=q.id where q.id='1' ;

连接查询

通过从表对象按主表条件查询

Choice.objects.filter(question__pub_date__year=current_year)
# 等价于
select choice_text from polls_choice as c join polls_question as q on c.question_id=q.id where year(q.pub_date) =(select year(curdate()));

模糊查询并删除记录

q.choice_set.filter(choice_text__startswith='Just hacking').delete()
# 等价于
delete from polls_choice where choice_text like 'Just hacking%';

查询并排序

# 这里的-我没明白什么意思[TODO]
Question.objects.order_by('-pub_date')
# 等价于
select question_text from polls_question order by pub_date;