django orm关联查询_django orm 多表查询

#直接导入django可以查看并用于django包相关模块,但无法使用django创建的项目中的包

importdjangoimportos

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "duobiaochaxun.settings")

django.setup()from app.models importBook, AuthorDetail, Author, Publish#一对多关系(publish和book book依赖于publish)

#增加

publish = Publish.objects.create(name='老男孩出版社', address='上海浦东').id

publish1= Publish.objects.create(name='小女孩出版社', address='上海虹桥').id

publish2= Publish.objects.create(name='金沙江出版社', address='北京朝阳').id

Book.objects.create(name='西游记', price='33.33', publish_date='2018-01-01', publish_id=publish2)

Book.objects.create(name='东游记', price='44.44', publish_date='2018-02-02', publish_id=publish2)

Book.objects.create(name='爱丽莎', price='55.55', publish_date='2018-03-03', publish_id=publish1)

Book.objects.create(name='顾杜友', price='66.66', publish_date='2018-04-04', publish_id=publish1)

Book.objects.create(name='拍死你', price='77.77', publish_date='2018-05-05', publish_id=publish)

Book.objects.create(name='牛尼斯', price='88.88', publish_date='2018-06-06', publish_id=publish)

一对一关系 Author依赖于AuthorDetail

detail= AuthorDetail.objects.create(age=20, telephone=13333334444, info='Owen简介')

detail1= AuthorDetail.objects.create(age=19, telephone=14356789900, info='Zero简介')

detail2= AuthorDetail.objects.create(age=20, telephone=16678906677, info='Egon简介')

detail3= AuthorDetail.objects.create(age=21, telephone=18900123456, info='Lxx简介')

detail4= AuthorDetail.objects.create(age=17, telephone=16875435678, info='Yhh简介')

Author.objects.create(name='Owen', author_detail=detail)

Author.objects.create(name='Zero', author_detail=detail1)

Author.objects.create(name='Egon', author_detail=detail2)

Author.objects.create(name='Lxx', author_detail=detail3)

Author.objects.create(name='Yhh', author_detail=detail4)

多对多关系

b1=Book.objects.first()

b2= Book.objects.all()[1]

b3= Book.objects.all()[2]

b4= Book.objects.all()[3]

b5= Book.objects.all()[4]

b6=Book.objects.last()

a1=Author.objects.first()

a2= Author.objects.all()[1]

a3= Author.objects.all()[2]

a4= Author.objects.all()[3]

a5=Author.objects.last()

b1.author.add(a1, a2)

b2.author.add(a1, a2, a5)

b3.author.add(a4)

b4.author.add(a4)

b5.author.add(a1, a3, a4)

b6.author.add(a4, a5)#地址在北京的出版社出版的书名#价格超过50元的书籍的出版社名

books = Book.objects.filter(publish__address__contains='北京')for book inbooks:print(book.name)

publish= Publish.objects.filter(book__price__gt=50).distinct()for p inpublish:print(p.name)#获取所有名字里包含字母o(不区分大小写)作者的电话号码#年龄是20岁作者的作者名

authors = AuthorDetail.objects.filter(author__name__icontains='o')for author inauthors:print(author.telephone)

author= Author.objects.filter(author_detail__age=20)for age inauthor:print(age.name)#-- 获取第三位作者出版过的书的书名#-- 获取最后一本书作者们的简介

author = Author.objects.all()[2]for book inauthor.book_set.all():print(book.name)

authors=Book.objects.last().author.all()for author inauthors:print(author.author_detail.info)#获取第一个出版社出版的最近一次出版的书的作者们的详情

authors = Publish.objects.first().book_set.all().order_by('publish_date').last().author.all()for author inauthors:print(author.author_detail.info)#1. 找到第一个出版社 2.找到最后一本书 3.找到作者们

authors = Publish.objects.first().book_set.all().order_by('publish_date').last().author.all()for author inauthors:print(author.name)#-- 获取地址在上海的出版社名，与出版过的书名#-- 获取2018年出版的书名、书的价格与出版社名

publish = Publish.objects.filter(address__contains='上海').values('name','book__name')print(publish)

books= Book.objects.filter(publish_date__year=2018).values('name', 'price', 'publish__name')print(books)#-- 获取年龄小于20岁作者的名字、年龄、电话与简介

print(Author.objects.filter(author_detail__age__lt=20).values('name', 'author_detail__age', 'author_detail__info'))#-- 获取名字中包含e(不区分大小写)的作者出版过的书的书名与价格(需要去重)#-- 获取书籍价格不超过50元的作者名与作者电话(需要去重)

n_pr = Author.objects.filter(name__icontains='e').values('name','book__name', 'book__price').distinct()print(n_pr)#name_tep = Book.objects.filter(price__lte=50).values('author__name', 'author__author_detail__telephone').distinct()print(name_tep)#-- 获取在老男孩出版过书的年龄最大的作者的作者名、年龄、电话与简介

info= Author.objects.filter(book__publish__name__contains='老男孩')\

.values('name', 'author_detail__age', 'author_detail__telephone', 'author_detail__info', 'book__publish__name')\

.order_by('author_detail__age').last()print(info)