Create a dynamic update request in psycopg2

Question

I need to build a dynamic update request for postgresql. Its dynamics, because in advance I have to determine which columns to update.

Given a sample table:

create table foo (id int, a int, b int, c int)

Then I will program the sentence "set"

_set = {}
_set['a'] = 10
_set['c'] = NULL

After that I need to create an update request. And here I am stuck. I have to build this sql update command:

update foo set a = 10, b = NULL where id = 1

How to do this with the parameterized psycopg2 command? (i.e. looping through a dict if it's not empty and build a set clause)?

UPDATE

While I was sleeping, I found a solution on my own. It is dynamic as I wanted to be :-)

create table foo (id integer, a integer, b integer, c varchar)

updates = {}
updates['a'] = 10
updates['b'] = None
updates['c'] = 'blah blah blah'
sql = "upgrade foo set %s where id = %s" % (', '.join("%s = %%s" % u for u in updates.keys()), 10)
params = updates.values()
print cur.mogrify(sql, params)
cur.execute(sql, params)

And the result is what and how I need it (especially columns with zero value and quotation marks):

"upgrade foo set a = 10, c = 'blah blah blah', b = NULL where id = 10"

Answer 1

, :

sql_template = "UPDATE foo SET ({}) = %s WHERE id = {}"
sql = sql_template.format(', '.join(updates.keys()), 10)
params = (tuple(addr_dict.values()),)
print cur.mogrify(sql, params)
cur.execute(sql, params)

Answer 2

SQL. , a NULL b NULL.

a, b:

_set = dict(
    id = 1,
    a = 10,
    b = 20, b_update = 1
)
update = """
    update foo
    set
        a = coalesce(%(a)s, a), -- a is not nullable
        b = (array[b, %(b)s])[%(b_update)s + 1] -- b is nullable
    where id = %(id)s
"""
print cur.mogrify(update, _set)
cur.execute(update, _set)

:

update foo
set
    a = coalesce(10, a), -- a is not nullable
    b = (array[b, 20])[1 + 1] -- b is nullable
where id = 1

:

_set = dict(
    id = 1,
    a = None,
    b = 20, b_update = 0
)

:

update foo
set
    a = coalesce(NULL, a), -- a is not nullable
    b = (array[b, 20])[0 + 1] -- b is nullable
where id = 1