ORM 對錶操作 詳解
- 2019 年 10 月 8 日
- 筆記
目錄
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對錶的 更新 操作 ```python 一對一 更新 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對錶的 查詢 操作(重點 ) ```python #查詢 # 一對一 # 1. 查詢崔老師的電話號 # 方式1 正向查詢 # obj = models.Author.objects.filter(name='崔老師').values('authorDetail__telephone') # print(obj) # #方式2 反向查詢 # obj = models.AuthorDetail.objects.filter(author__name='崔老師').values('telephone','author__age') # print(obj) # # 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) # # obj = models.Book.objects.filter(publishs__name='B哥出版社').values('title') # print(obj) # # 李帥的床頭故事這本書是誰寫的 # obj = models.Book.objects.filter(title='李帥的床頭故事').values('authors__name') # print(obj) # obj = models.Author.objects.filter(book__title='李帥的床頭故事').values('name') # print(obj) # # 高傑寫了哪些書 # 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) # ''' 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 給反向查詢的小寫表名起別名,以後反向查詢調用都用這個別名
作 者：郭楷豐
