Use python variable in SQL cell

Feature suggestion
1

I have access to a few databases but unable to utilize linked servers. It would be nice to be able to grab a python variable to use in sql cell (like an f string or something). I am left to copy and paste in my notebook from python output to sql cell.

2

Hi @Philip_Steichen, I’m happy to announce that SQL query parameterization is already in test and hopefully will be available later this month.

Anyway, thanks for the suggestion!

3

Thanks @igro! I’ll be watching the blog closely!

1 Like
4

Hello!

You can now parametrize queries inside SQL cells,
you can find more info in our docs: SQL cells | Datalore

2 Likes
5

How can i do stuff like


SELECT * FROM table WHERE column LIKE '%{python_variable}%'

this kind of parametrized queries with embedded string pattern doesn’t work.

Shall i left out the pattern string just with variable, then it works.

SELECT * FROM table WHERE column LIKE {python_variable};
6

SELECT * FROM table WHERE column LIKE ‘%{python_variable}%’

There is no way to do what you are asking. How should replacement algorithm figure out whether you want python_variable to be replaced in this valid SQL string literal or not?

Shall i left out the pattern string just with variable, then it works.

SELECT * FROM table WHERE column LIKE {python_variable};

Yes, this is the right approach.

7

How should replacement algorithm figure out whether you want python_variable to be replaced in this valid SQL string literal or not?

I don’t know how the implementation of replacement algorithm works.
I thought {} might be used internally for a python eval(), so i also put f-string inside.

SELECT * FROM table WHERE column LIKE {f"\'\%{python_variable}\%\' "}

but it also doesn’t work.

8

I found a solution:

# Python cell
search_text = "C"
search_pattern =f"%{search_text}%"

# SQL cell
SELECT * FROM table WHERE column LIKE {search_pattern}