Wildcard query with variable

Hi guys,

I’ve been having trouble figuring out how to apply the following. I want to query the database to count the number of entries containing a matching string. The string would be changing weekly. I’m using the code below

year = str(self.year)
week = str(self.week_number)
city = str(self.city)
name = str(year + “-” + week + “-” + city)
num = frappe.db.sql(“select count(name) from tabaction_planning where name like %s”, (name,))

If I substitute a string for name instead of using the variable the code does execute without a problem.

I’ve tried different combinations of adding the wildcard symbol ‘%’ to ‘%s’ and or to ‘name’. But all have given errors so far

@hosp1 use the standard frappe db api

frappe.db.count("action_planning", filters=[["name", "like", "{0}%".format(name)]])

Thanks for the reply.

Tried using the code you gave, however I get an error “TypeError: coercing to Unicode: need string or buffer, list found”

@hosp1 I thought that I forgot the mention of the doctype in the filters, try use the filters as [["action_planning", "name", "like", "{0}".format(name)]]


I managed to use the previous code snippet I had and used ‘sounds like’ instead of ‘like’ with the wildcard symbol

Thanks again though