How to use a variables in SQL statement in databricks?

Question

I want to use a WHERE statement with two variables within the where clause. I've done research on this looking at how to use variables in SQL statements in Databricks and Inserting Variables Using Python, Not Working. I've tried to implement the solutions provided but it's not working.

a= 17091990
b = 30091990

df = spark.sql(' SELECT * FROM table WHERE date between "a" AND "b" ')

Whats the error that you are getting? Maybe because of the date format? Date format should be separated by dash(-), somthing like this I Think A = '09-17-1990' and B = '09-30-1990' — Buchiman
– Buchiman, Commented Aug 20, 2019 at 1:17
Can you clarify how it is "not working". Can you provide some sample data and also show what the expected vs actual output is? — Dijkgraaf
– Dijkgraaf, Commented Aug 20, 2019 at 1:17
@Dijkgraaf I dont know how to do it. Ive been trying a different ways to use the variables in the query but doesnt work — P3P0
– P3P0, Commented Aug 20, 2019 at 1:40

anshul_cached · Accepted Answer · 2019-08-20 07:55:39Z

4

You can use python's formatted string literals

df = spark.sql(f"SELECT * FROM table WHERE date between {a} AND {b} ")

For more about formatted string literals you can refer to https://docs.python.org/3/whatsnew/3.6.html#whatsnew36-pep498

answered Aug 20, 2019 at 7:55

anshul_cached

7727 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bruno.braga Over a year ago

Strongly discourage the direct manipulation of strings to build a query, which is vulnerable to SQL injection. Use parameters instead: docs.databricks.com/dev-tools/python-sql-connector.html

Collectives™ on Stack Overflow

How to use a variables in SQL statement in databricks?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related