在 psycopg2 中构建动态更新查询 [英] Build a dynamic update query in psycopg2

问题描述

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

I have to construct a dynamic update query for postgresql. Its dynamic, because beforehand I have to determine which columns to update.

给定一个示例表:

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

然后我会以编程方式构造set"子句

Then I will construct programmatically the "set" clause

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

之后我必须构建更新查询.我被困在这里.我必须构造这个 sql Update 命令:

After that I have to build the update query. And here I'm stuck. I have to construct this sql Update command:

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

如何使用 psycopg2 参数化命令执行此操作?(即循环遍历字典，如果它不为空并构建 set 子句)?

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

更新

当我睡觉时，我自己找到了解决方案.它是动态的，正是我想要的 :-)

While I was sleeping I have found the solution by myself. It is dynamic, exactly how 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 needed (especially the nullable and quotable columns):

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

推荐答案

实际上有一个更简洁的方法，使用 替代列列表语法:

There is actually a slightly cleaner way to make it, using 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)

这篇关于在 psycopg2 中构建动态更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！