从 pandas df更新数据库中的现有行 [英] Update existing row in database from pandas df

问题描述

我有一个PostgreSQL数据库. Pandas具有"to_sql"功能，可将数据框的记录写入数据库.但是我还没有找到有关在完成数据框后如何使用pandas更新现有数据库行的文档.

I have a PostgreSQL db. Pandas has a 'to_sql' function to write the records of a dataframe into a database. But I haven't found any documentation on how to update an existing database row using pandas when im finished with the dataframe.

当前，我能够使用pandas read_sql_table将数据库表读入数据框.然后，根据需要处理数据.但是我还无法弄清楚如何将该数据帧写回到数据库中以更新原始行.

Currently I am able to read a database table into a dataframe using pandas read_sql_table. I then work with the data as necessary. However I haven't been able to figure out how to write that dataframe back into the database to update the original rows.

我不想覆盖整个表.我只需要更新最初选择的行即可.

I dont want to have to overwrite the whole table. I just need to update the rows that were originally selected.

推荐答案

一种方法是利用sqlalchemy表类"和session.merge(row)，session.commit():

One way is to make use of an sqlalchemy "table class" and session.merge(row), session.commit():

这里是一个例子:

for row in range(0, len(df)):
    row_data = table_class(column_1=df.ix[i]['column_name'],
                           column_2=df.ix[i]['column_name'],
                           ...
                           )
    session.merge(row_data)
    session.commit()

这篇关于从 pandas df更新数据库中的现有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！