Add lot of records to SQL Database if they are not already in with SQLAlchemy

Question

I have an SQL DB (Postgree) with ~ 2000000 records in a table. And I have a CSV file with ~ 500000 rows. I need to check all records in CSV and add them to DB if they were not present before . I should check them by name field.

My question is what is the fastest way to do that?

Thanks

Answer 1

1. Load your new rows into a staging table.
2. Index your staging and target tables on the name column

3. write a query like this:

   INSERT INTO target (column list) SELECT column list FROM source LEFT JOIN target ON target.name = source.name WHERE target.name IS NULL

or

INSERT INTO target (<column list>)
SELECT <column list> FROM source
WHERE NOT EXISTS (
    SELECT * from target 
    WHERE target.name = source.name
)