Python 函数使数据库更新过多

Question

这里有点令人费解。

我有一个数据库表，其中有一组统计数据作为列，一组非常相似的平均值。

设置一个函数来多次迭代我的数据集并多次更新数据库中的行真的很容易。

但是它的速度慢得离谱，而且一个大而长的脚本只执行 1 组更新 1 次将花费大量时间来编写并且维护起来会很痛苦。

我想知道是否有一种方法既可以成为 OOP，又不会做太多多余的搜索/数字运算。

我想知道是否有更简单的方法...SSIS 会让我只提取一次数据，最后合并它，然后将其放回数据库。但我不确定如何使用 Python 来做到这一点......

所以我在 python 中定义了一个平均执行 n 天的函数:

def moving_average(conn, cur, dates, days, stockid, col_name):
#    print days
    if (len(dates) < days):
        print "WARNING: Cannot do a " + str(days) + " day moving average for stock id " + str(stock_id) + "!  Not enough data!"
    else:
        for x in range (0, len(dates) - int(days) - 1):
            row_id = dates[x][0]
            numerator = 0
            for i in range (0, days - 1):
                numerator = numerator + dates[x][3]
                moving_average_for_period = round(numerator / days, 2)
                sql = "UPDATE historical_prices SET " + col_name + " = " + str(moving_average_for_period) + " WHERE id = " + str(row_id) + ";"
                cur.execute(sql) 
        conn.commit()

到目前为止一切顺利。

然后我调用:

  # now do the moving averages
    moving_average(conn,cur,dates,5,stock_id,'five_day_ma')
    moving_average(conn,cur,dates,10,stock_id,'ten_day_ma')
    moving_average(conn,cur,dates,15,stock_id,'fifteen_day_ma')
    moving_average(conn,cur,dates,30,stock_id,'thirty_day_ma')
    moving_average(conn,cur,dates,60,stock_id,'sixty_day_ma')
    moving_average(conn,cur,dates,120,stock_id,'onetwenty_day_ma')

看到我的问题了吗？我的 I/O 是 6 倍。

在此先感谢您的帮助。

干杯，

丹

Answer 1

虽然纯 SQL 通常比来自 Python(或 Java 等)的一次一个更新更快，但在一个大表上进行更新可能会根据其大小迅速变得指数级慢，即使是相对较小的数字正在更新的行数。

如果您确实采用纯 SQL 路线(我的意思是从 Python 执行 UPDATE，这会影响大量行)——在这种情况下我肯定会推荐这种方法——我建议使用 LIMIT 和 OFFSET 对其进行分块，这样每个 UPDATE 都会小得多。 Postgres 通常能够更好地处理这些问题。 (我遇到过这样的情况，当我(通过 Python)以 5000 block 的形式完成时，UPDATE 在 5 个多小时后无法在一个 block 中完成，而在 30 分钟内完成。

当然，如果您的表足够小，您可能不会遇到这个问题，但请记住这一点。