Pass a python variable into an SQL query

Question

I am working on Databricks and I am trying to pass a python variable into a SQL query:

series_name "LogTest"

query = """SELECT * FROM results_table
  WHERE name = $series_name
  """
spark.sql(query).toPandas()

I tried with $ but it does not work.

How do I do this?

Regards

look this example :https://stackoverflow.com/questions/44582450/how-to-pass-variables-in-spark-sql-using-python — Bahtiyar, May 29 '20 at 11:43
is this a python code? any reason '=' is missing the first line? — najeem, May 29 '20 at 11:44

score 1 · Answer 1 · answered May 29 '20 at 11:45

In this case, your variable and queries are just strings. You can do:

query = f"""SELECT * FROM results_table
WHERE name = '{series_name}'
"""

... in python 3. Remember that your query string needs the single quotes around the inserted variable. However, for certain variables, you may need to pass the variable directly to the spark module so it can interpret it. I use pyodbc a lot and would do this:

query = f"""SELECT * FROM results_table
WHERE name = ?"""
cursor.execute(query, series_name)

Thanks! I tried with f-string but I did not put the single quotes ('). Normally it works without it — vftw, May 29 '20 at 11:49

score 1 · Answer 2 · answered Jun 03 '20 at 22:32

1

Following will also work,

series_name = "LogTest"
spark.sql("SELECT * FROM results_table WHERE name = " + series_name).toPandas()

answered Jun 03 '20 at 22:32

viswanathanr

58
3

score 0 · Answer 3 · edited Jun 06 '20 at 10:22

0

we can also try the following.

series_name = "LogTest"
query = "SELECT * FROM results_table WHERE name = {}".format(series_name)
spark.sql(query).toPandas() #

edited Jun 06 '20 at 10:22

viswanathanr

58
3

answered May 29 '20 at 11:47

Shafay

187
2
7

Pass a python variable into an SQL query

3 Answers3