一、模型类的objects
1.1 objects字段
默认情况下， 由创建模型类的元类在模型类中创建一个 django.db.models.Manager类的对象， 赋给objects。
Manager类实际是QuerySet类的子类。
class CategoryEntity(models.Model):
objects = models.Manager() # objects 必须为Manager类对象，不能是其他的
指定objects 为其它字段类型时，报错如下：
ValueError: Model CategoryEntity must specify a custom Manager, because it has a field named 'objects'
1.2 objects对象的创建
1.2.1 显性对象： 开发者手动创建对象； 在模型类中，开发者声明的属性字段
class OrderManager(models.Manager):
def create(self, summary, pay_type): # 创建对象时，进行字段赋值
order = self.model()
order.sumary = summary
order.pay_type = pay_type
return order def get_queryset(self): return super().get_queryset().filter(~Q(order_status=5)) class OrderModel(BaseModel): objects = OrderManager() # 显性创建 objects, 只展示未取消的订单
2023年 共有 三个单据， 有一个单据 已取消 order_status=5， 查询2023年的单据只能查到2单
在Django Console 中 测试, 执行查询：
select sum(price) as total_price from t_order where substr(create_time,1,4)='2023' and pay_status != 5;
>>> from orderapp.models import OrderModel >>> from django.db.models import Sum
>>> OrderModel.objects.filter(create_time__year=2023) \ >>> .aggregate(total_price=Sum('price')) {'total_price': 1500.0} >>> OrderModel.objects.filter(create_time__year=2023).aggregate(Count('title')) {'title__count': 2}
1.2.2 隐性对象
系统自动创建的对象； 如果手动指定，则系统不会再创建； objects 是 models.Manager 类型
1.3 源码
1.3.1 django\db\models\base.py 文件
class ModelBase(type):
def _prepare(cls):
......
if not opts.managers:
if any(f.name == 'objects' for f in opts.fields):
raise ValueError(
"Model %s must specify a custom Manager, because it has a "
"field named 'objects'." % cls.__name__
)
manager = Manager()
manager.auto_created = True
cls.add_to_class('objects', manager)
1.3.2 django\db\models\manager.py 文件
class Manager(BaseManager.from_queryset(QuerySet)):
pass
class BaseManager:
@classmethod
def from_queryset(cls, queryset_class, class_name=None):
if class_name is None:
class_name = '%sFrom%s' % (cls.__name__, queryset_class.__name__)
return type(class_name, (cls,), {
'_queryset_class': queryset_class,
**cls._get_queryset_methods(queryset_class),
})
二、 基于objects的CRUD
2.1 过滤器： filter() / exclude() / all()
2.1.1 编写 mainapp/views.py, 定义 find_fruit()函数，
def find_fruit(request: HttpRequest):
# 根据价格区间查询水果信息, 返回数据
price_start = request.GET.get('price_start', 0)
price_end = request.GET.get('price_end', 1000)
fruits = FruitEntity.objects.filter(price__gt=price_start, price__lt=price_end).all()
return render(request, 'fruit/list.html', locals())
2.1.2 访问 http://127.0.0.1:8000/user/fruit/list?price_start=30&price_end=1000
2.1.3 在页面加入搜索框
<body>
<form method="get">
{# <input name="price_start" value="0"> - <input name="price_end" value="1000">#}
<input name="price_start" value="{% if price_start %} {{ price_start }} {% else %} 0 {% endif %}"> -
<input name="price_end" value="{% if price_end %} {{ price_end }} {% else %} 1000 {% endif %}">
<button>搜索</button>
</form>
<ul>
{% for fruit in fruits %}
2.2 条件
语法格式：
- 模型类.objects.filter(属性名__条件 = 条件值)
- 模型类.objects.filter(属性名 = 条件值)
- 模型类.objects.filter(属性名__时间__条件 = 条件值)
条件包括：
- 运算相关： gt大于 / lt小于 / gte大于等于 / lte小于等于
- 字符串相关： contains 包含(区分大小写) / icontains / (i)startswith / (i)endswith
- Null相关： isnull() / isnotnull()
- 范围： in
时间属性：
- year， month, day, hour, minute, second
2.2.1 条件
fruits = FruitEntity.objects.filter(price__range=[price_start, price_end]) \
.exclude(price=50).filter(name__contains='果').all()
2.2.2 时间属性
def find_store(request: HttpRequest): # stores = StoreEntity.objects.filter(create_time__year=2023).all() # stores = StoreEntity.objects.filter(create_time__month__gte=3, create_time__year__lte=2022).all()
query_set = StoreEntity.objects.filter(create_time__month__lt=6).order_by('-id') first_store = query_set.first() print(first_store) # 云里蔬果店-青岛
stores = query_set.all().filter(city='北京') return render(request, 'store/list.html', locals())
2.3 获取对象 QuerySet对象的方法
QuerySet对象本身是可以被迭代的。 返回QuerySet对象的方法有：
filter() / exclude() / all() / values() / values_list() / order_by('name', '-city')
def all_store(request):
result = {}
if StoreEntity.objects.exists():
datas = StoreEntity.objects.values()
print(type(datas)) # <class 'django.db.models.query.QuerySet'>
total = StoreEntity.objects.count()
store_list = []
for store in datas:
store_list.append(store)
result['data'] = store_list
result['total'] = total
else:
result['msg'] = '数据为空'
return JsonResponse(result)
2.4 聚合函数
from django.db.models import F, Q, Count, Sum, Min, Max, Avg
def count_fruit(request):
result = FruitEntity.objects.aggregate(Count('name'), Sum('price'),
Max('price'), Min('price'), Avg('price'))
return JsonResponse(result)
2.5 F 字段条件
django.db.models.F 用于获取字段的值，并参与计算或作业更新条件。
def discount_fruit(request):
result = FruitEntity.objects.aggregate(Count('name'))
# 水果88折优惠
FruitEntity.objects.update(price=round(F('price') * 0.88, 2))
fruits = FruitEntity.objects.values() # QuerySet
return JsonResponse({
'count': result,
'fruits': [fruit for fruit in fruits]
})
2.6 Q 条件
查询条件的封装； from django.db.models import F, Q
可以进行逻辑运算： 与& 或| 非~
- Store.objects.all().filter(Q(years=2020)|Q(years=2021))
- Store.objects.all().filter(~Q(years=2020))
- Store.objects.all().filter(Q(years__gt=2020)&Q(years__lt=2023))
def query_fruit(request):
q_ret = FruitEntity.objects.all().filter(Q(price__lte=10)|Q(price__gte=200)).values()
# 查询水果价格小于10或高于200的，或者 产地是西安且名字包含"果"的
multi_ret = FruitEntity.objects.all()\
.filter(Q(price__lte=10)|Q(price__gte=200)|
Q(Q(source='西安') & Q(name__contains="果")))\
.values()
return JsonResponse({
"fruits": [fruit for fruit in q_ret],
"multi_fruits": [fruit for fruit in multi_ret]
})
2.7 数据验证与批量创建
2.7.1 数据验证; 验证不通过，可以抛出 ValidationError异常
from django.core.exceptions import ValidationError
class UserValidator:
@staticmethod
def valid_phone(cls, value):
if not re.match(r'1[1-35-9]\d{9}', value):
raise ValidationError('手机格式不正确')
return True
class UserEntity(models.Model):
phone = models.CharField(max_length=11, verbose_name='手机号',
validators=[UserValidator.valid_phone],
blank=True, null=True)
使用clean_fields() 测试
>>> from mainapp.models import UserEntity
>>> u1 = UserEntity()
>>> u1.name = 'Selly'
>>> u1.age = 18
>>> u1.phone = '12121'
>>> u1.password = '123456'
>>> u1.clean_fields()
Traceback (most recent call last):
File "<input>", line 1, in <module>
File "E:\PythonLearn\djangoDemo\venv\lib\site-packages\django\db\models\base.py", line 1177, in clean_fields
raise ValidationError(errors)
django.core.exceptions.ValidationError: {'phone': ['手机格式不正确']}
在保存数据时，进行验证
def save(self, force_insert=False, force_update=False, using=None,
update_fields=None):
if len(self.password) < 15:
self.password = make_password(self.password)
self.clean_fields() # 新增clean_fields()
super().save()
2.7.2 批量创建 bulk_create
>>> from mainapp.models import CategoryEntity
>>> c1 = CategoryEntity.objects.create(name='清火', order_num=20)
>>> c2 = CategoryEntity.objects.create(name='增寿', order_num=30)
>>> c3 = CategoryEntity()
>>> c3.name='补水'
>>> c3.order_num=40
>>> c4 = CategoryEntity()
>>> c4.name='甜品'
>>> c4.order_num=50
>>> CategoryEntity.objects.bulk_create([c3,c4])
[<CategoryEntity: 补水>, <CategoryEntity: 甜品>]
>>> CategoryEntity.objects.all()
<QuerySet [<CategoryEntity: 甜品>, <CategoryEntity: 补水>, <CategoryEntity: 增寿>, <CategoryEntity: 清火>,
<CategoryEntity: 北方水果>, <CategoryEntity: 南方水果>, <CategoryEntity: 热带水果>]>
2.7.3 返回指定字段
>>> CategoryEntity.objects.values('name')
<QuerySet [{'name': '甜品'}, {'name': '补水'}, {'name': '增寿'}, {'name': '清火'},
{'name': '北方水果'}, {'name': '南方水果'}, {'name': '热带水果'}]>
>>> CategoryEntity.objects.values('order_num')
<QuerySet [{'order_num': 50}, {'order_num': 40}, {'order_num': 30}, {'order_num': 20},
{'order_num': 10}, {'order_num': 5}, {'order_num': 1}]>
三、原生的SQL语句查询
针对复杂查询，通过QuerySet查询不是很方便，则可以使用原生的SQL查询。
两种原生SQL查询： QuerySet.raw()、 QuerySet.extra()
使用django.db.connect数据库连接对象进行原生SQL查询
3.1 QuerySet.raw()
查询的字段必须是模型类中声明的字段，且必须存在主键列。查询结果是RawQuerySet类对象，可迭代， 元素是模型类对象。
SQL查询语句可以使用 "%s" 或 "%(name)s" 占位符， 可以使用元祖或关键参数传值
3.1.1 不带条件的查询
>>> from mainapp.models import FruitEntity
>>> FruitEntity.objects.filter(price__gt=10).values()
<QuerySet [{'id': 1, 'name': '火龙果', 'price': 11.50, 'source': '泰国', 'category_id': 1},
{'id': 3, 'name': '神仙果', 'price': 395.59, 'source': '非洲', 'category_id': 1},
{'id': 4, 'name': '荔枝', 'price': 10.78, 'source': '福州', 'category_id': 1},
{'id': 5, 'name': '橘子', 'price': 17.98, 'source': '江西', 'category_id': 2},
{'id': 6, 'name': '芒果', 'price': 21.21, 'source': '湖南', 'category_id': 2},
{'id': 7, 'name': '葡萄', 'price': 19.77, 'source': '新疆', 'category_id': 3}]>
>>> FruitEntity.objects.raw('select name, price from t_fruit')
<RawQuerySet: select name, price from t_fruit>
>>> raw_queryset = FruitEntity.objects.raw('select name, price from t_fruit')
django.db.models.query_utils.InvalidQuery: Raw query must include the primary key # 查询必须带上主键
>>> raw_set = FruitEntity.objects.raw('select id, name, price from t_fruit')
>>> for fruit in raw_set:
print(fruit)
火龙果-泰国:11.50
苹果-烟台:2.01
3.1.2 带条件的查询
raw_set2 = FruitEntity.objects.raw('select id, name, price from t_fruit where price < %s', (10,))
raw_set3 = FruitEntity.objects.raw('select id, name, price from t_fruit where price < %s LIMIT %s, 10', (100,0))
raw_set4 = FruitEntity.objects.raw('select id,name,price from t_fruit where price < %s order by price DESC LIMIT %s, 10', (10, 0))
# 使用字典传值, 执行报错
>>> raw_set5 = FruitEntity.objects.raw('select id, name, price from t_fruit where price < %(pr)s LIMIT %(pa)s, 10', {'pr': 100,'pa': 0})
print(raw_set3)
<RawQuerySet: select id, name, price from t_fruit where price < 100 order by price desc LIMIT 0, 10>
>>> for raw in raw_set5:
print(raw)
django.db.utils.OperationalError: near "%": syntax error
3.2 QuerySet.extra()
extra()扩展查询，针对QuerySet查询结果集，增加查询条件或排序等操作。返回结果是QuerySet对象。
# django/db/models/query.py 文件
class QuerySet:
def extra(self, select=None, where=None, params=None, tables=None,
order_by=None, select_params=None):
>>> FruitEntity.objects.extra(where=['price<%s'], params=['10'])
<QuerySet [<FruitEntity: 苹果-烟台:2.01>]>
>>> FruitEntity.objects.extra(where=['price<%s or name like %s'], params=['100', '果'])
<QuerySet [<FruitEntity: 火龙果-泰国:11.50>, <FruitEntity: 苹果-烟台:2.01>, <FruitEntity: 荔枝-福州:10.78>,
<FruitEntity: 橘子-江西:17.98>, <FruitEntity: 芒果-湖南:21.21>, <FruitEntity: 葡萄-新疆:19.77>]>
>>> ext = FruitEntity.objects.extra(where=['price<%s or name like %s and source=%s'], params=['100', '果', '烟台'])
>> ext.all()
<QuerySet [<FruitEntity: 火龙果-泰国:11.50>, <FruitEntity: 苹果-烟台:2.01>, <FruitEntity: 荔枝-福州:10.78>,
<FruitEntity: 橘子-江西:17.98>, <FruitEntity: 芒果-湖南:21.21>, <FruitEntity: 葡萄-新疆:19.77>]>
>>> ext.values()
<QuerySet [{'id': 1, 'name': '火龙果', 'price': 11.50, 'source': '泰国', 'category_id': 1},
{'id': 2, 'name': '苹果', 'price': 2.01, 'source': '烟台', 'category_id': 2},
{'id': 4, 'name': '荔枝', 'price': 10.78, 'source': '福州', 'category_id': 1},
{'id': 5, 'name': '橘子', 'price': 17.98, 'source': '江西', 'category_id': 2},
{'id': 6, 'name': '芒果', 'price': 21.21, 'source': '湖南', 'category_id': 2},
{'id': 7, 'name': '葡萄', 'price': 19.77, 'source': '新疆', 'category_id': 3}]>
3.3 django.db.connection连接进行原生SQL查询
connection对象表示与数据库的连接对象，通过connection对象获取游标cursor对象
通过cursor的 execute()/fetchall()/rowcount相关的方法或函数来执行原生SQL和获取执行结果。
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute('select * from t_fruit')
<django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x000000142A801EE0>
>>> print(cursor.rowcount)
-1
>>> for row in cursor.fetchall():
print(row)
(1, '火龙果', 11.50, '泰国', 1)
(2, '苹果', 2.01, '烟台', 2)
>>> cursor.execute('update t_fruit set price=11.50 where id =1')
<django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x000000860A710EE0>
>>> cursor.rowcount
1
>>> connection.commit()
