django的orm操作

orm的分组查询的一些用法

class User(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=20, null=False, blank=False)
    pwd = models.CharField(max_length=50, null=False, blank=False)

    def __str__(self):
        return self.name


class Question(models.Model):
    id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=30, null=False, blank=False)
    content = models.TextField(null=False, blank=False)
    create_time = models.DateTimeField(default=datetime.now)
    author = models.ForeignKey(User, on_delete=models.CASCADE)

    def __str__(self):
        return self.title


class Answer(models.Model):
    id = models.AutoField(primary_key=True)
    content = models.CharField(max_length=50, null=False, blank=False)
    create_time = models.DateTimeField(default=datetime.now)
    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    author = models.ForeignKey(User, on_delete=models.CASCADE)

    def __str__(self):
        return self.content


class Author(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=20, null=False, blank=False)
    pwd = models.CharField(max_length=50, default='123456', null=False, blank=False)

    def __str__(self):
        return self.name


class Book(models.Model):
    id = models.AutoField(primary_key=True)
    b_name = models.CharField(max_length=10, null=False, blank=False)
    b_content = models.CharField(max_length=100, null=False, blank=False)
    author = models.ManyToManyField(Author)

    def __str__(self):
        return self.b_name

>>> from db_test.models import Author, Answer, User, Question, Book

>>> Author.objects.all()
<QuerySet [<Author: author.1>, <Author: author.2>, <Author: author.3>, <Author: author.4>]>

>>> Author.objects.all().values()
<QuerySet [{'id': 1, 'name': 'author.1', 'pwd': ''}, {'id': 2, 'name': 'author.2', 'pwd': ''}, {'id': 3, 'name': 'author.3', 'pwd': ''}, {'id': 4, 'name': 'author.4', 'pwd': ''}]>

>>> Author.objects.all().values('id', 'name')
<QuerySet [{'id': 1, 'name': 'author.1'}, {'id': 2, 'name': 'author.2'}, {'id': 3, 'name': 'author.3'}, {'id': 4, 'name': 'author.4'}]>

>>> from django.db import connection
>>> connection.queries[-1]
{'sql': 'SELECT "db_test_author"."id", "db_test_author"."name" FROM "db_test_author"  LIMIT 21', 'time': '0.000'}

>>> Author.objects.all().values('name')
<QuerySet [{'name': 'author.1'}, {'name': 'author.2'}, {'name': 'author.3'}, {'name': 'author.4'}]>
>>> connection.queries[-1]
{'sql': 'SELECT "db_test_author"."name" FROM "db_test_author"  LIMIT 21', 'time': '0.000'}

>>> Author.objects.all().values_list('name')
<QuerySet [('author.1',), ('author.2',), ('author.3',), ('author.4',)]>
>>> Author.objects.all().values_list('name').all()
<QuerySet [('author.1',), ('author.2',), ('author.3',), ('author.4',)]>
>>> connection.queries[-1]
{'sql': 'SELECT "db_test_author"."name" FROM "db_test_author"  LIMIT 21', 'time': '0.001'}

>>> from django.db.models import *

>>> Book.objects.all().aggregate(作者最多的书籍=Max('author'))
{'作者最多的书籍': 3}

>>> Book.objects.all().aggregate(作者最多的书籍=Max('author')).values('id', 'b_name')
Traceback (most recent call last):
  File "<input>", line 1, in <module>
TypeError: values() takes no arguments (2 given)

>>> Book.objects.all().values('id','b_name').aggregate(作者最多的书籍=Max('author'))
{'作者最多的书籍': 3}

>>> Book.objects.all()
<QuerySet [<Book: book.1>, <Book: book.2>, <Book: book.3>]>

