Django3 python3 model field extra query, the field value is a string separated by commas

最新推荐文章于 2024-03-12 01:49:13 发布

yuntaoWei

最新推荐文章于 2024-03-12 01:49:13 发布

阅读量136

点赞数 1

分类专栏：笔记

本文链接：https://blog.csdn.net/m0_46300881/article/details/111573463

版权

笔记专栏收录该内容

13 篇文章 0 订阅

订阅专栏

from django.db.backends.utils import CursorDebugWrapper
from django.db import connection
from django.db.backends.mysql.base import DatabaseWrapper
from pymysql.cursors import Cursor
from collections import Iterable
from django.db.models import Model,Count
from django.db.models.fields import CharField

import logging


class rowsdivide:
    ''' yuntao
    Make group by having count(*) with django orm
    https://stackoverflow.com/questions/40452690/make-group-by-having-count-with-django-orm
    Prepared Statements, Make Dynamic SQL EXECUTE
    https://dev.mysql.com/doc/refman/5.7/en/sql-prepared-statements.html
    mysql逗号分隔字符串成多行数据
    https://blog.csdn.net/XDSXHDYY/article/details/84589486
    Mysql CREATE PROCEDURE and CREATE FUNCTION Statements
    https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

    DROP PROCEDURE if EXISTS proc_rowsdivide;
    DELIMITER ;;
    CREATE DEFINER=`starpro`@`%`
      PROCEDURE proc_rowsdivide (IN tbname VARCHAR(30), IN tbpk VARCHAR(30), IN tbfield VARCHAR(40), IN delimiter_ VARCHAR(1))
      BEGIN
        DROP TEMPORARY TABLE if EXISTS temp_rowsdivide;
        CREATE TEMPORARY TABLE temp_rowsdivide(
          id int(11) NOT NULL,
          name VARCHAR(100) DEFAULT NULL
        );
        set @tbname:=tbname;
        set @tbpk:=tbpk;
        set @tbfield:=tbfield;
        set @delimiter_:=delimiter_;
        set @sqlcmd1=concat(
          "INSERT into temp_rowsdivide (id, name)",
          " SELECT a.", @tbpk,
          ",substring_index(substring_index(a.", @tbfield,
          ",?,b.help_topic_id+1),?,-1) from ", @tbname,
          " a join mysql.help_topic b on b.help_topic_id < (length(a.", @tbfield,
          ") - length(replace(a.", @tbfield,
          ",?,''))+1)"
        );
        prepare stmt from @sqlcmd1;
        EXECUTE stmt USING @delimiter_, @delimiter_, @delimiter_;
        deallocate prepare stmt;
        SELECT * from temp_rowsdivide;
      END;
    ;;

    DELIMITER ;
    '''

    procedure_name = 'proc_rowsdivide'

    def __init__(self, queryset, tbfield, delimiter=',',):
        '''
        给查询集添加 字段值是以特定字符串分割的字符串的 搜索条件
        :param queryset: Django model 查询集
        :param tbfield: model字段 值是以,等字符分割的字符串
        :param delimiter: 分割的字符
        '''
        assert hasattr(queryset, 'model'), 'Error'
        assert issubclass(queryset.model, Model), 'Error'
        field = queryset.model._meta.get_field(tbfield)
        assert isinstance(field, CharField), 'Error'
        self.delimiter = delimiter
        self.queryset = queryset
        self.tbname = queryset.model._meta.db_table
        self.tbpk = queryset.model._meta.pk.name
        self.tbfield = tbfield

    def run(self, fieldvalue, default='or'):
        '''
        :param fieldvalue: 要查寻的值 比如 字段值 是 '182,193,200' 要查询的是 ['182'] 或者 ['182','172']
        :param default: or 默认多值查询时 使用or方式 也可指定and方式
        :return: 新的加了查询条件的查询集
        '''
        result = self.queryset
        assert isinstance(fieldvalue, Iterable), 'Error'
        assert default in ('or', 'and'), 'Error'
        params = [fieldvalue] if isinstance(fieldvalue, str) else fieldvalue
        with connection.cursor() as cursor:
            cursor.callproc(self.procedure_name, [self.tbname, self.tbpk, self.tbfield, self.delimiter])
            result = self.queryset.extra(tables=['temp_rowsdivide'], where=[
                'temp_rowsdivide.id={}.{}'.format(self.tbname, self.tbpk),
                'temp_rowsdivide.name in %s'
            ], params=[params, ])
            if default == 'and':
                result = result.annotate(cnt=Count(1)).filter(cnt__gte=len(params))
        return result