ORM对表操作详解
目录
表结构
-
作者表,作者详细信息表,出版社表,书籍表
from django.db import models
# 作者表(一对一作者详细信息表) #比较常用的信息放到这个表里面
class Author(models.Model):
name = models.CharField(max_length=32, verbose_name='作者姓名')
age = models.IntegerField(verbose_name='年龄')
# authorDetail=models.OneToOneField(to="AuthorDetail",to_field="nid",on_delete=models.CASCADE)
# #to关联表,to_field指定字段,on_delete设置级联删除
authorDetail = models.OneToOneField(to='AuthorDetail', verbose_name='作者详细信息')
# 一对一到AuthorDetail表 生成为表字段之后,会自动变为authorDetail_id这样有一个名称
# OneToOneField(一对一关系) 相当于 foreign+unique
def __str__(self):
return self.name
# 作者详细信息表
class AuthorDetail(models.Model):
birthday = models.DateField(verbose_name='出生日期')
# telephone=models.BigIntegerField()#不方便查询,比如模糊查询,占资源比较多
telephone = models.CharField(max_length=32, verbose_name='电话') # CharField推荐使用来存储电话
addr = models.CharField(max_length=64, verbose_name='住址')
def __str__(self):
return self.addr
# 出版社表 #(一对多 书籍表)是
class Publish(models.Model):
name = models.CharField(max_length=32, verbose_name='出版社名称')
city = models.CharField(max_length=32, verbose_name='出版社所在城市')
email = models.EmailField(verbose_name='出班社邮箱') # EmailField本质上是charfield做了一些邮箱规则的效验
def __str__(self):
return self.name
# 书籍表
class Book(models.Model):
nid = models.AutoField(primary_key=True)
title = models.CharField(max_length=32, verbose_name='书名')
publishDate = models.DateField(verbose_name='出版日期')
price = models.DecimalField(max_digits=5, decimal_places=2,
verbose_name='价格') # max_digits小数总位数,decimal_places小数部分位数
publishs = models.ForeignKey(to="Publish", verbose_name='出版社')
authors = models.ManyToManyField(to='Author', verbose_name='作者')
def __str__(self):
return self.title
ORM对表的 增 删 改 查
-
增加
一对一
models.Author.objects.create(name='xx',age=18,authorDetail=mdoels.AuthorDetail.Objects.get(id=1))
models.Author.objects.create(name='xx',age=18,authorDetail_id=2)
一对多
models.Book.objects.create(xx=xx,publishs=mdoels.Publish.Objects.get(id=1))
models.Book.objects.create(xx=xx,publishs_id=2)
多对多
book_obj = models.Book.objects.get(id=1)
book_obj.authors.add(*[author_obj1,author_obj2,..])
book_obj.authors.add(*[1,2,3...])
-
删
一对一
models.Author.objects.filter(id=1).delete()
一对多
models.Book.objects.filter(id=1).delete()
多对多
book_obj = models.Book.objects.get(id=1)
book_obj.authors.remove(1,2,3,4)
book_obj.authors.remove(*[1,2,...])
book_obj.authors.clear()
book_obj.authors.set(['1','2',...]) :clear -- add
-
改
一对一
models.Author.objects.filter(id=1).update(
authorDetail=mdoels.AuthorDetail.Objects.get(id=1)
)
models.Author.objects.filter(id=1).update(
authorDetail_id=2,
)
一对多
models.Book.objects.filter(id=1).update(
publishs=mdoels.Publish.Objects.get(id=1)
)
models.Book.objects.filter(id=1).update(
publishs_id=2,
)
多对多
book_obj.authors.set(['1','2',...]) :clear -- add
基于对象的跨表查询 -- 类似于子查询
-
正向查询和反向查询
-
关系属性(字段)写在哪个类(表)里面,从当前类(表)的数据去查询它关联类(表)的数据叫做正向查询,反之叫做反向查询
#查询
# 一对一
# 正向查询
#1 查询崔老师的电话号
# author_obj = models.Author.objects.filter(name='崔老师').first()
# # print(author_obj.authorDetail) #辽宁峨眉山
# # print(author_obj.authorDetail.telephone) #444
# #2 反向查询
# #2 查询一下这个444电话号是谁的.
# author_detail_obj = models.AuthorDetail.objects.get(telephone='444')
# print(author_detail_obj.author) #崔老师
# print(author_detail_obj.author.name) #崔老师
''' 正向查询:Authorobj.authorDetail,对象.关联属性名称
Author----------------------------------->AuthorDetail
<-----------------------------------
反向查询:AuthorDetailobj.author ,对象.小写类名
'''
# 一对多
# 查询一下李帅的床头故事这本书的出版社是哪个
# 正向查询
book_obj = models.Book.objects.get(title='李帅的床头故事')
print(book_obj.publishs) #B哥出版社
print(book_obj.publishs.name) #B哥出版社
# B哥出版社出版社出版了哪些书
# 反向查询
pub_obj = models.Publish.objects.get(name='B哥出版社')
print(pub_obj.book_set.all()) #<QuerySet [<Book: 李帅的床头故事>, <Book: 李帅的床头故事2>]>
''' 正向查询 book_obj.publishs 对象.属性
Book ---------------------------------------------> Publish
<----------------------------------------------
反向查询 publish_obj.book_set.all() 对象.表名小写_set
'''
# 多对多
# 李帅的床头故事这本书是谁写的
# 正向查询
book_obj = models.Book.objects.get(title='李帅的床头故事')
print(book_obj.authors.all())
# 高杰写了哪些书
author_obj = models.Author.objects.get(name='高杰')
print(author_obj.book_set.all())
''' 正向查询 book_obj.authors.all() 对象.属性
Book ---------------------------------------------> Author
<----------------------------------------------
反向查询 author_obj.book_set.all() 对象.表名小写_set
'''
基于双下划的跨表查询 -- 连表 join
- 正向查询和反向查询
#查询
# 一对一
# 1. 查询崔老师的电话号
# 方式1 正向查询
# obj = models.Author.objects.filter(name='崔老师').values('authorDetail__telephone')
# print(obj) #<QuerySet [{'authorDetail__telephone': '444'}]>
# 方式2 反向查询
# obj = models.AuthorDetail.objects.filter(author__name='崔老师').values('telephone','author__age')
# print(obj) #<QuerySet [{'telephone': '444'}]>
# 2. 哪个老师的电话是444
# 正向
# obj = models.Author.objects.filter(authorDetail__telephone='444').values('name')
# print(obj)
# 反向
# obj = models.AuthorDetail.objects.filter(telephone='444').values('author__name')
# print(obj)
# 一对多
# 查询一下李帅的床头故事这本书的出版社是哪个
# obj = models.Book.objects.filter(title='李帅的床头故事').values('publishs__name')
# print(obj) #<QuerySet [{'publishs__name': 'B哥出版社'}]>
# obj = models.Publish.objects.filter(book__title='李帅的床头故事').values('name')
# obj = models.Publish.objects.filter(xx__title='李帅的床头故事').values('name')
# print(obj)
# B哥出版社出版社出版了哪些书
# obj = models.Publish.objects.filter(name='B哥出版社').values('book__title')
# print(obj) #<QuerySet [{'book__title': '李帅的床头故事'}, {'book__title': '李帅的床头故事2'}]>
# obj = models.Book.objects.filter(publishs__name='B哥出版社').values('title')
# print(obj) #<QuerySet [{'title': '李帅的床头故事'}, {'title': '李帅的床头故事2'}]>
# 李帅的床头故事这本书是谁写的
# obj = models.Book.objects.filter(title='李帅的床头故事').values('authors__name')
# print(obj)
# obj = models.Author.objects.filter(book__title='李帅的床头故事').values('name')
# print(obj) #<QuerySet [{'name': '高杰'}, {'name': '崔老师'}]>
#高杰写了哪些书
# obj = models.Book.objects.filter(authors__name='高杰').values('title')
# print(obj)
# obj = models.Author.objects.filter(name='高杰').values('book__title')
# print(obj)
#进阶的
# B哥出版社 出版的书的名称以及作者的名字
# obj = models.Book.objects.filter(publishs__name='B哥出版社').values('title','authors__name')
# print(obj)
#<QuerySet [{'title': '李帅的床头故事', 'authors__name': '高杰'}, {'title': '李帅的床头故事', 'authors__name': '崔老师'}, {'title': '李帅的床头故事2', 'authors__name': '崔老师'}, {'title': '李帅的床头故事2', 'authors__name': '王涛'}]>
'''
SELECT app01_book.title,app01_author.name from app01_publish INNER JOIN app01_book on app01_publish.id=app01_book.publishs_id
INNER JOIN app01_book_authors on app01_book.nid = app01_book_authors.book_id INNER JOIN app01_author
ON app01_author.id = app01_book_authors.author_id where app01_publish.name='B哥出版社';
:param request:
:return:
'''
# obj = models.Publish.objects.filter(name='B哥出版社').values('book__title','book__authors__name')
# print(obj)
# obj = models.Author.objects.filter(book__publishs__name='B哥出版社').values('name','book__title')
# print(obj)
# authorDetail author book publish
# 手机号以4开头的作者出版过的所有书籍名称以及出版社名称
# ret = models.AuthorDetail.objects.filter(telephone__startswith='4').values('author__book__title','author__book__publishs__name')
# print(ret)
#QuerySet [{'author__book__title': '李帅的床头故事', 'author__book__publishs__name': 'B哥出版社'}, {'author__book__title': '李帅的床头故事2', 'author__book__publishs__name': 'B哥出版社'}]>
#查询一下B哥出版社出版了哪些书
# obj = models.Publish.objects.filter(name='B哥出版社').first()
# print(obj.xx.all())
ORM对表的操作示例
操作示例 点击查看(代码)
System.out.println("Hello to see U!");
```python
1.1 一对一增加
new_author_detail = models.AuthorDetail.objects.create(
birthday='1979-08-08',
telephone='138383838',
addr='黑龙江哈尔滨'
)
obj = models.AuthorDetail.objects.filter(addr='山西临汾').first()
方式1
models.Author.objects.create(
name='王涛',
age='40',
authorDetail=new_author_detail,
)
方式2 常用
models.Author.objects.create(
name='王涛',
age='40',
authorDetail_id=obj.id,
)
1.2 一对多增加
方式1
obj = models.Publish.objects.get(id=2)
models.Book.objects.create(
title = '李帅的床头故事',
publishDate='2019-07-22',
price=3,
# publishs=models.Publish.objects.get(id=1),
publishs=obj,
)
方式2 常用
models.Book.objects.create(
title='李帅的床头故事2',
publishDate='2019-07-21',
price=3.5,
# publishs=models.Publish.objects.get(id=1),
publishs_id=obj.id
)
1.3 多对多增加
方式1 常用(效率高)
book_obj = models.Book.objects.get(nid=1)
book_obj.authors.add(*[1,2])
方式2
author1 = models.Author.objects.get(id=1)
author2 = models.Author.objects.get(id=3)
book_obj = models.Book.objects.get(nid=5)
book_obj.authors.add(*[author1,author2])
### ORM对表的 删 操作
```python
#一对一 和 一对多 的删除和单表删除是一样的.
#一对一 表一外键关联到表二,表一删除,不影响表2,表2删除会影响表1
models.AuthorDetail.objects.get(id=2).delete()
models.Author.objects.get(id=3).delete()
一对多
models.Publish.objects.get(id=1).delete()
models.Book.objects.get(nid=1).delete()
多对多关系删除
book_obj = models.Book.objects.get(nid=6)
book_obj.authors.remove(6) #authors是表中的关联对象 删除单个
book_obj.authors.remove(*[5,6]) #删除多个
book_obj.authors.clear() #全部关联对象删除
book_obj.authors.add(*[1,]) #添加
book_obj.authors.set('1') #删除之前关系添加新关系
book_obj.authors.set(['5','6']) #删除然后更新 多个
ORM对表的 更新 操作
一对一 更新
models.Author.objects.filter(id=5).update(
name='崔老师',
age=16,
# authorDetail=models.AuthorDetail.objects.get(id=5),
authorDetail_id=4,
)
一对多 更新
models.Book.objects.filter(pk=4).update(
title='B哥的往事2',
# publishs=models.Publish.objects.get(id=3),
publishs_id=3,
)
#一对多
models.Publish.objects.filter(pk=2).update(
id=4, # 没有级联更新,报错!!
)
ORM对表的 查询 操作(重点 )
#查询
# 一对一
# 1. 查询崔老师的电话号
# 方式1 正向查询
# obj = models.Author.objects.filter(name='崔老师').values('authorDetail__telephone')
# print(obj) #<QuerySet [{'authorDetail__telephone': '444'}]>
#方式2 反向查询
# obj = models.AuthorDetail.objects.filter(author__name='崔老师').values('telephone','author__age')
# print(obj) #<QuerySet [{'telephone': '444'}]>
# 2. 哪个老师的电话是444
# 正向
# obj = models.Author.objects.filter(authorDetail__telephone='444').values('name')
# print(obj)
# 反向
# obj = models.AuthorDetail.objects.filter(telephone='444').values('author__name')
# print(obj)
# 一对多
# 查询一下李帅的床头故事这本书的出版社是哪个
# obj = models.Book.objects.filter(title='李帅的床头故事').values('publishs__name')
# print(obj)
# obj = models.Publish.objects.filter(book__title='李帅的床头故事').values('name')
# print(obj)
# B哥出版社出版社出版了哪些书
# obj = models.Publish.objects.filter(name='B哥出版社').values('book__title')
# print(obj) #<QuerySet [{'book__title': '李帅的床头故事'}, {'book__title': '李帅的床头故事2'}]>
# obj = models.Book.objects.filter(publishs__name='B哥出版社').values('title')
# print(obj) #<QuerySet [{'title': '李帅的床头故事'}, {'title': '李帅的床头故事2'}]>
# 李帅的床头故事这本书是谁写的
# obj = models.Book.objects.filter(title='李帅的床头故事').values('authors__name')
# print(obj)
# obj = models.Author.objects.filter(book__title='李帅的床头故事').values('name')
# print(obj) #<QuerySet [{'name': '高杰'}, {'name': '崔老师'}]>
# 高杰写了哪些书
# obj = models.Book.objects.filter(authors__name='高杰').values('title')
# print(obj)
# obj = models.Author.objects.filter(name='高杰').values('book__title')
# print(obj)
# 进阶的
# B哥出版社 出版的书的名称以及作者的名字
# obj = models.Book.objects.filter(publishs__name='B哥出版社').values('title','authors__name')
# print(obj)
# <QuerySet [{'title': '李帅的床头故事', 'authors__name': '高杰'}, {'title': '李帅的床头故事', 'authors__name': '崔老师'}, {'title': '李帅的床头故事2', 'authors__name': '崔老师'}, {'title': '李帅的床头故事2', 'authors__name': '王涛'}]>
'''
SELECT app01_book.title,app01_author.name from app01_publish INNER JOIN app01_book on app01_publish.id=app01_book.publishs_id
INNER JOIN app01_book_authors on app01_book.nid = app01_book_authors.book_id INNER JOIN app01_author
ON app01_author.id = app01_book_authors.author_id where app01_publish.name='B哥出版社';
:param request:
:return:
'''
# obj = models.Publish.objects.filter(name='B哥出版社').values('book__title','book__authors__name')
# print(obj)
# obj = models.Author.objects.filter(book__publishs__name='B哥出版社').values('name','book__title')
# print(obj)
# authorDetail author book publish
# 手机号以4开头的作者出版过的所有书籍名称以及出版社名称
# ret = models.AuthorDetail.objects.filter(telephone__startswith='4').values('author__book__title','author__book__publishs__name')
# print(ret)
# QuerySet [{'author__book__title': '李帅的床头故事', 'author__book__publishs__name': 'B哥出版社'}, {'author__book__title': '李帅的床头故事2', 'author__book__publishs__name': 'B哥出版社'}]>
# 查询一下B哥出版社出版了哪些书
# obj = models.Publish.objects.filter(name='B哥出版社').first()
# print(obj.xx.all())
正向查 与 反向查
-
正向查反向查(从设置表关系的那张表查另一张就是正向,反之就是反向查)
related_name 给反向查询的小写表名起别名,以后反向查询调用都用这个别名