>>> Book.objects.all().values('id','b_name').aggregate(作者最多的书籍=Max('author'))
{'作者最多的书籍': 3}
>>> connection.queries[-1]
{'sql': 'SELECT MAX("db_test_book_author"."author_id") AS "作者最多的书籍" FROM "db_test_book" LEFT OUTER JOIN "db_test_book_author" ON ("db_test_book"."id" = "db_test_book_author"."book_id")', 'time': '0.001'}

>>> Book.objects.all().values('id','b_name').aggregate(作者最多的书籍=Max('author'))
{'作者最多的书籍': 3}

>>> Book.objects.all().values('id','b_name').aggregate(作者最多的书籍=Count('author'))
{'作者最多的书籍': 6}
>>> connection.queries[-1]
{'sql': 'SELECT COUNT("db_test_book_author"."author_id") AS "作者最多的书籍" FROM "db_test_book" LEFT OUTER JOIN "db_test_book_author" ON ("db_test_book"."id" = "db_test_book_author"."book_id")', 'time': '0.000'}

>>> Book.objects.all().values('id','b_name').aggregate(作者最多的书籍=Count('author')).values()
dict_values([6])

>>> Book.objects.all().values('id','b_name').annotate(作者最多的书籍=Count('author'))
<QuerySet [{'id': 1, 'b_name': 'book.1', '作者最多的书籍': 1}, {'id': 2, 'b_name': 'book.2', '作者最多的书籍': 3}, {'id': 3, 'b_name': 'book.3', '作者最多的书籍': 2}]>
>>> connection.queries[-1]
{'sql': 'SELECT "db_test_book"."id", "db_test_book"."b_name", COUNT("db_test_book_author"."author_id") AS "作者最多的书籍" FROM "db_test_book" LEFT OUTER JOIN "db_test_book_author" ON ("db_test_book"."id" = "db_test_book_author"."book_id") GROUP BY "db_test_book"."id", "db_test_book"."b_name"  LIMIT 21', 'time': '0.001'}

>>> Book.objects.all().values('id','b_name').annotate(作者数量=Count('author'))
<QuerySet [{'id': 1, 'b_name': 'book.1', '作者数量': 1}, {'id': 2, 'b_name': 'book.2', '作者数量': 3}, {'id': 3, 'b_name': 'book.3', '作者数量': 2}]>

>>> Book.objects.all().annotate(作者数量=Count('author'))
<QuerySet [<Book: book.1>, <Book: book.2>, <Book: book.3>]>
>>> connection.queries[-1]
{'sql': 'SELECT "db_test_book"."id", "db_test_book"."b_name", "db_test_book"."b_content", COUNT("db_test_book_author"."author_id") AS "作者数量" FROM "db_test_book" LEFT OUTER JOIN "db_test_book_author" ON ("db_test_book"."id" = "db_test_book_author"."book_id") GROUP BY "db_test_book"."id", "db_test_book"."b_name", "db_test_book"."b_content"  LIMIT 21', 'time': '0.000'}

>>> Book.objects.all().annotate(作者数量=Count('author')).values()
<QuerySet [{'id': 1, 'b_name': 'book.1', 'b_content': 'book.1.content', '作者数量': 1}, {'id': 2, 'b_name': 'book.2', 'b_content': 'book.2.content', '作者数量': 3}, {'id': 3, 'b_name': 'book.3', 'b_content': 'book.3.content', '作者数量': 2}]>
>>> connection.queries[-1]
{'sql': 'SELECT "db_test_book"."id", "db_test_book"."b_name", "db_test_book"."b_content", COUNT("db_test_book_author"."author_id") AS "作者数量" FROM "db_test_book" LEFT OUTER JOIN "db_test_book_author" ON ("db_test_book"."id" = "db_test_book_author"."book_id") GROUP BY "db_test_book"."id", "db_test_book"."b_name", "db_test_book"."b_content"  LIMIT 21', 'time': '0.000'}

