擅长:python、mysql、java
<p>catver的方法很有效。然而,我发现创建一个临时表被证明是更有效的。</p>
<pre><code>import psycopg2
from psycopg2.extensions import AsIs
rows = zip(d.keys(), d.values())
curs.execute("""
CREATE TEMP TABLE codelist(DKEY INTEGER, DVALUE TEXT)
ON COMMIT DROP""")
curs.executemany("""
INSERT INTO codelist (DKEY, DVALUE)
VALUES(%s, %s)""",
rows)
for i in [(AsIs('furniture'), AsIs('furniture')), (AsIs('plant'), AsIs('plant'))]:
curs.execute("""
UPDATE my_table
SET %s = codelist.DVALUE
FROM codelist
WHERE codelist.DKEY = my_table.%s;
""", i)
</code></pre>
<p>注意:这个例子可能不太有效,因为我正在用<code>TEXT</code>值替换<code>INTEGER</code>。这可能会引发错误<code>ERROR: operator does not exist: integer = character varying</code>。
在这种情况下,<a href="https://stackoverflow.com/a/25358092/2075003">this answer</a>可能会有帮助。</p>