how to use python list in %sql query

user_299791 0
Viewed 2879

I am using the sql package in a Jupyter notebook and I understand how to use variables in my query:

client = "Disney"
queryid = %sql SELECT * FROM mytable WHERE name = :client

What I don't understand is how to pass a list to my query, like:

clients = ["Disney", "Netflix", "Sky"]
queryid = %sql SELECT * FROM mytable WHERE name in (:clients)

This raises an error which states that my SQL is wrong. What is the way to handle lists in this setting?

jupyter ipython mysql python
4 Answers

Removing parentheses works for me.

clients = ["Disney", "Netflix", "Sky"]
queryid = %sql SELECT * FROM mytable WHERE name in :clients
user_26496110

This function saves me from this kind of problem:


> def splitParam(param): # this function splits multiple values of filter
    filteredParam = ""
    if param:
        for item in param:
            if filteredParam=="":
                filteredParam += "'" + item.upper() + "'"
            else:
                filteredParam += ", '" + item.upper() + "'"

    return filteredParam
user_47626300

Anecdotal note about applying the $ with python tuples in a SQL query:

It needs to be used in a %sql line, it cannot* be used in a %%sql code block. Use line continuation backslashes for readability on the query.

*as far as I can tell

user_98823250
Related