Use dictionary to replace parameters in SQL query

Question

I have SQL query as a string:

SELECT DISTINCT a, b AS value FROM ${source} WHERE b IN (${string}) AND slice_start >= ${date} AND slice_start <= ${date1};

And I need to replace the parameters using dictionary , that comes from JSON file looking like this:

my_dict = {"source": "table", "string": "abc", "date": "2020-01-01", "date1": "2020-01-02"}

I am confused how to replace every string that starts with $ and how to attach " (but not on source) at the end and at the begging of every parameter, so the SQL query can be executed later. This is where I am now:

for k, v in target_groups_queries.items():
    if isinstance(v, dict):
        for k1, v1 in v.items():
            queries = v1['sql'].split()
            final_string = ' '.join(str(my_dict.get(word, word)) for word in queries)
            print(final_string)

    else:
        pass

How is this string being generated? Is there any way to remove the $s beforehand? — mechanical_meat
– mechanical_meat, Commented Feb 14, 2020 at 21:43

mechanical_meat · Accepted Answer · 2020-02-14 15:39:17Z

1

You can use dictionary- or keyword-unpacking as follows:

my_dict = {"source": "table", "string": "abc", "date": "2020-01-01", 
           "date1": "2020-01-02"}
sql = '''SELECT DISTINCT a, b AS value 
         FROM ${source} 
         WHERE b IN (${string}) 
             AND slice_start >= ${date} 
             AND slice_start <= ${date1};'''                                               

sql.format(**my_dict)

Result:

In [32]: from pprint import pprint                                                                  

In [33]: pprint(sql.format(**my_dict))                                                              
('SELECT DISTINCT a, b AS value FROM $table WHERE b IN ($abc) AND slice_start '
 '>= $2020-01-01 AND slice_start <= $2020-01-02;')

answered Feb 14, 2020 at 15:39

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

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

3 Comments

Y.C.T Over a year ago

This works, but I need also " at the beginning and at the end of the parameters and the $ removed.

mechanical_meat Over a year ago

@Y.C.T: which database interface are you using? Because that should do the quoting on your behalf.

Y.C.T Over a year ago

The queries will be executed on Hive.

Collectives™ on Stack Overflow

Use dictionary to replace parameters in SQL query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related