Django raw sql statement with params and likes search

Question

I have a problem when performing a raw sql in Django.

res = []
start = '2017-12-08T01:56:56.701Z'
end = '2020-12-08T01:56:56.701Z'
with connection.cursor() as cursor:
    raw_sql = '''
    select
    case
        when content like '%products%' then 'Products'
    else 'Others'
    end as name,
    count(id) as times
    from mytable
    where somefield LIKE "%somefieldcontent%"
    and created between '%s' and '%s'
    '''
    cursor.execute(raw_sql, [start, end])
    res = cursor.fetchall()

It raise this error: unsupported format character ''' (0x27)

I tried to perform this sql directly in mysql, it works. But it does not work in the Django environment. I think must be something wrong I do about the string.

Basically I want to use params instead concat to build the SQL statement. Any ideas? Thanks!

Answer 1

You can try using f-string, something like this

from django.db import connection
start = '2017-12-08T01:56:56.701Z'
end = '2020-12-08T01:56:56.701Z'
def query_example(start, end):
   cursor = connection.cursor()
   
   cursor.execute(
     f'''
     select
      case
        when content like '%products%' then 'Products'
      else 'Others'
      end as name,
      count(id) as times
      from mytable
      where somefield LIKE "%somefieldcontent%"
      and created between {start} and {end}
     '''
   )

   return cursor.fetchall()

And then you can access the data in views, call query_example func

def request_data(request):

  data = query_example(...)
  # do something...
  ...
  
  return JsonResponse(data, safe=False)
      or 
  return render(request, "your_template.html", {"data": data})