Django数据库基本API示例

数据模型创建后，Django会自动给予一套数据库抽象API，允许增删改查对象。在Django实际代码中，这些应该都是写在views.py内的。
官方文档：https://docs.djangoproject.com/zh-hans/4.1/topics/db/queries/

进入交互式命令行

1
2
3
4
python
 manage.
py
shell

导入模型

1
2
3
4
5
6
7
8
from
 polls.models 
import
 Choice, Question
from
 django.utils 
import
 timezone

新增记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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
?
','
-01
-04
:
:
.068086
');

所有查询记录

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

1
2
3
4
5
6
Question
.objects.
all
()
# 等价于
# select question_text from polls_question;

按字段查询

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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?"
;

模糊查找

1
2
3
4
5
6
7
8
9
10
11
12
13
Question.objects.filter(question_text__startswith=
'What'
)
# 等价于
select
 question_text 
from
 polls_question 
where
 question_text 
like
'how%'
;

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

只返回单个结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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(
)))
;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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'
);

查找记录数量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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'
 ;

连接查询

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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(
)))
;

模糊查询并删除记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
q.choice_set.
filter
(choice_text__startswith=
'Just hacking'
).
delete
()
# 等价于
delete
from
 polls_choice 
where
 choice_text 
like
'Just hacking%'
;

查询并排序

1
2
3
4
5
6
7
8
9
10
11
12
# 这里的-我没明白什么意思[TODO]
Question.objects.order_by(
'-pub_date'
)
# 等价于
select
 question_text 
from
 polls_question 
order
by
 pub_date;
Dev
#Python
Django数据库基本API示例
https://zhouyinglin.top/2023/01/05/Django数据库基本API示例/
作者
小周
发布于
2023年1月5日
许可协议