Django model ORM数据表相关操作
分析思路,创建数据表
对于表操作,表之间的关联关系,必须理解他们之间的关系,对于编程很重要。可以看看映射关系、外键和relationship查询 ,至少明白外键相关基本知识。
(一)多表查询
一对一:models.OneToOneField(to_field='id',to='Authordatil')
一对多:(外键设置唯一性)
models.ForeignKey(to='Publish',to_field='id')
多对多:自动生成第三张表
models.ManyToManyField(to='Author')
(二)创建表模型
作者与详情 ( 一对一 )
书与作者( 多对多 )
出版社与书(一对多 )
出版社:id name add email
书: id name price publish
作者: id name sex authordatil
详情: id photo address
models.py
文件,创建数据模型
class Publish(models.Model):
name = models.CharField(max_length=100)
address = models.CharField(max_length=64)
email = models.EmailField()
def __str__(self):
return '%s,%s,%s' % (self.name, self.address, self.email)
class Author(models.Model):
name = models.CharField(max_length=32)
choices = ((0, '女'), (1, '男'))
sex = models.SmallIntegerField(choices, default=0) # 数字整型
# 作者与详情:(一对一) to='AuthorDetail' 加引号,这个表能找到就可以,不用引号,类必须在上面定义
authordatil = models.OneToOneField(to_field='id', to='Authordatil', on_delete=models.CASCADE)
def __str__(self):
return self.name
class Authordatil(models.Model):
photo = models.CharField(max_length=124)
address = models.CharField(max_length=64)
class Book(models.Model):
name = models.CharField(max_length=32)
price = models.DecimalField(max_digits=5, decimal_places=2)
# 出版社与书(一对多关系)外检设置唯一性,关联的表,关联的字段
publish = models.ForeignKey(to='Publish', to_field='id', on_delete=models.CASCADE)
# 书与作者关系是多对多关系
authors = models.ManyToManyField(to='Author')
- 对于外键字段,Django 会在字段名上添加"_id" 来创建数据库中的列名
- 外键字段 ForeignKey 有一个 null=True 的设置(它允许外键接受空值 NULL),你可以赋给它空值 None 。
一.添加/删除/修改表记录
1.1一对多添加记录
一对多添加记录需要先添加父表(Publish)记录,再添加子表(Book)记录,反过来则会报错
简单测试,直接添加Book,views中定义一个方法:
def add_book(request):
Book.objects.create(name='红楼梦', price=20.0, publish_id=1)
return HttpResponse('成功插入一条Book记录!')
看看控制台,会报错了
'django.db.utils.IntegrityError: FOREIGN KEY constraint failed初步判定主键约束错误,也就是说publish_id 对应的Publish不存在。那就先添加几个Publish吧
这里有数据库建表的相关文章,不明白的同学可以看看:
1.1.1 views中定义一个方法:
def add_publish(request):
Publish.objects.create(name='北京出版社', address='北京', email='itjavawfc@163.com')
Publish.objects.create(name='安徽出版社', address='安徽', email='itjavawfc@163.com')
Publish.objects.create(name='人教版出版社', address='北京', email='itjavawfc@163.com')
li=[]
for i in range(5):
li.append(Publish(name='新华出版社%s' % i, address='北京', email='itjavawfc@163.com')) # 批量生成对象
Publish.objects.bulk_create(li)
return HttpResponse('成功插入Publish记录!')
查看数据库信息:
1.1.2 出版社Publish数据有了,现在继续测试添加Book功能了
def add_book(request):
Book.objects.create(name='红楼梦', price=20.0, publish_id=1)
return HttpResponse('成功插入一条Book记录!')
查看数据库数据,可以看到数据已经成功插入了。
简单理解刚才的问题: 主键约束了之后,插入book前提是已经有了外键的数据,publish_id,也就是有一个Publish记录,这样才可以插入Book记录,且publish_id正好是Publish记录中对应的一条记录。
通过对象添加记录
首先得到一个publish对象,然后作为参数直接插入到Book表中,看看数据库信息。
def add_book_by_publish(request):
publish = Publish.objects.filter(pk=1).first()
Book.objects.create(name='红楼梦', price=34.5, publish=publish)
return HttpResponse('通过对象插入一条记录!')
pk 表示主键,一旦主键改变,还是会找到对应的主键来获取对象,如果通过主键查询道德数据为null,那么表示不存在这个记录,也就无法正常插入Book了。
2. 一对多删除记录
- 删除数据时,先删除子表(bokk)记录,再删除父表(publish)记录;否则是会报错的。这里就不演示了。
下面是直接删除book数据的接口函数
def del_book(request):
Book.objects.filter(name='红楼梦').first().delete()
return HttpResponse('删除记录!')
3.一对多修改记录
- 通过queryset对象的update方法修改
- 通过对象的属性修改,调用对象的save()方法保存
- 通过queryset对象的update方法修改
方式一:通过 _id 修改
def up_date_book(request):
Book.objects.filter(pk=2).update(name='西游记', publish_id=1)
return HttpResponse('通过ID修改!')
方式二:通过 对象修改修改
def up_date_book_by_publish(request):
publish = Publish.objects.filter(pk=1).first()
Book.objects.filter(pk=2).update(name='东游记', price=100.0, publish=publish)
return HttpResponse('通过对象修改!')
3.1通过对象的属性修改,调用对象的save()方法保存
方式一:通过 _id 修改
def up_date_book_by_id_book(request):
book = Book.objects.filter(pk=4).first()
book.publish_id = 104
book.price = 200.0
book.save()
return HttpResponse('upDateBookByIDBook!')
方式二:通过对象修改
def up_date_book_by_book(request):
book = models.Book.objects.filter(pk=4).first()
publish = models.Publish.objects.filter(pk=105).first()
book.publish = publish
book.save()
return HttpResponse('upDateBookBook!')
4.多对多增删改记录
先准备测试用的数据
def add_author_detail(request):
Authordatil.objects.create(photo='www.baidu.com/author1.png', address='天上人间1包间')
Authordatil.objects.create(photo='www.baidu.com/author2.png', address='天上人间2包间')
Authordatil.objects.create(photo='www.baidu.com/author3.png', address='天上人间3包间')
Authordatil.objects.create(photo='www.baidu.com/author4.png', address='天上人间4包间')
return HttpResponse('添加AuthorDetail')
def add_author(request):
Author.objects.create(name='tom', sex=0,authordatil_id=1)
Author.objects.create(name='tony', sex=1,authordatil_id=2)
Author.objects.create(name='jack', sex=1,authordatil_id=3)
Author.objects.create(name='ant', sex=0,authordatil_id=4)
return HttpResponse('添加addAuthor')
4.1多对多增加记录
(1)通过对象添加记录
多对多
- 添加数据时,先添加父表记录(book,author),再添加子表(book_author_relation)记录
- 删除数据时,先删除子表记录(book_author_relation),再删除父表记录(book,author)
现在就要为这边《东游记》这本书添加作者
add()
def add_author_by_book(request):
tom = Author.objects.filter(name='tom').first()
book = Book.objects.filter(name='东游记').first()
tony = Author.objects.filter(name='tony').first()
jack = Author.objects.filter(name='jack').first()
book.authors.add(tom)
# 一次添加两个作者,中间用逗号隔开,若为列表的话传参时需要展开*[]
book.authors.add(tony, jack)
return HttpResponse('给书添加作者')
看数据库数据表: books_book_authors
(2)通过 id 添加记录
直接上代码
def add_author_by_book_by_id(request):
# 为红楼梦这本书添加一个作者
book = Book.objects.filter(name='东游记').first()
# 一次添加两个作者,中间用逗号隔开
book.authors.add(4)
return HttpResponse('addAuthorByBookById')
也就是说,得到了book之后,直接通过作者的id作为参数,找到了作者,然后直接添加,内部django帮助封装了一层其实。
4.2 多对多删除记录
4.2.1 通过对象删除记录
remove()
def del_book_of_author(request):
# 删除东游记作者名字是tom的作者
tom = Author.objects.filter(name='tom').first()
book = Book.objects.filter(name='东游记').first()
book.authors.remove(tom)
return HttpResponse('delBookOfAuthor')
4.2.2 通过id删除记录
def del_book_of_author_by_id(request):
# 删除东游记作者id是3、4的两个作者
book = Book.objects.filter(name='东游记').first()
book.authors.remove(3,4)
return HttpResponse('delBookOfAuthorByID')
4.3 多对多清空记录
clear()
def clear_book_author(request):
book = Book.objects.filter(name='东游记').first()
book.authors.clear()
return HttpResponse('delBookOfAllAuthor')
4.4 多对多修改记录
set()
4.4.1 通过对象修改记录
def update_book_author(request):
tom = Author.objects.filter(name='tom').first()
book = Book.objects.filter(name='东游记').first()
book.authors.set([tom, ])
return HttpResponse('updateBookAuthor')
4.4.2 通过id修改记录
def update_book_author_id(request):
# 修改东游记作者为作者id是3、4的作者
book = Book.objects.filter(name='东游记').first()
book.authors.set([3,4 ])
return HttpResponse('updateBookAuthorID')
二、基于对象的跨表查询–多次查询、子查询
正向查询和反向查询
正向查询:关联字段在从表,由从表查询主表中的数据 -----> 按字段查询
反向查询:关联字段在从表,由主表查询从表中的数据 -----> 按表名小写查询
1、一对一基于对象的跨表查询
正向:正向查询按 字段
反向:反向查询按 表名小写
def find_book_and_publish(request):
# 1.正向 查询东游记这本书的出版社邮箱
book = Book.objects.filter(name='东游记').first()
# book.publish 就是出版社对象
pulish = book.publish
print(pulish.email)
# 2.反向 查询地址是北京的出版社出版的图书
publish = Publish.objects.filter(address='北京').first()
# publish.book_set.all() 拿出所有的图书
books = publish.book_set.all()
print(books)
# 统计一下条数
books = publish.book_set.all().count()
print(books)
return HttpResponse('findBookAndPublish')
查询结果
jonhson@163.com
<QuerySet []>
0
2、多对多基于对象的跨表查询
正向:正向查询按 字段.all()
反向:反向按 表名小写_set.all()
def find_book_and_publish_more_to_more(request):
# 1.查询红楼梦这本书所有的作者
book = Book.objects.filter(name='东游记').first()
book.authors.all() # 是所有的作者,是一个queryset对象,可以继续点
print(book.authors.all())
# 2.查询lqz写的所有书
lqz = Author.objects.filter(name='jack').first()
books = lqz.book_set.all()
print(books)
print(books.values())
return HttpResponse('findBookAndPublishMoreToMore')
查询结果
<QuerySet [<Author: jack>, <Author: ant>]>
<QuerySet [<Book: Book object (2)>]>
<QuerySet [{'id': 2, 'name': '东游记', 'price': Decimal('200.00'), 'publish_id': 3}]>
三、基于双下划线的跨表查询— 连标 join
1、一对一的基于双下划线的跨表查询
正向:按 字段,跨表可以在filter,也可以在values中
反向:按 表名小写,跨表可以在filter,也可以在values中
def find_book_author_detail(request):
# 1.查询tom作者的照片 正向查询 跨表的话,按字段
# 以author表作为基表
ret = Author.objects.filter(name='tom').values('authordatil__photo')
print(ret)
# 2.以authordetail作为基表 反向查询,按表名小写 跨表的话,用表名小写
ret = Authordatil.objects.filter(author__name='tom').values('photo')
print(ret)
return HttpResponse('find_book_author_detail')
<QuerySet [{'authordatil__photo': 'www.baidu.com/author1.png'}]>
<QuerySet [{'photo': 'www.baidu.com/author1.png'}]>
2、多对多的基于双下划线的跨表查询
正向:按 字段,跨表可以在filter,也可以在values中
反向:按 表名小写,跨表可以在filter,也可以在values中
def find_book_author(request):
# 查询东游记的所有作者名字
# 1.以Book为基表
ret = Book.objects.filter(name='东游记').values('authors__name')
print(ret)
# 2.以Author为基表
ret = Author.objects.filter(book__name='东游记').values('name')
print(ret)
return HttpResponse('find_book_author')
<QuerySet [{'authors__name': 'jack'}, {'authors__name': 'ant'}]>
<QuerySet [{'name': 'jack'}, {'name': 'ant'}]>
3、连续跨表查询
一直用双下划线 __ 获取字段
def find_book_author_detail_photo(request):
# 查询东游记这本书所有的作者的照片
book = Book.objects.filter(name='东游记').first()
authors = book.authors.all()
for author in authors:
author_detail = author.authordatil
print(author_detail.photo)
# 方法二,使用连续跨表
ret = Book.objects.filter(name='东游记').values('authors__authordatil__photo')
print(ret)
return HttpResponse('findBookTyp3')
www.baidu.com/author3.png
www.baidu.com/author4.png
<QuerySet [{'authors__authordatil__photo': 'www.baidu.com/author3.png'}, {'authors__authordatil__photo': 'www.baidu.com/author4.png'}]>
四.文章所用到的code
gitee demo:https://gitee.com/wo_long_gang/django-demo-book-mall
from django.http import HttpResponse
from django.views import View
from books.models import Book, Publish, Authordatil, Author
def add_book(request):
Book.objects.create(name='红楼梦', price=20.0, publish_id=1)
return HttpResponse('成功插入一条Book记录!')
def add_publish(request):
Publish.objects.create(name='北京出版社', address='北京', email='jonhson@163.com')
Publish.objects.create(name='安徽出版社', address='安徽', email='jonhson@163.com')
Publish.objects.create(name='人教版出版社', address='北京', email='jonhson@163.com')
li = []
for i in range(5):
li.append(Publish(name='新华出版社%s' % i, address='北京', email='jonhson@163.com')) # 批量生成对象
Publish.objects.bulk_create(li)
return HttpResponse('成功插入Publish记录!')
def add_book_by_publish(request):
publish = Publish.objects.filter(pk=1).first()
Book.objects.create(name='红楼梦', price=34.5, publish=publish)
return HttpResponse('通过对象插入一条记录!')
def del_book(request):
Book.objects.filter(name='红楼梦').first().delete()
return HttpResponse('删除记录!')
def up_date_book(request):
Book.objects.filter(pk=2).update(name='西游记', publish_id=1)
return HttpResponse('通过ID修改!')
def up_date_book_by_publish(request):
publish = Publish.objects.filter(pk=1).first()
Book.objects.filter(pk=2).update(name='东游记', price=100.0, publish=publish)
return HttpResponse('通过对象修改!')
def up_date_book_by_id_book(request):
book = Book.objects.filter(pk=2).first()
book.publish_id = 3
book.price = 200.0
book.save()
return HttpResponse('upDateBookByIDBook!')
def add_author_detail(request):
Authordatil.objects.create(photo='www.baidu.com/author1.png', address='天上人间1包间')
Authordatil.objects.create(photo='www.baidu.com/author2.png', address='天上人间2包间')
Authordatil.objects.create(photo='www.baidu.com/author3.png', address='天上人间3包间')
Authordatil.objects.create(photo='www.baidu.com/author4.png', address='天上人间4包间')
return HttpResponse('添加AuthorDetail')
def add_author(request):
Author.objects.create(name='tom', sex=0, authordatil_id=1)
Author.objects.create(name='tony', sex=1, authordatil_id=2)
Author.objects.create(name='jack', sex=1, authordatil_id=3)
Author.objects.create(name='ant', sex=0, authordatil_id=4)
return HttpResponse('添加addAuthor')
def add_author_by_book(request):
tom = Author.objects.filter(name='tom').first()
book = Book.objects.filter(name='东游记').first()
tony = Author.objects.filter(name='tony').first()
jack = Author.objects.filter(name='jack').first()
book.authors.add(tom)
# 一次添加两个作者,中间用逗号隔开
book.authors.add(tony, jack)
return HttpResponse('给书添加作者')
def add_author_by_book_by_id(request):
# 为红楼梦这本书添加一个作者
book = Book.objects.filter(name='东游记').first()
# 一次添加两个作者,中间用逗号隔开
book.authors.add(4)
return HttpResponse('addAuthorByBookById')
def del_book_of_author(request):
# 删除东游记作者名字是tom的作者
tom = Author.objects.filter(name='tom').first()
book = Book.objects.filter(name='东游记').first()
book.authors.remove(tom)
return HttpResponse('delBookOfAuthor')
def del_book_of_author_by_id(request):
# 删除东游记作者id是3、4的两个作者
book = Book.objects.filter(name='东游记').first()
book.authors.remove(3, 4)
return HttpResponse('delBookOfAuthorByID')
def clear_book_author(request):
book = Book.objects.filter(name='东游记').first()
book.authors.clear()
return HttpResponse('delBookOfAllAuthor')
def update_book_author(request):
tom = Author.objects.filter(name='tom').first()
book = Book.objects.filter(name='东游记').first()
book.authors.set([tom, ])
return HttpResponse('updateBookAuthor')
def update_book_author_id(request):
# 修改东游记作者为作者id是3、4的作者
book = Book.objects.filter(name='东游记').first()
book.authors.set([3, 4])
return HttpResponse('updateBookAuthorID')
def find_book_and_publish(request):
# 1.正向 查询东游记这本书的出版社邮箱
book = Book.objects.filter(name='东游记').first()
# book.publish 就是出版社对象
pulish = book.publish
print(pulish.email)
# 2.反向 查询地址是北京的出版社出版的图书
publish = Publish.objects.filter(address='北京').first()
# publish.book_set.all() 拿出所有的图书
books = publish.book_set.all()
print(books)
# 统计一下条数
books = publish.book_set.all().count()
print(books)
return HttpResponse('findBookAndPublish')
def find_book_and_publish_more_to_more(request):
# 1.查询红楼梦这本书所有的作者
book = Book.objects.filter(name='东游记').first()
book.authors.all() # 是所有的作者,是一个queryset对象,可以继续点
print(book.authors.all())
# 2.查询lqz写的所有书
lqz = Author.objects.filter(name='jack').first()
books = lqz.book_set.all()
print(books)
print(books.values())
return HttpResponse('findBookAndPublishMoreToMore')
def find_book_author_detail(request):
# 1.查询tom作者的照片 正向查询 跨表的话,按字段
# 以author表作为基表
ret = Author.objects.filter(name='tom').values('authordatil__photo')
print(ret)
# 2.以authordetail作为基表 反向查询,按表名小写 跨表的话,用表名小写
ret = Authordatil.objects.filter(author__name='tom').values('photo')
print(ret)
return HttpResponse('find_book_author_detail')
def find_book_author(request):
# 查询东游记的所有作者名字
# 1.以Book为基表
ret = Book.objects.filter(name='东游记').values('authors__name')
print(ret)
# 2.以Author为基表
ret = Author.objects.filter(book__name='东游记').values('name')
print(ret)
return HttpResponse('find_book_author')
def find_book_author_detail_photo(request):
# 查询东游记这本书所有的作者的照片
book = Book.objects.filter(name='东游记').first()
authors = book.authors.all()
for author in authors:
author_detail = author.authordatil
print(author_detail.photo)
# 方法二,使用连续跨表
ret = Book.objects.filter(name='东游记').values('authors__authordatil__photo')
print(ret)
return HttpResponse('findBookTyp3')