526互联

Django基础 - 05Model模型之CRUD

发布时间 2023-04-08 21:58:10作者: Kingdomer

 

一、模型类的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 源码

1.2.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.2.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]
    })

　　

三、原生的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()