Python Peewee MySQL 批量更新

Question

我正在使用 Python 2.7、Peewee 和 MySQL。如果 csv 中存在订单号，我的程序会从 csv 文件读取并更新该字段。可能有 2000-3000 次更新，我正在使用天真的方法一条一条地更新记录，这太慢了。我已经从使用 Peewee 更新转为使用 Raw 查询，速度更快一些。但是，它仍然很慢。我想知道如何在不使用循环的情况下以更少的事务更新记录。

def mark_as_uploaded_to_zoho(self, which_file):
    print "->Started marking the order as uploaded to zoho."
    with open(which_file, 'rb') as file:
        reader = csv.reader(file, encoding='utf-8')
        next(reader, None) ## skipping the header

        for r in reader:
            order_no = r[0]
            query = '''UPDATE sales SET UploadedToZoho=1 WHERE OrderNumber="%s" and UploadedToZoho=0''' %order_no
            SalesOrderLine.raw(query).execute()

    print "->Marked as uploaded to zoho."

Answer 1

新的最佳答案是使用 Model.bulk_update与交易。可以更改 batch_size 参数以调整性能。

# CSV generator for efficient memory usage
def gen_csv(file_name, header=True, **kwargs):
  with open(file_name, 'r') as f:
    reader = csv.reader(f, **kwargs)
    if header:
      next(reader, None)
    for row in reader:
      yield row

# Update based on order number
with database.atomic():
  rows = gen_csv(file_path, encoding='utf-8')
  SalesOrderLine.bulk_update(rows, fields=['OrderNumber'], batch_size=200)