Python string substitution for dynamic column name and values

Question

I have following query to retrieve data from PostgreSQL.

select_stmt = "SELECT * FROM kart_user WHERE token = %(token)s"
cursor.execute(select_stmt, { 'token': 2 })

Some case dictionary may contain multiple keys and values
Eg-

{ 'token': 10,'custid':100,cycle:"yes" }

In this case how i can dynamically specify the column name and its values??

I have tried..

select_stmt = "SELECT * FROM kart_user WHERE dist.keys() = %(dist.keys())s"
cursor.execute(select_stmt, dist)

But no luck. Thank you for your response..

did you try to iterate through the key-value pairs and add them one by one to the SQL string? — Remigius Stalder, Dec 14 '15 at 07:16
I have already thought that way , please suggest some other better way to solve this issue .thank you) — Abdul Razak, Dec 14 '15 at 07:22

Hardik Patel · Accepted Answer · 2015-12-19T06:57:24.420

As per your tried statement, I assume you have three keys and three values so you want to execute SELECT statement for all three one by one. Below solution will help you.

d = { 'token': 10,'custid':100,cycle:"yes" }
select_stmt = "SELECT * FROM kart_user WHERE {key} = '{value}'"
allowed_col = ['token', 'custid'] # Predefined column names to allow from coming dictionary.
for key, value in d.iteritems():
    if  key in allowed_col:
        select_stmt = select_stmt.format(key=key, value=value)
        cursor.execute(select_stmt, dist)
        # Further fetching code you can execute. Here you will get select statement for particular key and value.

@ For and conditions

d = { 'token': 10,'custid':100,'cycle':"yes" }
select_stmt = "SELECT * FROM kart_user WHERE"
allowed_col = ['token', 'custid'] # Predefined column names to allow from coming dictionary.
for key, value in d.iteritems():
    if  key in allowed_col:
        condition = "{key} = '{value}'".format(key=key, value=value)
        select_stmt = select_stmt + " and " + condition
cursor.execute(select_stmt, dist)
# Further fetching code you can execute. Here you will get select statement for particular key and value.

One liner solution for above answer,

d = { 'token': 10,'custid':100,'cycle':"yes" }
condition = ' and '.join([key+'="'+str(value)+'"' for key,value in d.iteritems() if key in allowed_col])
select_stmt = "SELECT * FROM kart_user WHERE"
select_stmt = " ".join([select_stmt, condition])
cursor.execute(select_stmt, dist)
# Further fetching code you can execute. Here you will get select statement for particular key and value.

If your query is different then what I answered then reply on this. Thanks.

Depending on where the dictionary data comes from this method is vulnerable to SQL injection. — kylieCatt, Dec 14 '15 at 07:58
That is right, so to protect that we can prepare one list of allowed columns only and just add one if condition in for loop. — Hardik Patel, Dec 14 '15 at 08:34
Those are column names of the table taken from your example. — Hardik Patel, Dec 14 '15 at 08:39
This 'allowed_col' list is only required to avoid SQL injection. Otherwise you can remove that if condition and this list is not required. — Hardik Patel, Dec 14 '15 at 08:43
yes it was worked for only single column and its values , what about and condetions — Abdul Razak, Dec 18 '15 at 09:19

Python string substitution for dynamic column name and values

1 Answers1