My list:
pylist = ['A', 'B', 'C', 'D']
My spark.sql() line:
query = spark.sql(
"""
SELECT col1, col2, col3
FROM database.table
WHERE col3 IN ('A', 'B', 'C', 'D')
"""
)
I want to replace the list of elements in the spark.sql() statement with the Python list so that the last line in the SQL is:
...
AND col3 IN pylist
I am aware of {} and str.format but I don't understand if that's the correct option and how that works.
I think the solution is .format(tuple(pylist)):
pylist = ['A', 'B', 'C', 'D']
s = """
SELECT col1, col2, col3
FROM database.table
WHERE col3 IN {}
""".format(tuple(pylist))
query = spark.sql(s)
Convert to tuple to get "()" instead of "[]" while converting to string
sql_str="SELECT col1, col2, col3 FROM database.table WHERE col3 IN " + str(tuple(pylist))
query = spark.sql(sql_str)
You can also use an f-string with {tuple(pylist)} as shown:
The statement now is:
pylist = ['A', 'B', 'C', 'D']
s = f"""
SELECT col1, col2, col3
FROM database.table
WHERE col3 IN {tuple(pylist)}
"""
query = spark.sql(s)
If it's one element in the list use a conditional statement to fix the DELETE query's WHERE clause:
if len(filesToDelete) > 1:
s = """
DELETE from table_name WHERE s3_file_name in {}
""".format(tuple(filesToDelete))
print(s)
elif len(filesToDelete) == 1:
s = """ DELETE from table_name WHERE s3_file_name = '{}'
""".format(filesToDelete[0])
print(s)
else:
print("Log No Files to Delete")
