'%' 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
<transactions_table>
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.