Reference a single item tuple using .format() in spark.sql()

[cmilligan]

I'm trying to pass the elements of a tuple into a sql query using .format(). This works fine when I have multiple items in my tuple, but when using a single item in a tuple I get an error.

tuple1 = (1,2,3)
tuple2 = (5,)
 
combo = tuple1 + tuple2
 
print(combo)
 
query = ("""
 
select
case
  when column in {tuple1} then 1
  when column in {tuple2} then 2
end as check
 
from table
 
where column in {combo}
 
""".format(tuple1 = tuple1, tuple2 = tuple2, combo = combo))
 
print(query)

Whenever I print the query it keeps the comma at the end of the single tuple which is what I think is causing the error.

 

I do want to continue to use a tuple because there are scenarios where I need to concatenate multiple tuples together.

[ChaubeyPravin]

Hello,

can you please add some code or snippet where you are getting error, it will be more clear to understand your problem.

[cmilligan]

Thanks! I added it in

[Lakshay]

Could you please post the code and the error that you are getting?

[cmilligan]

@Lakshay Goel​ I added it in

[Lakshay · Accepted solution]

For a single-value tuple, you need to use a check to modify the SQL query as well as how the tuple is passed. The single-value tuples will always have a comma at the end. You need to modify the query to be able to pass only the value from the tuple. The new query would look something like

query = ("""

select key,

case

 when column in {tuple1} then 1

 when column in ({tuple2}) then 2

end as check

from table

""".format(tuple1 = tuple1, tuple2 = tuple2[0], combo = combo))

[cmilligan]

Thanks for the reply. I think I'll keep digging around a little trying to find something that a user can update without having to worry if there is one value or multiple