1
votes

Insérer dans la table postgreSQL à partir de pandas avec mise à jour "en conflit"

J'ai un DataFrame pandas que je dois stocker dans la base de données. Voici ma ligne de code actuelle pour l'insertion:

insert_rows.to_sql(table, con=engine, if_exists='append', index_label=index_col)

Cela fonctionne bien si aucune des lignes de df n'existe dans ma table. Si une ligne existe déjà, j'obtiens cette erreur:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) duplicate key
value violates unique constraint "mypk"
DETAIL:  Key (id)=(42) already exists.
 [SQL: 'INSERT INTO mytable (id, owner,...) VALUES (%(id)s, %(owner)s,...']
 [parameters:...] (Background on this error at: http://sqlalche.me/e/gkpj)

et rien n'est inséré.

PostgreSQL a optionnel ON CONFLICT clause, qui pourrait être utilisée pour UPDATE les lignes de table existantes. J'ai lu la page de manuel pandas.DataFrame.to_sql et je n'ai trouvé aucun moyen d'utiliser ON CONFLICT dans la fonction DataFrame.to_sql () .

J'ai envisagé de fractionner mon DataFrame en deux basé sur ce qui est déjà dans la table db. Alors maintenant, j'ai deux DataFrames, insert_rows et update_rows , et je peux exécuter en toute sécurité

df.to_sql(table,con=engine,if_exists='append',index_label=index_col)

Mais alors, il semble ne pas être UPDATE équivalent à DataFrame.to_sql () . Alors, comment mettre à jour la table en utilisant DataFrame update_rows?

postgresql python pandas dataframe insert-update

0 commentaires

3 Réponses :

2
votes

Si vous remarquez dans la documentation to_sql , il est fait mention d'un argument method qui accepte un appelable. La création de cet appelable devrait vous permettre d'utiliser les clauses Postgres dont vous avez besoin. Voici un exemple d'appelable mentionné dans la documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#io-sql-method

C'est assez différent de ce dont vous avez besoin, mais suivez les arguments passés à cet appelable. Ils vous permettront de construire une instruction SQL régulière.

0 commentaires

1
votes

Pour donner suite à la réponse de Brendan avec un exemple, voici ce qui a fonctionné pour moi:

CREATE TABLE test_table(
    col_a text NOT NULL,
    col_b text NOT NULL,
    col_c text,
    UNIQUE (col_a, col_b)
)

où dans cet exemple le schéma serait quelque chose comme:

import os
import sqlalchemy as sa
import pandas as pd
from sqlalchemy.dialects.postgresql import insert


engine = sa.create_engine(os.getenv("DBURL"))
meta = sa.MetaData()
meta.bind = engine
meta.reflect(views=True)


def upsert(table, conn, keys, data_iter):
    upsert_args = {"constraint": "test_table_col_a_col_b_key"}
    for data in data_iter:
        data = {k: data[i] for i, k in enumerate(keys)}
        upsert_args["set_"] = data
        insert_stmt = insert(meta.tables[table.name]).values(**data)
        upsert_stmt = insert_stmt.on_conflict_do_update(**upsert_args)
        conn.execute(upsert_stmt)


if __name__ == "__main__":
    df = pd.read_csv("test_data.txt")
    with db.engine.connect() as conn:
        df.to_sql(
            "test_table",
            con=conn,
            if_exists="append",
            method=upsert,
            index=False,
        )

0 commentaires

0
votes

Si quelqu'un voulait construire sur la réponse de zdgriffith et générer dynamiquement le nom de la contrainte de table, vous pouvez utiliser la requête suivante pour postgreSQL:

select distinct tco.constraint_name
from information_schema.table_constraints tco
         join information_schema.key_column_usage kcu
              on kcu.constraint_name = tco.constraint_name
                  and kcu.constraint_schema = tco.constraint_schema
                  and kcu.constraint_name = tco.constraint_name
where kcu.table_name = '{table.name}'
  and constraint_type = 'PRIMARY KEY';

Vous peut ensuite formater cette chaîne pour remplir table.name dans la méthode upsert () .

Je n'avais pas non plus besoin de la méta . liez les lignes et meta.reflect () . Ce dernier sera de toute façon bientôt obsolète.

0 commentaires