Python Peewee MySQL 批量更新

我正在使用 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." 

请您参考如下方法:

新的请您参考如下方法:是使用 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)