Python use dictionary to replace variables in a string

Question

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.

You should probably be using a prepared statement here rather than trying to bind and concatenate the statement yourself. What is your SQL version? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 12, 2021 at 0:47
ok i would like to try this approach but appreciate alternatives. postgresql — paranormaldist
– paranormaldist, Commented Feb 12, 2021 at 0:48
Have you ever heard of SQL injection vulnerabilities? You're in serious danger of creating one. — Brian McCutchon
– Brian McCutchon, Commented Feb 12, 2021 at 0:53
i'm only trying to create a reference to a column value in the effort of automation — paranormaldist
– paranormaldist, Commented Feb 12, 2021 at 0:54
Why are you using two brackets like that? {{cols_1}}? Is this supposed to be a jinja template?? — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Feb 12, 2021 at 1:04

Gopal Gautam · Accepted Answer · 2021-02-12 01:15:54Z

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

answered Feb 12, 2021 at 1:15

Gopal Gautam

3691 gold badge3 silver badges9 bronze badges

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

3 Comments

paranormaldist Over a year ago

ELI5 , why is $cols_1 much better than {{cols_1}}? still learning

Gopal Gautam Over a year ago

This is what supported by Template library. From documentation: "Template strings support $-based substitutions, using the following rules:"

Gopal Gautam Over a year ago

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: stackoverflow.com/questions/34360603/…

Akshay Sehgal · Accepted Answer · 2021-02-12 01:15:17Z

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

edited Feb 12, 2021 at 1:15

answered Feb 12, 2021 at 0:54

Akshay Sehgal

19.4k3 gold badges26 silver badges57 bronze badges

14 Comments

paranormaldist Over a year ago

this is what i'm looking for but i'm running into an error 'tuple' object has no attribute 'replace'

Akshay Sehgal Over a year ago

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.

paranormaldist Over a year ago

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?

Akshay Sehgal Over a year ago

Do read this w3schools.com/sql/sql_injection.asp

Akshay Sehgal Over a year ago

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.

|

Chris · Accepted Answer · 2021-02-12 01:15:10Z

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

edited Feb 12, 2021 at 1:15

answered Feb 12, 2021 at 0:49

Chris

29.8k3 gold badges34 silver badges56 bronze badges

1 Comment

Open AI - Opting Out Over a year ago

What are the dangers to this?

Collectives™ on Stack Overflow

Python use dictionary to replace variables in a string

3 Answers 3

3 Comments

14 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

14 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related