CODEWITHSUNDEEP
pythonpostgresqlpsycopg2postgresql-9.1
Punter Vicky
Punter Vicky

Reputation: 16992

psycopg2.copy_expert - insert only new data

I am using psycopg2.copy_expert to copy data from CSV to PostgreSQL DB. I run this every day and I want only the new data to be inserted. If data is already present then no action must be perfomed. Is there a way to leverage copy_expert function to do this out of the box or do I have code his logic myself and create a new CSV file that contains only the delta changes?

copy_sql = """
           COPY %s FROM stdin WITH CSV HEADER
           DELIMITER as ','
           """
f = open(f"{file_path}", 'r', encoding="utf-8")
cur.copy_expert(sql=copy_sql % table_name, file=f)

Upvotes: 2

Views: 3646

Answers (1)

Ionut Ticus
Ionut Ticus

Reputation: 2789

You can use a temporary table coupled with ON CONFLICT to achieve this:

CREATE TEMPORARY TABLE buffer (
    field1,
    field2,
    ...
    fieldn
);

COPY buffer FROM STDIN With CSV HEADER DELIMITER as ',';

INSERT INTO table(field1, field2, ... fieldn)
SELECT field1, field2, ... fieldn FROM buffer
ON CONFLICT (ID) DO NOTHING;

DROP TABLE buffer;

You should be able to use the query above together with a file handle for copy_expert.

Upvotes: 5

Related Questions