Django终端打应SQL语句
# 在Django项目的settings.py文件中,在最后复制粘贴如下代码: LOGGING = { 'version': 1, 'disable_existing_loggers': False, 'handlers': { 'console':{ 'level':'DEBUG', 'class':'logging.StreamHandler', }, }, 'loggers': { 'django.db.backends': { 'handlers': ['console'], 'propagate': True, 'level':'DEBUG', }, } } # 即为你的Django项目配置上一个名为django.db.backends的logger实例即可查看翻译后的SQL语句。
在Python脚本中加载Django环境
import os if __name__ == '__main__': # 加载Django项目的配置信息 os.environ.setdefault("DJANGO_SETTINGS_MODULE", "web01.settings") # 导入Django,并启动Django项目 import django django.setup() from app01 import models books = models.Book.objects.all() print(books)
ORM练习
models.py,手动插入数据
from django.db import models # Create your models here. class Author(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) age = models.IntegerField() # 与AuthorDetail建立一对一的关系 authorDetail = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE) class AuthorDetail(models.Model): nid = models.AutoField(primary_key=True) birthday = models.DateField() telephone = models.BigIntegerField() addr = models.CharField(max_length=64) class Publish(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) city = models.CharField(max_length=32) email = models.EmailField() class Book(models.Model): nid = models.AutoField(primary_key=True) title = models.CharField(max_length=32) publishDate = models.DateField() price = models.DecimalField(max_digits=5, decimal_places=2) # 与Publish建立一对多的关系,外键字段建立在多的一方 publish = models.ForeignKey(to="Publish", to_field="nid", on_delete=models.CASCADE) # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个,自动创建第三张表 authors = models.ManyToManyField(to='Author', ) class Employee(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() salary = models.BigIntegerField() province = models.CharField(max_length=32) dept = models.ForeignKey(to="Dept") class Dept(models.Model): name = models.CharField(max_length=32)
test.py
from django.test import TestCase # Create your tests here. import os if __name__ == '__main__': os.environ.setdefault("DJANGO_SETTINGS_MODULE", "ormdemo.settings") import django django.setup() from app01 import models # #####################基于对象查询(子查询)############################## # 按字段(publish) # 一对多 book -----------------> publish # <----------------- # book_set.all() 正向查询按字段: 1.查询python这本书籍的出版社的邮箱 python = models.Book.objects.filter(title="python").first() print(python.publish.email) 反向查询按 表名小写_set.all() 2.苹果出版社出版的书籍名称 publish_obj = models.Publish.objects.filter(name="苹果出版社").first() for obj in publish_obj.book_set.all(): print(obj.title) 按字段(authors.all()) 多对多 book -----------------------> author <----------------------- book_set.all() 3. 查询python作者的年龄 python = models.Book.objects.filter(title="python").first() for author in python.authors.all(): print(author.name, author.age) 4. 查询alex出版过的书籍名称 alex = models.Author.objects.filter(name="alex").first() for book in alex.book_set.all(): print(book.title) 按字段 authorDetail 多对多 author ----------------> authordetail <---------------- 按表名 author 5.查询alex的手机号 alex = models.Author.objects.filter(name='alex').first() print(alex.authorDetail.telephone) 6.查询家在山东的作者名字 ad_list = models.AuthorDetail.objects.filter(addr="shandong") for ad in ad_list: print(ad.author.name) ''' 对应sql: select publish_id from Book where title="python" select email from Publish where nid = 1 ''' #####################基于queryset和__查询(join查询)############################ 正向查询:按字段 反向查询:表名小写 1.查询python这本书籍的出版社的邮箱 ret = models.Book.objects.filter(title="python").values("publish__email") print(ret.query) ''' select publish.email from Book left join Publish on book.publish_id=publish.nid where book.title="python" ''' 2.苹果出版社出版的书籍名称 方式1: ret1 = models.Publish.objects.filter(name="苹果出版社").values("book__title") print("111111111====>", ret1.query) 方式2: ret2 = models.Book.objects.filter(publish__name="苹果出版社").values("title") print("2222222222====>", ret2.query) 3.查询alex的手机号 方式1: ret = models.Author.objects.filter(name="alex").values("authorDetail__telephone") 方式2: models.AuthorDetail.objects.filter(author__name="alex").values("telephone") 4.查询手机号以151开头的作者出版过的书籍名称, 以及书籍对应的出版社名称 ret = models.Book.objects.filter(authors__authorDetail__telephone__startswith="151").values('title',"publish__name") print(ret.query) 聚合与分组 ---聚合--- from django.db.models import Avg, Sum, Count, Max 1. 查询所有书籍的价格总和 ret = models.Book.objects.aggregate(s=Sum("price")) print(ret) 2. 查询所有作者的平均年龄 ret = models.Author.objects.aggregate(a=Avg("age")) print(ret) ---分组--- 1. 查询每一个部门 名称以及对应人数 ret = models.Dept.objects.values('id').annotate(c=Count("employee__id")).values('name','c') print(ret.query) """ 关键点: 1. queryset对象.annotate(), 它才有这个方法 2. annotate进行分组统计,按前面的select的字段进行group by 3. annotate()返回值依然是queryset对象,增加了分组统计之后的键值对 """ 2. 查询每一个作者的名字 及出版过的书籍最高价格 ret = models.Author.objects.values("name").annotate(m=Max("book__price")).values("name","m") print(ret) 3. 查询每一个出版社出版过的书籍的平均价格 ret = models.Publish.objects.values("name").annotate(a=Avg("book__price")).values("name","a") print(ret) 4. 查询每一本书籍的作者个数 ret = models.Book.objects.values("title").annotate(c=Count("authors__name")).values("title","c") print(ret) 5. 查询每一个分类名称以及对应的文章数 ret = models.Ger ret = models.Category.object.values("title").annotate(c=Count("article__nid")).values("title","c") 6. 统计不止一个作者的图书名称 ret = models.Book.objects.values("title").annotate(c=Count("authors__name")).filter(c__gt=1) print(ret)