pymysql实现web多条件组合查询

 

 

 代码：

import pymysql
from rest_framework.views import APIView
from rest_framework.response import Response
# Create your views here.
'''利用pymysql实现多条件查询'''
class SqlWeb(APIView):
    def get(self, request):
        name = request.GET.get('name')
        gender = request.GET.get('gender')
        class_id = request.GET.get('class_id')
        request_data_dic = {}
        if name is not None:
            request_data_dic['sname'] = name
        if gender is not None:
            request_data_dic['gender'] = gender
        if class_id is not None:
            request_data_dic['class_id'] = class_id
        print('request_data_dic:{}'.format(request_data_dic))
        conn = pymysql.connect(
            user='root',
            password='123456',
            host='127.0.0.1',
            database='sql_test',
            port=3306,
            charset='UTF8'
        )
        # 获取游标
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        # 先将SQL固定的部分定义变量
        sql_1 = "SELECT T1.* FROM student AS T1 WHERE "
        choose_list = []
        if len(request_data_dic.keys()) != 0:
            count = 0
            for k, v in request_data_dic.items():
                # 当sql的查询条件为索引为0时,直接用=拼接
                if count == 0:
                    choose = k + '=' + '"' + v + '"'
                    choose_list.append(choose)
                # 当sql的查询条件为大于等于1时,需要在前面加上and拼接
                if count >= 1:
                    choose = ' and ' + k + '=' + '"' + v + '"'
                    choose_list.append(choose)
                count += 1
        print(choose_list)
        # 利用for循环将列表的查询条件元素拼接到一起
        chooose_new_1 = ''
        for i in choose_list:
            chooose_new_1 += i
        print('chooose_new_1:{}'.format(chooose_new_1))
        # 将SQL固定部分和查询条件部分拼接成完整SQL
        sql = sql_1 + chooose_new_1
        print(sql)
        cursor.execute(sql)
        ret = cursor.fetchall()
        data = {
            'data': ret,
            'msg': '返回数据'}
        return Response(data)