'%' character in SQL LIKE statement throws error

[miguelbento]

Hi there,

Firstly thank you for a really useful package!

I've tried a number of queries and they all worked until I used a query that uses LIKE statements in the SQL to check for phrases in text fields. The use of the % sign and 'S' is causing an error - perhaps its being resolved to something else in the code? Please see example below. When I change the %SNB% to something else (eg. %FNB%), I get a different error. Please assist :)

query being executed: select count(1) from

where BeneficiaryReference like '%SNB%' and TransactionDate between {{ TrxnStartDate }} and {{ TrxnEndDate }} **params:** params = {'TrxnStartDate': '2019-04-01', 'TrxnEndDate': '2020-03-31'} **error output when using like '%SNB%':** --------------------------------------------------------------------------- ValueError Traceback (most recent call last) in 1 #add competitor home loans 2 #queries.sql_get_Competitor_HL ----> 3 apply_sql_template(queries.sql_get_Competitor_HL, params) 4 #competitor_home_loans_dataset = pd.read_sql(apply_sql_template(queries.sql_get_Competitor_HL, params), conn) 5 #clients_details = competitor_home_loans(clients_details) in apply_sql_template(template, parameters) 24 j = JinjaSql(param_style='pyformat') 25 query, bind_params = j.prepare_query(template, parameters) ---> 26 return get_sql_from_template(query, bind_params) in get_sql_from_template(query, bind_params) 19 for key, val in params.items(): 20 params[key] = quote_sql_string(val) ---> 21 return query % params 22 23 def apply_sql_template(template, parameters): **ValueError: unsupported format character 'S' (0x53) at index 114** **error output when using like '%FNB%':** --------------------------------------------------------------------------- TypeError Traceback (most recent call last) in 1 #add competitor home loans 2 #queries.sql_get_Competitor_HL ----> 3 apply_sql_template(queries.sql_get_Competitor_HL, params) 4 #competitor_home_loans_dataset = pd.read_sql(apply_sql_template(queries.sql_get_Competitor_HL, params), conn) 5 #clients_details = competitor_home_loans(clients_details) in apply_sql_template(template, parameters) 24 j = JinjaSql(param_style='pyformat') 25 query, bind_params = j.prepare_query(template, parameters) ---> 26 return get_sql_from_template(query, bind_params) in get_sql_from_template(query, bind_params) 19 for key, val in params.items(): 20 params[key] = quote_sql_string(val) ---> 21 return query % params 22 23 def apply_sql_template(template, parameters): **TypeError: must be real number, not collections.OrderedDict**

[sripathikrishnan]

By default, JinjaSQL converts your query to a string with %s. So a query like SELECT * from users where name = {{params.firstName}} becomes SELECT * from users where name = %s. When you have a literal string with a %s as part of a like query, the underlying database driver gets confused.

You have two ways to solve this problem:

1. You can escape the percentage. So instead of column like '%sblah%s', write it with two % symbols, like column like %%blah%%. That should solve your problem.
2. Alternatively, you can configure JinjaSQL to use a different param style - like ?.

If you end up changing to ?, and your query has a literal ? in it, you would then have to escape it. I guess % is more likely in a query, so it perhaps makes sense to change the format to qmark.