>>> Book.objects.all().annotate(作者数量=Count('author')).values('id', 'name', '作者数量')
Traceback (most recent call last):
  File "E:\Envs\Django_\lib\site-packages\django\db\models\sql\query.py", line 1768, in add_fields
    join_info = self.setup_joins(name.split(LOOKUP_SEP), opts, alias, allow_many=allow_m2m)
  File "E:\Envs\Django_\lib\site-packages\django\db\models\sql\query.py", line 1504, in setup_joins
    names[:pivot], opts, allow_many, fail_on_missing=True,
  File "E:\Envs\Django_\lib\site-packages\django\db\models\sql\query.py", line 1420, in names_to_path
    "Choices are: %s" % (name, ", ".join(available)))
django.core.exceptions.FieldError: Cannot resolve keyword 'name' into field. Choices are: author, b_content, b_name, id, 作者数量

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "E:\Envs\Django_\lib\site-packages\django\db\models\query.py", line 798, in values
    clone = self._values(*fields, **expressions)
  File "E:\Envs\Django_\lib\site-packages\django\db\models\query.py", line 793, in _values
    clone.query.set_values(fields)
  File "E:\Envs\Django_\lib\site-packages\django\db\models\sql\query.py", line 2020, in set_values
    self.add_fields(field_names, True)
  File "E:\Envs\Django_\lib\site-packages\django\db\models\sql\query.py", line 1791, in add_fields
    "Choices are: %s" % (name, ", ".join(names)))
django.core.exceptions.FieldError: Cannot resolve keyword 'name' into field. Choices are: author, b_content, b_name, id, 作者数量

>>> Book.objects.all().annotate(作者数量=Count('author')).values('id', 'b_name', '作者数量')
<QuerySet [{'id': 1, 'b_name': 'book.1', '作者数量': 1}, {'id': 2, 'b_name': 'book.2', '作者数量': 3}, {'id': 3, 'b_name': 'book.3', '作者数量': 2}]>
>>> connection.queries[-1]
{'sql': 'SELECT "db_test_book"."id", "db_test_book"."b_name", COUNT("db_test_book_author"."author_id") AS "作者数量" FROM "db_test_book" LEFT OUTER JOIN "db_test_book_author" ON ("db_test_book"."id" = "db_test_book_author"."book_id") GROUP BY "db_test_book"."id", "db_test_book"."b_name", "db_test_book"."b_content"  LIMIT 21', 'time': '0.001'}

>>> Book.objects.values('id').annotate(作者数量=Count('author')).values('id', 'b_name', '作者数量')
<QuerySet [{'id': 1, 'b_name': 'book.1', '作者数量': 1}, {'id': 2, 'b_name': 'book.2', '作者数量': 3}, {'id': 3, 'b_name': 'book.3', '作者数量': 2}]>
>>> connection.queries[-1]
{'sql': 'SELECT "db_test_book"."id", "db_test_book"."b_name", COUNT("db_test_book_author"."author_id") AS "作者数量" FROM "db_test_book" LEFT OUTER JOIN "db_test_book_author" ON ("db_test_book"."id" = "db_test_book_author"."book_id") GROUP BY "db_test_book"."id", "db_test_book"."b_name"  LIMIT 21', 'time': '0.000'}

>>> Book.objects.values('b_name').annotate(作者数量=Count('author')).values('id', 'b_name', '作者数量')
<QuerySet [{'id': 1, 'b_name': 'book.1', '作者数量': 1}, {'id': 2, 'b_name': 'book.2', '作者数量': 3}, {'id': 3, 'b_name': 'book.3', '作者数量': 2}]>
>>> connection.queries[-1]
{'sql': 'SELECT "db_test_book"."id", "db_test_book"."b_name", COUNT("db_test_book_author"."author_id") AS "作者数量" FROM "db_test_book" LEFT OUTER JOIN "db_test_book_author" ON ("db_test_book"."id" = "db_test_book_author"."book_id") GROUP BY "db_test_book"."b_name", "db_test_book"."id"  LIMIT 21', 'time': '0.000'}