0

Similar questions out there, but not quite my use case. I would like to use a dictionary reference to replace variables within a string. In this case, the string is a sql statement, but not too relevant.

SQL with variables {}-

qry = """SELECT
    SUM(CASE WHEN {{cols_1}} is null AND {{cols_2}} > 0 THEN 1 ELSE 0 END) AS output
    FROM mytable"""

dictionary -

dict = {'cols_1': 'id', 'cols_2': 'weather'}

So then it would end up like this -

qry = """SELECT
    SUM(CASE WHEN id is null AND weather > 0 THEN 1 ELSE 0 END) AS output
    FROM mytable"""

And I'd like to replace cols_1 and cols_2 with dictionary values. But I'm not sure how to do this?

def substitute_values(qry, dict):

    if dict:
        qry = qry.replace('{{cols_1}}','{{cols_2}}'),dict[]
    
    return qry

After spinning my wheels a bit, appreciate any guidance.

paranormaldist
  • 489
  • 5
  • 16

3 Answers3

1

Use Template. Easy:

from string import Template
qry = """SELECT SUM(CASE WHEN $cols_1 is null AND $cols_2 > 0 THEN 1 ELSE 0 END) AS output FROM mytable"""
dict = {'cols_1': 'id', 'cols_2': 'weather'}
qry = Template(qry).safe_substitute(dict)

Documentation here: https://docs.python.org/3/library/string.html#template-strings

Gopal Gautam
  • 369
  • 1
  • 3
  • 9
  • ELI5 , why is $cols_1 much better than {{cols_1}}? still learning – paranormaldist Feb 12 '21 at 01:25
  • 1
    This is what supported by Template library. From documentation: "Template strings support $-based substitutions, using the following rules:" – Gopal Gautam Feb 12 '21 at 01:28
  • 1
    Still it is doable using Template to put {{}} instead of $. But then it will be very complex as you have to create your own Templating Class. Furthermore, check this: https://stackoverflow.com/questions/34360603/python-template-safe-substitution-with-the-custom-double-braces-format – Gopal Gautam Feb 12 '21 at 01:38
0

You can use str.format twice:

print(qry.format().format(**d))

Output:

SELECT
    SUM(CASE WHEN id is null AND weather > 0 THEN 1 ELSE 0 END) AS output
    FROM mytable
Chris
  • 29,127
  • 3
  • 28
  • 51
0

Is this what you are looking for?

qry = """SELECT
    SUM(CASE WHEN {{cols_1}} is null AND {{cols_2}} > 0 THEN 1 ELSE 0 END) AS output
    FROM mytable"""

d = {'cols_1': 'id', 'cols_2': 'weather'}


for k,v in d.items():
    qry = qry.replace('{{'+k+'}}', v)
    
print(qry)

SELECT
    SUM(CASE WHEN id is null AND weather > 0 THEN 1 ELSE 0 END) AS output
    FROM mytable
Akshay Sehgal
  • 18,741
  • 3
  • 21
  • 51
  • this is what i'm looking for but i'm running into an error ```'tuple' object has no attribute 'replace'``` – paranormaldist Feb 12 '21 at 00:57
  • are you defining the dictionary as `d` instead of `dict`? try to not use `dict` as variable name. Also, is your `qry` a string or a tuple? Try running the whole code i have provided first to test if it works. then see if you are providing `qry` in the same form. – Akshay Sehgal Feb 12 '21 at 00:57
  • ok this will work for me, but i am a newbie so would like to understand what the issue/"danger" of doing this approach is? – paranormaldist Feb 12 '21 at 01:07
  • Do read this https://www.w3schools.com/sql/sql_injection.asp – Akshay Sehgal Feb 12 '21 at 01:16
  • I see the documentation has a hefty warning, but I'm still not quite understanding .. Can someone explain a bit more what issue of replacing {{ }} variables is ? – paranormaldist Feb 12 '21 at 01:32
  • If someone (with malicious intent) passes a keyword that you unknowingly replace and run your query, they can cause serious damage to your database. Example, someone sends the string "1=1" which is always True as a condition, instead of a valid condition. This will cause them to get full read access to the database. – Akshay Sehgal Feb 12 '21 at 01:35
  • but unless they have access to my notebook, how would they do so? – paranormaldist Feb 12 '21 at 01:37
  • They wont, its just not a good practice to use code like this. – Akshay Sehgal Feb 12 '21 at 01:37
  • i see, so better practice to not use {{ }} and use $ instead? – paranormaldist Feb 12 '21 at 01:38
  • 1
    No thats not the point. both are equally bad. Basically replacing any string from another input variable (which maybe input by another user) in a SQL query is bad practice. $ or {{}} doesnt matter. The $ is only for the specific library called Template. It doesnt protect againt potential SQL injections. – Akshay Sehgal Feb 12 '21 at 01:39
  • Since you are doing all of this on your local and just for automation of your work, i dont think its an issue, but either way, doing any of this is a bad practice in production. – Akshay Sehgal Feb 12 '21 at 01:41
  • ah ok i see, thank you for explaining. yes this won't be going to production, just for local automation purposes – paranormaldist Feb 12 '21 at 01:42
  • Exactly, be very careful where you take the dictionary from. Thats the point. – Akshay Sehgal Feb 12 '21 at 01:42
  • Do mark the answer if it helped you and do upvote it if it’s well written to your liking. This encourages me to help solve your future questions :) – Akshay Sehgal Feb 12 '21 at 01:43