Pass a python variable to SQL query with koalas

Question

I am using a databricks notebook and I would like to pass several python variables to an SQL query using koalas.sql.

Here a simplified example of what I am trying to do.

import databricks.koalas as ks

query = """
        SELECT *
        FROM my_database
        WHERE animal = pyth_var
        """
ks.sql(query, globals = {'pyth_var':'dog'})

But the python variable cannot be read. I get:

AnalysisException: cannot resolve 'pyth_var' given input columns

Answer 1

I think all you need is to wrap the reference variables in your SQL statement in curly braces, so that Koalas can interpret them as variables. The docs say:

(koalas.sql) This function also supports embedding Python variables (locals, globals, and parameters) in the SQL statement by wrapping them in curly braces.

So, you could try something like this:

ks.sql("SELECT * FROM my_database WHERE animal = {pyth_var}", globals={"python_var": "dog"})