如何使用Python / psycopg2高效地更新大型PostgreSQL表中的列？

Question

如何使用Python / psycopg2高效地更新大型PostgreSQL表中的列？

5

我有一个在PostgreSQL 9.4数据库中拥有大约1000万行的大型表格。它看起来有点像这样：

gid | number1 | random | result | ...
 1  |    2    |  NULL  |  NULL  | ...
 2  |   15    |  NULL  |  NULL  | ...
... |   ...   |   ...  |  ...   | ...

现在我想根据number1的函数来更新random和result列。这意味着至少需要在数据库外部的脚本中生成random。由于我的RAM有限，我想知道如何使用psycopg2进行高效处理。我认为我面临两个问题：如何在不使用过多RAM的情况下获取数据以及如何将其重新放回去。简单的方法看起来像这样：

curs.execute("""SELECT gid1, number1 FROM my_table;""")
data = curs.fetchall()

result = []
for i in data:
    result.append((create_random(i[1]), i[0]))
curs.executemany("""UPDATE my_table
                    SET random = %s
                    WHERE gid = %s;""",
                 results)
curs.execute("""UPDATE my_table
                SET result = number1 * random;""")

然而，这会消耗掉我所有的内存，并且需要很长时间才能更新我的表格。

有什么更明智的策略吗？数据库正在被独占访问并且可以被锁定。不幸的是，PostgreSQL的随机函数对我的情况不适用。

- n1000

请定义“太多的RAM”，因为1000万* 3个整数是240MB，这比许多系统可以处理的要多。此外，您的生成逻辑是否可以实现为存储过程？ - spectras

你能使用(PL/Python)[http://www.postgresql.org/docs/9.4/static/plpython.html]吗？如果可以，你可以编写自己的替代Postgres随机函数的代码，并将其作为一个`UPDATE`语句在数据库中运行。 - foz

@foz 哦，有趣！但我想这在我的情况下不起作用，因为我需要numpy包...？ - n1000

@n1000 嗯，我自己没有尝试过，但是这里有一个例子，在 PL/Python 函数中导入 numpy：https://github.com/ihuston/plpython_examples/blob/master/simple_examples.sql - foz

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Clodoaldo Neto · Accepted Answer

unnest函数可将数据一次性展开：

def create_random(i):
    return random() * i

curs.execute("select gid, number from t;")
data = curs.fetchall()

results = []
for i in data:
    results.append((create_random(i[1]), i[0]))

curs.execute("""
    update t
    set
        rnd = s.rnd,
        result = number * s.rnd
    from unnest(%s) s(rnd numeric, gid integer)
    where t.gid = s.gid;
""", (results,))

con.commit()

表t：

create table t (
    gid integer,
    number integer,
    rnd float,
    result float
);