CODEWITHSUNDEEP
python-3.xpostgresql
Casanalo
Casanalo

Reputation: 15

How to create a dynamic UPDATE query in SQL using python

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.

Upvotes: 0

Views: 1523

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19665

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.

Upvotes: 2

Related Questions