【Django】数据库操作

查询多条，并转成字典

list(map(model_to_dict, query_set.objects.filter(url=url))) #python3的map必须用list,否则报错

goroup by

data = list(query_set.objects.filter(url=url).values("point_x", "point_y").annotate(
                            value=Count('point_x')))

count

v_total = query_set.objects.filter(url=url).aggregate(Count('id'))['id__count']

values

values返回是字典列表

values_list

values_list返回的是元组列表,若果希望返回值列表，那就加flat=True

DpShop.objects.values_list('category_name',flat=True)

在本地已经有数据表的情况，生成对应的model

用命令 python manage.py inspectdb table_name

model choices选多个

安装 django-multiselectfield

from multiselectfield import MultiSelectField

from utils.base_model import BaseModel
from django.db import models


class MultiSelectCompatIntField(MultiSelectField):
    def get_prep_value(self, value):
        return '' if value is None else ','.join([str(x) for x in value])


class FormAModel(BaseModel):
    AREA_CHOICES = (
        (1, 'Sensing Technology'),
        (2, 'Industry Internet of Things'),
        (3, 'Future Materials'),
    )
  
    area = MultiSelectCompatIntField(choices=AREA_CHOICES, max_length=255)

对应的字段Serializer序列化

from rest_framework import serializers, fields
from project_survey.models import FormAModel

class FormASerializer(serializers.ModelSerializer):
    area = fields.MultipleChoiceField(choices=FormAModel.AREA_CHOICES, required=False, allow_null=True, )
    area_display = serializers.CharField(source='get_area_display', read_only=True)

Pandas数据批量保存到数据库

定义pandas数据处理方法

class PandasBulkCreate():
    def __init__(self, df, model):
        self.df = df
        self.model = model

    @classmethod
    def get_objecet_list(cls, df, model):
        entries = []
        for e in df.T.to_dict().values():
            entries.append(model(**e))
        return entries
        
    # 调用批量保存 bulk_create
    def create(self):
        self.model.objects.bulk_create(self.get_objecet_list(self.df, self.model))

Pandas数据批量更新到数据库

    @classmethod
    def get_objecet_list(cls, df, model, filter_params):
        entries = []
        for e in df.T.to_dict().values():
            filter_data = {item: e.get(item) for item in filter_params}
            ins = model.objects.filter(**filter_data).first()
            if ins:
                for key, value in e.items():
                    if hasattr(ins, key):
                        setattr(ins, key, value)
                entries.append(ins)
        return entries

调用批量保存 bulk_update

from bulk_update.helper import bulk_update

update_ins = cls.get_objecet_list(df, HikvisionUserModel, filter_params=['personId'])
if update_ins:
      bulk_update(update_ins)