更新时间:2021-12-10 08:23:29
表结构如下:
from django.db import models
class Person(models.Model):
name = models.CharField(max_length=32)
age = models.IntegerField()
birth = models.DateField(auto_now = True)
def __str__(self):
return f'<Person obj:{self.id}{self.name}>'
class Publisher(models.Model):
name = models.CharField(max_length=32)
def __str__(self):
return f'<Publisher obj:{self.id}{self.name}>'
class Book(models.Model):
name = models.CharField(max_length = 32)
price = models.DecimalField(max_digits=6,decimal_places=2)
sale = models.IntegerField(default=-1)
stock = models.IntegerField(default=-1)
publisher = models.ForeignKey(to='Publisher')
def __str__(self):
return f'<Book obj:{self.id}{self.name}>'
class Author(models.Model):
name = models.CharField(max_length=32)
books = models.ManyToManyField('Book')
def __str__(self):
return f'<Author obj:{self.id}{self.name}>'
models.Publisher.objects.all()
models.Publisher.objects.get(id=1)
models.Publisher.objects.filter(id=1,name='sss')
models.Publisher.objects.all().order_by('id')
pub_obj.id
pub_obj.name
book_obj.publisher //book所关联的出版社对象
book_obj.publisher.id //book所关联的出版社对象id
book_obj.publisher_id //book所关联的出版社对象id
author_obj.books //django封装的管理对象
author_obj.books.all() //作者管理的所有书籍的对象列表
models.Publisher.objects.create(name='新的出版社')
models.Bookj.objects.create(name='新的书名',publisher_id=1) //添加关联对象id
models.Bookj.objects.create(name='新的书名',publisher=pub_obj) //添加关联对象
autho_obj = models.Author.objects.create(name='新的作者')
autho_obj.books.set([1,2]) //关联对象id列表
models.Publisher.objects.get(id=1).delele() //删除特定对象
models.Publisher.objects.filter(name='xxx').delele() //批量删除
pub_obj.name = '新的名字'
pub_obj.save() //这种修改方式在数据库操作上会对所有字段重新赋值
book_obj.name = '新的书名'
book_obj.publisher = pub_obj //基于对象添加
# book_obj.publisher_id = pub_obj.id //基于关联对象id添加
book_obj.save()
author_obj.name='新的作者名' //修改字段
author_obj.save()
author_obj.books.set([1,2,3]) //修改多对多关系
ret = models.Person.objects.all()
ret = models.Person.objects.get(id= 2)
ret = models.Person.objects.filter(name='王计飞')
ret = models.Person.objects.filter(name='哈哈')
ret = models.Author.objects.exclude(id=1)
ret = models.Publisher.objects.values('id','name')
ret = models.Book.objects.values_list('id','name','price','sale','stock','publisher_id')
ret = models.Book.objects.all().order_by('sale')
ret = models.Book.objects.all().order_by('price').reverse()
ret = models.Book.objects.values().distinct()
ret = models.Publisher.objects.all().count()
ret = models.Book.objects.first()
ret = models.Book.objects.last()
ret = models.Book.objects.filter(name='awefefe').exists()
all()
filter()
exclude()
order_by()
reverse()
distinct()
values() 返回一个可迭代的字典序列
values_list() 返回一个可迭代的元祖序列
get()
first()
last()
exists()
count()
ret = models.Book.objects.filter(price__lt=50,price__gt=20) <大于小于>
ret = models.Book.objects.filter(price__in=[66]) <等于>
ret = models.Book.objects.exclude(price__in=[66]) <不等于>
ret = models.Book.objects.filter(name__contains='飞') <包含>
ret = models.Book.objects.filter(name__icontains='飞') <不区分大小写>
ret = models.Book.objects.filter(sale__range=[50,100]) <范围>
ret = models.Person.objects.filter(birth__year=1993) <date字段>
类似的还有:startswith,istartswith, endswith, iendswith
对象查找(跨表查询)
语法>>>(对象.关联字段.字段)
ret = models.Book.objects.first().publisher
ret = models.Book.objects.first().publisher.id
ret = models.Book.objects.first().publisher.name
字段查找(跨表查询)
语法>>>(关联字段__字段)
ret = models.Book.objects.values('publisher__id','publisher__name')
ret = models.Book.objects.values_list('publisher__id','publisher__name')
对象查找(跨表查询)
语法>>>(obj.表名_set)
//在Book表中创建外键时没有指定related_name
ret = models.Publisher.objects.get(id = 3).book_set.all()
ret = models.Publisher.objects.filter(id = 3)[0].book_set.all()
//在Book表中创建外键时指定related_name='books'
ret = models.Publisher.objects.get(id = 3).books.all()
字段查找(跨表查询)
语法>>>(表名__字段)
ret = models.Publisher.objects.values('name','book__name')
ret = models.Publisher.objects.values_list('name','book__name')
概念:"关联管理器"是在一对多或者多对多的关联上下文中使用的管理器。它存在于下面两种情况:1、外键关系的反向查询。 2、多对多关联关系。简单来说就是当 点后面的对象 可能存在多个的时候就可以使用以下的方法。
ret = models.Book.objects.create(name='跟帮政扫黄',price=34,publisher_id=4)
ret = models.Author.objects.first().books.create(name = '追风筝的人',publisher_id =1,price = 56)
添加对象>>> author_objs = models.Author.objects.filter(id__lt=3)
添加对象>>> models.Book.objects.first().authors.add(*author_objs)
添加id>>> models.Book.objects.first().authors.add(*[1, 2])
book_obj = models.Book.objects.first()
book_obj.authors.set([2, 3])
book_obj = models.Book.objects.first()
book_obj.authors.remove(3)
book_obj = models.Book.objects.first()
book_obj.authors.clear()
aggregate()是QuerySet 的一个终止子句,意思是说,它返回一个包含一些键值对的字典。键的名称是聚合值的标识符,值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。
ret = models.Book.objects.aggregate(Sum('price'),Count('id'),Max('price'),Min('price'),average = Avg('price'))
ret = models.Book.objects.all().annotate(author_num = Count('author')) for i in ret: print([i.name](http://i.name),i.author_num)
方法一:
ret = models.Publisher.objects.all().annotate(cheaper = Min('book__price')).values('name','cheaper')
方法二:
ret = models.Book.objects.values_list('publisher__name').annotate(cheaper = Min('price'))
ret = models.Book.objects.all().annotate(num = Count('author')).filter(num__gt =1)
ret = models.Book.objects.all().annotate(num = Count('author')).order_by('-num')
查询各个作者出的书的总价格
ret = models.Author.objects.annotate(sum_price = Sum('books__price')).values_list('name','sum_price')
Django 提供 F() 来做对两个字段的值的比较。F() 的实例可以在查询中引用字段,来比较同一个 model 实例中两个不同字段的值。
ret = models.Book.objects.filter(sale__gt=F('stock')).values('name','sale','stock')
ret = models.Book.objects.all().update(price = F('stock')+50)
from django.db.models.functions import Concat
from django.db.models import Value
ret = models.Book.objects.all().update(name = Concat(F('name'),Value('(第一版)')))
filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询(例如OR语句),你可以使用Q对象。
ret = models.Book.objects.all().filter(Q(author__name='王计飞')|Q(author__name='刘德凯')).values('name','author__name')
ret = models.Book.objects.all().filter(author__name = '王计飞')
1、事务操作的代码格式:
import os
if __name__ == '__main__':
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "BMS.settings")
import django
django.setup()
import datetime
from app01 import models
try:
from django.db import transaction
with transaction.atomic():
操作内容
except Exception as e:
print(str(e))
2、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',
},
}
}
3、在Python脚本中调用Django环境:
import os
if __name__ == '__main__':
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "BMS.settings")
import django
django.setup()
from app01 import models
books = models.Book.objects.all()
print(books)