IPython SQL Magic - Generate Query String Programmatically

Question

I'm generating SQL programmatically so that, based on certain parameters, the query that needs to be executed could be different (i.e., tables used, unions, etc). How can I insert a string like this: "select * from table", into a %%sql block? I know that using :variable inserts variable into the %%sql block, but it does so as a string, rather than sql code.

Answer 1

The answer was staring me in the face:

query="""
select 
*
from
sometable
"""
%sql $query

Answer 2

If you want to templatize your queries, you can use string.Template:

from string import Template

template = Template("""
SELECT *
FROM my_data
LIMIT $limit
""")

limit_one = template.substitute(limit=1)
limit_two = template.substitute(limit=2)

%sql $limit_one

Source: JupySQL documentation.

Important: If you use this approach, ensure you trust/sanitize the input!