How can I perform search operation using raw sql query in django SQLITE-CodePudding

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 message like thisTypeError('not all arguments converted during string formatting')),. and also if I try like detection_class=%s it works properly when gives while word else it returns None.

CodePudding user response：

Don't use string formatting when executing queries. Instead use a placeholder OR a named parameter.

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)

I believe it is also possible to do it using the placeholder method as well, as shown below.

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,))