[FIXED] sql-magic query works when executed in dedicated cell, but not when executed inline

Issue

I have a query that looks like this:

SELECT
    *
FROM my_table
WHERE
    columns_name = 'example text  9999-' -- note the double spaces before the number and dash at the end... don't know if it's actually important, but is the only outlier in format compared to other queries that work

In jupyter the query is generated dynamically from a pandas dataframe and the code looks like this:

for index, row in df.iterrows():

   needed_value = row['columns_name']

   query_string = f"""
   SELECT
       *
   FROM my_table
   WHERE
       columns_name = '{needed_value}'
   """
   
   result_set = %sql $query_string

   # do stuff with result_set

The query gets executed successfully, but the resulting dataframe is empty only for the string that looks like the one mentioned above. It works for all other. I have no idea why this happens.

If executed in it's own cell like below, it works fine.

%%sql

SELECT
    *
FROM my_table
WHERE
    columns_name = 'example text  9999-'

What am I dong wrong when generating the query dynamically?

Solution

The problem is that %sql replaces the two spaces from the value with a single one, while %%sql does not do this.

You can view the generated query by using .sql on the result set.

I modified the query a little bit to now use ILIKE and replacement of double spaces with " %". More of a workaround, but it works...

Answered By - drake10k