How to create a dynamic UPDATE query in SQL using python

Question

I have a webpage that allows a user to update some data, but the user can update all data or just part of the data, for example, one user can make the following update:

{key1: "value", key2: "value", key3: "value"}

But another user can update maybe just the key 1 field.

My question is, how can I construct the SQL statement such that I update the information that I get from the user

def update_info(data: dict, id: int):
    query : """ 
            UPDATE table
            SET (the key/value pairs of the data)
            WHERE id = %(id)s
            """
    data["id"] = id
    database_call(query,data)

I ask this because I know I can probably do it with a f string but I am worried of any kind of SQL injection, also, the data received has been already validated with cerberus.

Assuming you are using psycopg2(hint: include the driver you are using) then see Sql. — Adrian Klaver
– Adrian Klaver, Commented Jul 9, 2021 at 19:27

Adrian Klaver · Accepted Answer · 2021-07-09 21:51:27Z

2

So as an example of using the sql module:

from psycopg2 import sql

data = {"id": 1, "key1": "value1", "key2": "value2", "key3": "value3"}

update_fields = list(data.keys())

update_sql = sql.SQL("""UPDATE
        table
    SET
        ({}) = ({})
    WHERE id = %(id)s
    """).format(sql.SQL(", ").join(map(sql.Identifier, update_fields)),
                sql.SQL(", ").join(map(sql.Placeholder, update_fields)))
cur = con.cursor()
cur.execute(update_sql, data)
con.commit()

This is obviously not tested on your data, but it should help as a rough guide as to what to do.

answered Jul 9, 2021 at 21:51

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

2 Comments

Casanalo Over a year ago

Thank you so much! That's what I needed!

Adrian Klaver Over a year ago

Will you accept the answer, it would be appreciated.

Collectives™ on Stack Overflow

How to create a dynamic UPDATE query in SQL using python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related