python - 我需要使用有关 Django 程序中的 where 条件的 sql 语句的帮助

Question

这是我的程序。我需要更改SQL语句中的where条件以使用python程序动态生成，因为有时我的where语句中的条件可能没有值。我不希望他在没有值的情况下出现在SQL语句中。在

非常感谢，我真的需要帮助，我是新手，我不明白

@csrf_exempt
def test(request):
    if request.method == "GET":
        req_type = request.GET.get("get_data")
        if req_type:
            customer = request.GET.get('customer')
            order_type = request.GET.get("orderType")
            order_status = request.GET.get("orderStatus")
            with connection.cursor() as cursor:
                cursor.execute(
                    "SELECT account,order_id,start_at,update_at "
                    "FROM once  WHERE user_id=%s OR %s='' AND order_id=%s OR %s='' AND status=%s OR  %s='' ORDER BY order_id DESC ",
                    (customer, customer, order_type, order_type, order_status,order_status))
                verify_list = dict_fetchall(cursor)
            return HttpResponse(json.dumps(verify_list, cls=DecimalEncoder))
        else:
            with connection.cursor() as cursor:
                cursor.execute('SELECT id,`name` FROM goods;')
                goods_data = cursor.fetchall()
            with connection.cursor() as cursor1:
                cursor1.execute('SELECT id,account FROM `user`;')
                user_data = cursor1.fetchall()
            content = {"goods_data": goods_data, "user_data": user_data}
            return render(request, 'user/test.html', content)
    else:
        return HttpResponseRedirect(reverse('users:login'))

Answer 1

如果我已经很好地理解了这个问题，你可以动态构造一个sql查询字符串，例如:

where = []

if customer:
    where.append(f"user_id={customer}")  # assuming customer is integer, put value without quotes
if order_type:
    where.append(f"order_id={order_type}")  # assuming order_type is integer, put value without quotes
if order_status:
    where.append(f"status='{order_status}'")  # assuming order_status is string, put value inside quotes

where = ' AND '.join(where)        
where = ('WHERE ' if where else '') + where

query = f"SELECT account, order_id, start_at, update_at FROM once {where} ORDER BY order_id DESC"

with connection.cursor() as cursor:
    cursor.execute(query)