pandas panda df.to_sql如果列值存在,则替换或更新行

bkkx9g8r  于 2023-01-01  发布在  其他
关注(0)|答案(1)|浏览(313)

I'm using pandas df.to_sql to inserting rows to postgresql database.

df.to_sql('example_table', engine, if_exists='replace',index=False)

example_table有3列:"id"、"name"、"datetime"
我想在插入之前添加一个检查逻辑,即如果datetime已经存在,则替换或更新存在的行。
有没有这样的东西:

df.to_sql('example_table', engine, if_ datetime_exists='replace',index=False)
b1zrtrql

b1zrtrql1#

没有if_existsql函数。请尝试以下操作:

# Create a DataFrame with the rows you want to insert
df_to_insert = pd.DataFrame({'name': ['Alice', 'Bob', 'Charlie'], 'datetime': ['2022-01-01', '2022-01-02', '2022-01-03']})

# Read the existing rows from the database into a DataFrame
df_existing = pd.read_sql_query('SELECT * FROM example_table', engine)

# Merge the two DataFrames, using the "datetime" column as the key
df_merged = pd.merge(df_to_insert, df_existing, on='datetime', how='left')

# Replace the values in the merged DataFrame with the values from the to_insert DataFrame
# where the "datetime" column is null (indicating that it is a new row)
df_merged.loc[df_merged['datetime'].isnull(), ['name', 'datetime']] = df_to_insert.values

# Write the merged DataFrame to the database
df_merged.to_sql('example_table', engine, if_exists='replace', index=False)

这将在数据库中插入datetime(如果尚未插入),还将在缺少datetime的情况下更新现有行。

相关问题