10.4ORM回顾!
2018-10-4 17:41:52
继续优化一下我的博客项目!!
贴上orm参考连接:https://www.cnblogs.com/yuanchenqi/articles/8963244.html
回顾ORM
其实就两种数据方式!
1. 基于对象的
就是通过对象来查找属性
# 正向查询按字段：
# 反向查询按 表名小写_set.all()
2. 基于query的
# 正向查询：按字段 反向查询：表名小写
方式实例:
models 类:
from django.db import models # Create your models here. from django.db import models # Create your models here. class Author(models.Model): nid = models.AutoField(primary_key=True) name=models.CharField( max_length=32) age=models.IntegerField() # 与AuthorDetail建立一对一的关系 authorDetail=models.OneToOneField(to="AuthorDetail",on_delete=models.CASCADE) class AuthorDetail(models.Model): nid = models.AutoField(primary_key=True) birthday=models.DateField() telephone=models.BigIntegerField() addr=models.CharField( max_length=64) class Publish(models.Model): nid = models.AutoField(primary_key=True) name=models.CharField( max_length=32) city=models.CharField( max_length=32) email=models.EmailField() class Book(models.Model): nid = models.AutoField(primary_key=True) title = models.CharField( max_length=32) publishDate=models.DateField() price=models.DecimalField(max_digits=5,decimal_places=2) # 与Publish建立一对多的关系,外键字段建立在多的一方 publish=models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE) # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表 authors=models.ManyToManyField(to='Author',)
放上 orm查询的类!有详细注释
from django.shortcuts import render,HttpResponse # Create your views here. from app01 import models def query(request): # #####################基于对象查询(子查询)############################## # 按字段（publish） # 一对多 book -----------------> publish # <---------------- # book_set.all() # 正向查询按字段： # 查询python这本书籍的出版社的邮箱 # python=models.Book.objects.filter(title="python").first() # print(python.publish.email) # 反向查询按 表名小写_set.all() # 苹果出版社出版的书籍名称 # publish_obj=models.Publish.objects.filter(name="苹果出版社").first() # for obj in publish_obj.book_set.all(): # print(obj.title) # 按字段（authors.all()） # 多对多 book -----------------------> author # <---------------- # book_set.all() # 查询python作者的年龄 # python = models.Book.objects.filter(title="python").first() # for author in python.authors.all(): # print(author.name ,author.age) # 查询alex出版过的书籍名称 # alex=models.Author.objects.filter(name="alex").first() # for book in alex.book_set.all(): # print(book.title) # 按字段 authorDetail # 一对一 author -----------------------> authordetail # <---------------- # 按表名 author # 查询alex的手机号 # alex=models.Author.objects.filter(name='alex').first() # print(alex.authorDetail.telephone) # 查询家在山东的作者名字 # ad_list=models.AuthorDetail.objects.filter(addr="shandong") # # for ad in ad_list: # print(ad.author.name) ''' 对应sql: select publish_id from Book where title="python" # 查询的nid = 1 select email from Publish where nid = 1 ''' # #####################基于queryset和__查询（join查询）############################ # 正向查询：按字段 反向查询：表名小写 # 查询python这本书籍的出版社的邮箱 # ret=models.Book.objects.filter(title="python").values("publish__email") # print(ret.query) # 打印这条query的sql语句 ''' select publish.email from Book left join Publish on book.publish_id=publish.nid where book.title="python" ''' # 苹果出版社出版的书籍名称 # 方式1： ret1=models.Publish.objects.filter(name="苹果出版社").values("book__title") print("111111111====>",ret1.query) #方式2： ret2=models.Book.objects.filter(publish__name="苹果出版社").values("title") print("2222222222====>", ret2.query) #查询alex的手机号 # 方式1： ret=models.Author.objects.filter(name="alex").values("authorDetail__telephone") # 方式2： models.AuthorDetail.objects.filter(author__name="alex").values("telephone") # 查询手机号以151开头的作者出版过的书籍名称以及书籍对应的出版社名称 # filter就是join on 操作 添加表 # 不要管sql语句和表多少,只要join好表 数据字段都可以直接values # 先有个基表 书, 然后最终要查手机号, 然后反向推出来需要的表,然后正向连表就好了 ret=models.Book.objects.filter(authors__authorDetail__telephone__startswith="151").values('title', "publish__name") print(ret.query) return HttpResponse("OK")
query的查询 只要连过的表就可以随意拿数据, 正向就用字段,反向就用表名小写! 注释很清楚
贴上笔记!
day81 sql: 单表处理： Book id title price publish email addr 1 python 100 苹果出版社 123@qq.com 北京 2 go 200 橘子出版社 234@qq.com 南京 3 java 211 橘子出版社 234@qq.com 南京 4 linux 22 橘子出版社 234@qq.com 南京 ###################一对多######################多表： Book id title price publish_id 1 python 100 1 2 go 200 2 3 java 211 2 4 linux 22 2 Publish id name email addr 1 苹果出版社 123@qq.com 北京 2 橘子出版社 234@qq.com 南京 结论: 一旦确定表关系是一对多，那么关联字段建在多的表中 book: Foreignkey publish_id refference Publish(id) #######################多对多################################# Book id title price publish_id 1 python 100 1 2 go 200 2 3 java 211 2 4 linux 22 2 Author id name age 1 alex 38 2 liwen 43 Author_Book id author_id book_id 1 1 1 2 1 2 3 2 2 结论；一旦是多对多的关系：需要建立第三张关系表 #######################一对一################################# Author id name age 1 alex 38 2 liwen 43 AuthorDetail id tel gf author_id(unique) 1 131 钢蛋 1 2 121 铁锤 2 SELECT "app01_book"."title" FROM "app01_publish" LEFT OUTER JOIN "app01_book" ON ("app01_publish"."nid" = "app01_book"."publish_id") WHERE "app01_publish"."name" = 苹果出版社 SELECT "app01_book"."title" FROM "app01_book" INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."nid") WHERE "app01_publish"."name" = 苹果出版社 SELECT "app01_book"."title", "app01_publish"."name" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."nid" = "app01_book_authors"."book_id") INNER JOIN "app01_author" ON ("app01_book_authors"."author_id" = "app01_author"."nid") INNER JOIN "app01_authordetail" ON ("app01_author"."authorDetail_id" = "app01_authordetail"."nid") INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."nid") WHERE "app01_authordetail"."telephone" LIKE 151% ESCAPE '\'
