python - python 。优化将DF附加到MySQL数据库

我在数据库中添加如下模型的输出：

cursor = conn.cursor()
for i in range(len(df)):
    UserId = df.loc[i, 'UserId']
    Timestamp = df.loc[i, 'Timestamp']
    ChurnPropensity = df.loc[i, 'ChurnPropensity']

    sql = "INSERT INTO DB_Name (UserId, Timestamp, ChurnPropensity) VALUES ({},'{}',{});".format(UserId, Timestamp, ChurnPropensity)

    cursor.execute(sql)

conn.commit()

但是，由于for循环，需要花费很长时间。您将如何改善计算时间？

敬礼

最佳答案

试试这个方法。理想情况下，它应该加快执行速度。

query = "INSERT INTO DB_Name (UserId, Timestamp, ChurnPropensity) VALUES ({},'{}',{});"
df.apply(lambda row: cursor.execute(query.format(row['UserId'],
                                                 row['Timestamp'],
                                                 row['ChurnPropensity'])),
axis=1)

根据我的经验，execute方法本身很慢，因此您可以加快
 尝试一次执行多个查询。

query = "INSERT INTO DB_Name (UserId, Timestamp, ChurnPropensity) VALUES ({},'{}',{});"
queries_list = df.apply(lambda row: query.format(row['UserId'],
                                            row['Timestamp'],
                                            row['ChurnPropensity']),  axis=1).values.tolist()

queries = ' '.join(queries_list)
cursor.execute(queries, multi=True)