pandas 更新mysql,如何使用Pandas DataFrame对数据库表的现有行执行UPDATE？

最新推荐文章于 2024-07-12 14:00:29 发布

董新帅

最新推荐文章于 2024-07-12 14:00:29 发布

阅读量1.9k

点赞数

文章标签： pandas 更新mysql

该博客探讨了如何从MySQL数据库中查询数据，将其加载到Pandas DataFrame中进行修改，然后将更新的行写回相同的数据库表，而不必将整个表拉取并替换。作者考虑了数据量和需要更新的行数，并提供了一个解决方案，涉及创建临时表，删除要更新的行，然后重新插入修改后的行。

摘要由CSDN通过智能技术生成

I am attempting to query a subset of a MySql database table, feed the results into a Pandas DataFrame, alter some data, and then write the updated rows back to the same table. My table size is ~1MM rows, and the number of rows I will be altering will be relatively small (<50,000) so bringing back the entire table and performing a df.to_sql(tablename,engine, if_exists='replace') isn't a viable option. Is there a straightforward way to UPDATE the rows that have been altered without iterating over every row in the DataFrame?

I am aware of this project, which attempts to simulate an "upsert" workflow, but it seems it only accomplishes the task of inserting new non-duplicate rows rather than updating parts of existing rows:

Here is a skeleton of what I'm attempting to accomplish on a much larger scale:

import pandas as pd

from sqlalchemy import