ORM分组操作示例(与SQL语句的比较)

单表操作

建表:

class Employee(models.Model):
    name = models.CharField(max_length=16)
    age = models.IntegerField()
    salary = models.IntegerField()
    province = models.CharField(max_length=32)
    dept = models.CharField(max_length=16)

    def __str__(self):
        return self.name

    class Meta:
        db_table = "employee"

ORM分组操作示例(与SQL语句的比较)

  操作:

我们使用原生SQL语句,按照部分分组求平均工资:

select dept,AVG(salary) from employee group by dept;

ORM语句与SQL语句对应关系:

ORM分组操作示例(与SQL语句的比较)

ORM查询:

  ret = models.Employee.objects.all()
    print(ret)#<QuerySet [<Employee: 小黑>, <Employee: 小白>, <Employee: 赵导>, <Employee: 化工哥>]>
              #(0.003) SELECT `employee`.`id`, `employee`.`name`, `employee`.`age`, `employee`.`salary`, `employee`.`province`, `employee`.`dept` 
         FROM `employee` LIMIT 21; args=()
    ret = models.Employee.objects.values("dept")
    print(ret)
    # (0.002)  SELECT `employee`.`dept` FROM `employee` LIMIT  21; args = ()
    # < QuerySet[{‘dept‘: ‘保安部‘}, {‘dept‘: ‘影视部‘}, {‘dept‘: ‘影视部‘}, {‘dept‘: ‘福利部‘}] >
 ret = models.Employee.objects.values("dept").annotate(avg=Avg("salary")).values("dept","avg")
    print(ret)
    #(0.068) SELECT `employee`.`dept`, AVG(`employee`.`salary`) AS `avg` FROM `employee` GROUP BY `employee`.`dept` ORDER BY NULL LIMIT 21; 
    #<QuerySet [{‘dept‘: ‘保安部‘, ‘avg‘: 2000.0}, {‘dept‘: ‘影视部‘, ‘avg‘: 6500.0}, {‘dept‘: ‘福利部‘, ‘avg‘: 8000.0}]> 

多表操作

建表:

class Employee2(models.Model):
    name = models.CharField(max_length=16)
    age = models.IntegerField()
    salary = models.IntegerField()
    province = models.CharField(max_length=32)
    dept = models.ForeignKey(to="Dept")

    def __str__(self):
        return self.name

    class Meta:
        db_table = "employee2"


class Dept(models.Model):
    name = models.CharField(max_length=16, unique=True)

    def __str__(self):
        return self.name

    class Meta:
        db_table = "dept2"

ORM分组操作示例(与SQL语句的比较)

  SQL查询:

select dept2.name,AVG(salary) from employee2 inner join dept2 on (employee2.dept_id=dept2.id) group by dept_id;

ORM查询:

from django.db.models import Avg
ret = models.Employee2.objects.values("dept_id").annotate(avg=Avg("salary")).values("dept__name","avg")
print(ret)
# < QuerySet[{‘dept__name‘: ‘保安部‘, ‘avg‘: 2000.0}, {‘dept__name‘: ‘影视部‘, ‘avg‘: 6500.0}, {‘dept__name‘: ‘福利部‘, ‘avg‘: 8000.0}] >
# (0.089) SELECT `dept2`.`name`,AVG(`employee2`.`salary`) AS  `avg` FROM `employee2` INNER JOIN `dept2` ON(`employee2`.`dept_id` = `dept2`.id`) 
GROUP BY `employee2`.`dept_id`,`dept2`.`name` ORDER BY NULL LIMIT 21;args = ()
# 查所有的员工和部门名称
    ret = models.Employee2.objects.values("name", "dept__name")
    print(ret)
    #(0.012) SELECT `employee2`.`name`, `dept2`.`name` FROM `employee2` INNER JOIN `dept2` ON (`employee2`.`dept_id` = `dept2`.`id`) LIMIT 21;
    #<QuerySet [{‘name‘: ‘小黑‘, ‘dept__name‘: ‘保安部‘}, {‘name‘: ‘小白‘, ‘dept__name‘: ‘影视部‘}, {‘name‘: ‘赵导‘, ‘dept__name‘: ‘影视部‘},
{‘name‘: ‘化工哥‘, ‘dept__name‘: ‘福利部‘}]>
select_relatedprefetch_related 的使用
def select_related(self, *fields)
    性能相关:表之间进行join连表操作,一次性获取关联的数据。

    总结:
    1. select_related主要针一对一和多对一关系进行优化。
    2. select_related使用SQL的JOIN语句进行优化,通过减少SQL查询的次数来进行优化、提高性能。

