The path to learning in Python -2018/6/27

The path to learning in Python -2018/6/27 1. Multiple table operations add a record to add a many-to-many relationship

Method One: Incoming author object

= Book.objects.get(bid=1= Author.objects.get(name="gy"= Author.objects.get(name="yq")book.authors.add(gy, yq)

Operation Result:

Mode two: Incoming author primary key

= Book.objects.get(bid=2)book.authors.add(34)

Operation Result:

To remove a many-to-many relationship

= Book.objects.get(bid=1)book.authors.remove(2)  # 移除单个

Operation Result:

= Book.objects.get(bid=2)book.authors.clear()  # 移除所有

Operation Result:

2. Cross-table queries

Cross-Table query:

1. Object-based query
2. Query based on double underline
3. Aggregation and Grouping queries
4. F and Q query

Object-based one-to-many queries (book and Publish)

A-B, associated properties in table A

Forward query: A----->b, query by field

# 查询《坏蛋是怎样炼成的》这本书的出版社>>>= Book.objects.get(title="坏蛋是怎样炼成的")>>>print(book.publish)中国城市出版社

Reverse query: B----->a, by table name (lowercase) _set.all () query

# 查询中国城市出版社所有书籍>>>= Publish.objects.get(name="中国城市出版社")>>>= publish.book_set.all()>>>print(book_list)<QuerySet [<Book: 坏蛋是怎样炼成的><Book: 斗罗大陆>]>

One-to-one queries (Author and Authordetail)

Forward query:

# 查询作者gy的电话号码>>>= Author.objects.get(name="gy")>>>print(gy.author_detail.telephone)123

Reverse query: By table name

# 查询电话号码是456的作者名字>>>= AuthorDetail.objects.get(telephone=456)>>>print(author_detail.author.name)yq

Many-to-many queries (book and Author)

Forward query:

# 查询《斗罗大陆》作者名字和生活的城市>>>= Book.objects.get(title="斗罗大陆")>>>= book_obj.authors.all()>>>forin authors:        print(i.name, i.author_detail.addr)糖加三勺 北京

Reverse query:

# 查询天蚕土豆出过的书籍名字>>>= Author.objects.get(name="天蚕土豆")>>>= author.book_set.all()>>>print(books)<QuerySet [<Book: 斗破苍穹>]>

One-to-many queries based on double underlines

Forward query:

# 查询《坏蛋是怎样炼成的》这本书的出版社>>> Book.objects.filter(title="坏蛋是怎样炼成的").values("publish__name")<QuerySet [{‘publish__name‘‘中国城市出版社‘}]>

Reverse query:

# 查询《坏蛋是怎样炼成的》这本书的出版社>>> Publish.objects.filter(book__title="坏蛋是怎样炼成的").values("name")<QuerySet [{‘publish__name‘‘中国城市出版社‘}]>

Many-to-many queries

Forward query:

# 查询《斗罗大陆》作者名字和生活的城市>>> Book.objects.filter(title="斗罗大陆").values("authors__name",                         "authors__author_detail__addr")<QuerySet [{‘authors__name‘‘糖加三勺‘‘authors__author_detail__addr‘‘北京‘}]>

Reverse query:

# 查询《斗罗大陆》作者名字和生活的城市>>> Author.objects.filter(book__title="斗罗大陆").values("name",                         "author_detail__addr")<QuerySet [{‘name‘‘糖加三勺‘‘author_detail__addr‘‘北京‘}]>

One-to-one query

Forward Query

# 查询作者gy的电话号码>>> Author.objects.filter(name="gy").values("author_detail__telephone")<QuerySet [{‘author_detail__telephone‘123}]>

Reverse Query

# 查询作者gy的电话号码>>>= AuthorDetail.objects.filter(author__name="gy").values("telephone")<QuerySet [{‘telephone‘123}]>

Continuous cross-table

Forward Query

# 查询手机号以10开头的作者出版过的所有书籍名称以及出版社名称>>>  Book.objects.filter(authors__author_detail__telephone__startswith=10).values("title",          "publish__name")       <QuerySet [{‘title‘‘斗破苍穹‘‘publish__name‘‘武汉大学出版社‘}, {‘title‘‘武动乾坤‘‘publish__name‘‘北京大学出版社‘}]>

Reverse Query

# 查询手机号以10开头的作者出版过的所有书籍名称以及出版社名称>>> Author.objects.filter(author_detail__telephone__startswith=10).values("book__title",           "book__publish__name")<QuerySet [{‘book__title‘‘斗破苍穹‘‘book__publish__name‘‘武汉大学出版社‘}, {‘book__title‘‘武动乾坤‘‘book__publish__name‘‘北京大学出版社‘}]>

Aggregate queries

Aggregate, the return value is no longer queryset, but the dictionary

>>>fromimport Avg, Max, Min, Count>>> Book.objects.aggregate(Avg("price"), Max("price"), Min("price"), Count("price")){‘price__avg‘136.4‘price__max‘: Decimal(‘159.00‘‘price__min‘: Decimal(‘125.00‘‘price__count‘5}

Group Query Single Table query

Annotate, the return value is still queryset

Data:

# 查询每个职业的平均工资>>> Employee.objects.values("dep").annotate(Avg("salary"))<QuerySet [{‘dep‘‘副手‘‘salary__avg‘4000.0}, {‘dep‘‘船员‘‘salary__avg‘3500.0}, {‘dep‘‘船长‘‘salary__avg‘4950.0}]>

Multi-table Group query

# Check the name of each publisher and the number of books published>>>Publish.objects.values ("pid"). Annotate (c=Count ("Book__title"). VALUES ("Name","C")<QuerySet [{' name ':' China City Press ',' C ':2}, {' name ':' Wuhan University Press ',' C ':2}, {' name ':' Peking University Press ',' C ':1}]># Query The name of each author and the highest price of published books>>>Author.objects.values ("Nid"). Annotate (max_p=Max ("Book__price"). VALUES ("Name","Max_p")<QuerySet [{' name ':' Gy ',' max_p ': Decimal (' 120.00 ')}, {' name ':' Yq ',' max_p ': Decimal (' 126.00 ')}, {' name ':' sugar plus three spoons ',' max_p ': Decimal (' 125.00 ')}, {' name ':' Tian silkworm potatoes ',' max_p ': Decimal (' 125.00 ')}]># Query The name of each book and the corresponding number of authors>>>Book.objects.values ("Bid"). Annotate (Author_count=Count ("Authors__nid"). VALUES ("title","Author_count")<QuerySet [{' title ':' How the villain is tempered ',' Author_count ':1}, {' title ':' Break the Heavens ',' Author_count ':1}, {' title ':' bucket continent ',' Author_count ':1}, {' title ':' The movement of the Universe ',' Author_count ':1}, {' title ':' devour the Stars ',' Author_count ':1}]>

The path to learning in Python -2018/6/27