在psycopg2中构建动态更新查询

Question

我必须为postgresql构造一个动态更新查询。 它是动态的，因为我必须事先确定要更新哪些列。在

给出一个示例表：

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

然后我将以编程方式构造“set”子句

^{pr2}$

之后，我必须构建更新查询。我被困在这里了。 我必须构造以下sql更新命令：

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

如何使用psycopg2参数化命令执行此操作？（即，如果dict不为空，则循环该dict并生成set子句）？在

更新

在我睡觉的时候，我自己找到了解决办法。它是动态的，正是我想要的样子：-）

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)

结果是我需要什么以及如何需要（尤其是可空列和可引用列）：

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

Answer 1

实际上有一种稍微干净一点的方法，使用the alternative column-list syntax：

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

一个没有python格式的选项，它使用psycopg2的AsIs函数作为列名（尽管这并不妨碍您对列名进行SQL注入）。Dict被命名为data：

update_statement = f'UPDATE foo SET (%s) = %s WHERE id_column=%s'
columns = data.keys()
values = [data[column] for column in columns]
query = cur.mogrify(update_statement, (AsIs(','.join(columns)), tuple(values), id_value))

Answer 3

不需要动态SQL。假设a不可为空，b可为空。在

如果要同时更新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)

输出：

^{pr2}$

如果不想更新：

_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