如何使用django sqlite中的RAW SQL查询执行搜索操作

Question

I'm trying to search string in the STAT table's detection field like below:

query = "select detection_class, stream_id, track_id, detection_time, " \
                    "frame_id" \
                    " FROM stats where  stream_id = %s "\
                    "detection_class like %%s%% group by track_id; "
with connection.cursor() as cursor:
      cursor.execute(query,[stream_id, detection_class])

Here, when I give the word or some character which are contained in detection_class, it returns an error像这样的消息typeError（“不是在字符串格式化期间转换的所有参数”），。 而且，如果我尝试喜欢dintection_class =％s它在给出时可以正常工作，而否则将返回无。

Answer 1

执行查询时不要使用字符串格式。而是使用占位符或命名参数。

query = """
SELECT detection_class, 
       stream_id, 
       track_id, 
       detection_time,
       frame_id
FROM stats
WHERE stream_id = :stream_id
AND detection_class LIKE :dclass 
GROUP BY track_id;
"""

with connection.cursor() as cursor:
      params = {"stream_id": stream_id, "dclass": f"%{detection_class}%"}
      cursor.execute(query, params)

我相信也可以使用占位持有人方法进行操作，如下所示。

query = """
SELECT detection_class, 
       stream_id, 
       track_id, 
       detection_time,
       frame_id
FROM stats
WHERE stream_id = ?
AND detection_class LIKE %?%
GROUP BY track_id;
"""

with connection.cursor() as cursor:
      cursor.execute(query, (stream_id, detection_class,))