如何将字典项插入到PostgreSQL表中

Question

所以，我已经使用Psycopg2将我的Postgresql数据库连接到Python，并提取了两个特定列进行更新。我已经将第一列用作Python字典中的键，并对第二列运行了一些函数，并使用结果作为字典中的值。现在我想做的是将这些值作为新列添加回Postgresql表中，但我希望它们与字典中配对的正确键一起配对。本质上，我想将字典值作为新列插入，并选择它们属于Postgresql表中哪个“键”（但我不想手动分配它们，因为希望有更好的方法）。

Postgresql表

     |col1   |col2  |col3   | ... | coln  
row1 | a1    | b1   | c1    | ... | n1  
row2 | a2    | b2   | c2    | ... | n2  
...  | ...   | ...  | ...   | ... | n...  
rowm | am    | bm   | cm    | ... | nm

这是我用Python制作的字典，其中f()是在变量上运行的一系列函数：

{ 
    a1 : f(c1),  
    a2 : f(c2),   
    ... : ... 
}

现在我的目标是将值列添加回到表格中，使其对应原始的键。理想情况下，它应该看起来像这样：

     |col1|col2|col3| ... |newcol| coln  
row1 | a1 | b1 | c1 | ... | f(c1)| n1  
row2 | a2 | b2 | c2 | ... | f(c2)| n2  
...  | ...| ...| ...| ... |  ... | n...  
rowm | am | bm | cm | ... | f(cm)| nm

我知道我可以将列插入表中，但不确定如何将其与键配对。非常感谢任何帮助！

- dtluther

---

“col1” 是数据库表的主键吗？（它是Python字典中的关键字） - joop

@joop，不，col1在数据库中不是主键，因为实际上每个值的列1都有一对。有一个名为record_id的列，它基本上与行号相关联，可以唯一地标识每一行（我认为这就是主键，对吗？） - dtluther

Answer 1

您需要类似以下的UPDATE语句：

import psycopg2

con = psycopg2.connect('your connection string')
cur = connection.cursor()
# add newcol
cur.execute('ALTER TABLE your_table ADD COLUMN newcol text;')
con.commit()
for k,v in your_dict.iteritems():
    cursor.execute('''UPDATE your_table 
                         SET newcol = (%s) 
                       WHERE col1 = (%s);''',(v,k))
    conn.commit()
cur.close()
con.close()

- mechanical_meat

Answer 2

使用单个查询更新所有行。首先将字典转换为元组列表：

l = [(k,v) for k,v in my_dict.items()]

Psycopg将Python元组列表适配为记录数组。然后在from子句中对数组进行unnest操作：

query = '''
    update t
    set colX = s.val::text
    from unnest(%s) s (key integer, val unknown)
    where t.col1 = s.key
'''

print cursor.mogrify(query, (l,))
cursor.execute(query, (l,))

从unnest返回的记录需要声明其元素类型。对于字符串值，需要将它们的类型声明为unknown，并在分配或比较时进行转换到适当的类型。对于所有其他类型，只需像往常一样声明即可。

- Clodoaldo Neto