如何将Pandas DataFrame升级到PostgreSQL表?

如何将Pandas DataFrame升级到PostgreSQL表?,第1张

如何将Pandas DataFrame升级到PostgreSQL表?

如果您使用的是PostgreSQL 9.5或更高版本,则可以使用临时表和一条INSERT … ON CONFLICT语句执行UPSERT :

with engine.begin() as conn:    # step 0.0 - create test environment    conn.execute(sa.text("DROp TABLE IF EXISTS main_table"))    conn.execute(        sa.text( "CREATE TABLE main_table (id int primary key, txt varchar(50))"        )    )    conn.execute(        sa.text( "INSERT INTO main_table (id, txt) VALUES (1, 'row 1 old text')"        )    )    # step 0.1 - create Dataframe to UPSERT    df = pd.Dataframe(        [(2, "new row 2 text"), (1, "row 1 new text")], columns=["id", "txt"]    )    # step 1 - create temporary table and upload Dataframe    conn.execute(        sa.text( "CREATE TEMPORARY TABLE temp_table (id int primary key, txt varchar(50))"        )    )    df.to_sql("temp_table", conn, index=False, if_exists="append")    # step 2 - merge temp_table into main_table    conn.execute(        sa.text(""" INSERT INTO main_table (id, txt)  SELECT id, txt FROM temp_table ON ConFLICT (id) DO     UPDATe SET txt = EXCLUDED.txt """        )    )    # step 3 - confirm results    result = conn.execute(sa.text("SELECT * FROM main_table ORDER BY id")).fetchall()    print(result)  # [(1, 'row 1 new text'), (2, 'new row 2 text')]


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/5666719.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-12-16
下一篇 2022-12-16

发表评论

登录后才能评论

评论列表(0条)

保存