I am writing a Python function that does string formatting for a SQL query. Due to this, the string formatting needs to be very precise.
Currently, I have a SQL query template string and I am trying to fill out values using str.format().
Imagine something like:
sql_query_template.format(arg1=arg1, arg2=arg2)
An issue I am running into is the placement of quotation marks of Python strings.
Desired result:
(ARRAY[‘blah’, ‘25’], ‘test_group_1’, '243'),(ARRAY[‘blah2’, '12'], ‘test_group_2', '21')
- The goal is to create tuples while using
ARRAY[]keyword, it is important forARRAY[]to not turn into‘ARRAY[]’or for the entire tuple to get encased by quotes -> SQL needs to treat the keywords as keywords.
Current result:
(‘ARRAY[blah, 25]’, ‘test_group_1’, '243'),(‘ARRAY[blah2, 12]’, ‘test_group_2', '21')
- Here
ARRAY[]is being wrapped in quotation marks (ex:‘ARRAY[blah, 25]') - This is problematic because Presto won’t recognize
ARRAYas a keyword and is treating‘ARRAY[]’as a string
Current snippet:
# dummy example:
test_dict = json.loads(
json.dumps(
[
{
"parameter_1": ["blah", "25"],
"parameter_2": "test_group_1",
"parameter_3": "243",
},
{
"parameter_1": ["blah2", "12"],
"parameter_2": "test_group_2",
"parameter_3": "21",
},
]
)
)
list_of_tuples = []
for dict in test_dict:
list_of_tuples.append(
(
"ARRAY[{}]".format(",".join(dict[“parameter_1"])),
dict["parameter_2"],
f"{dict[parameter_3]}",
)
)
formatted_tuples = ",".join(
str(tup) for tup in list_of_tuples
)
print(sql_query_template.format(arg1=formatted_tuples))
Is it possible to write Python code in such a way where string formatting will insert certain parts that aren’t wrapped up in quotes?