def prefetch_related(self, *lookups)
    性能相关:多表连表操作时速度会慢,使用其执行多次SQL查询在Python代码中实现连表操作。

    总结:
    1. 对于多对多字段(ManyToManyField)和一对多字段,可以使用prefetch_related()来进行优化。
    2. prefetch_related()的优化方式是分别查询每个表,然后用Python处理他们之间的关系。
select_related的使用示例
 #select_related的使用:表之间进行join连表操作,一次性获取关联的数据。
    ret = models.Employee2.objects.select_related()
    print(ret)
    #(0.019) SELECT `employee2`.`id`, `employee2`.`name`, `employee2`.`age`, `employee2`.`salary`, `employee2`.`province`, `employee2`.`dept_id`,
`dept2`.`id`, `dept2`.`name` FROM `employee2` INNER JOIN `dept2` ON (`employee2`.`dept_id` = `dept2`.`id`) LIMIT 21; args=()
#<QuerySet [<Employee2: 小黑>, <Employee2: 小白>, <Employee2: 赵导>, <Employee2: 化工哥>]> ret = models.Employee2.objects.select_related().values("name","dept__name") print(ret) #(0.020) SELECT `employee2`.`name`, `dept2`.`name` FROM `employee2` INNER JOIN `dept2` ON (`employee2`.`dept_id` = `dept2`.`id`) LIMIT 21; #<QuerySet [{‘name‘: ‘小黑‘, ‘dept__name‘: ‘保安部‘}, {‘name‘: ‘小白‘, ‘dept__name‘: ‘影视部‘}, {‘name‘: ‘赵导‘, ‘dept__name‘: ‘影视部‘},
{‘name‘: ‘化工哥‘, ‘dept__name‘: ‘福利部‘}]>
  建立多对多关系表:
class Author(models.Model):
    name = models.CharField(max_length=32)
    books = models.ManyToManyField(to="Book")

    def __str__(self):
        return self.name

    class Meta:
        db_table = "author"

class Book(models.Model):
    title = models.CharField(max_length=32)

    def __str__(self):
        return self.title

    class Meta:
        db_table = "book"

ORM分组操作示例(与SQL语句的比较) 

 ret = models.Author.objects.select_related("books__title").values("name", "books__title")
    print(ret)
    #(0.014) SELECT `author`.`name`, `book`.`title` FROM `author` LEFT OUTER JOIN `author_books` ON (`author`.`id` = `author_books`.`author_id`)
LEFT OUTER JOIN `book` ON (`author_books`.`book_id` = `book`.`id`) LIMIT 21; args=()
#<QuerySet [{‘name‘: ‘小黑‘, ‘books__title‘: ‘沙河出版社‘}, {‘name‘: ‘小白‘, ‘books__title‘: ‘沙河出版社‘}, {‘name‘: ‘小黑‘,
‘books__title‘: ‘光子出版社‘}, {‘name‘: ‘小黄‘, ‘books__title‘: ‘光子出版社‘}, {‘name‘: ‘小黑‘, ‘books__title‘: ‘番茄物语‘},
{‘name‘: ‘小白‘, ‘books__title‘: ‘番茄物语‘}, {‘name‘: ‘小黄‘, ‘books__title‘: ‘番茄物语‘}]>

批量操作

def bulk_create(self, objs, batch_size=None):
    # 批量插入
    # batch_size表示一次插入的个数
    objs = [
        models.DDD(name=r11),
        models.DDD(name=r22)
    ]
    models.DDD.objects.bulk_create(objs, 10)

示例:

    # 批量创建
    # 有100个书籍对象
    objs = [models.Book(title="沙河{}".format(i)) for i in range(6)]
    #
    # 在数据库中批量创建, 2次一提交
    models.Book.objects.bulk_create(objs, 2)

ORM分组操作示例(与SQL语句的比较) 

ORM分组操作示例(与SQL语句的比较)

上一篇:CodeForces 478B 第六周比赛B题


下一篇:pl/sql简单执行记录