How to use pandas.to_sql but only add row if row doesn't exist yet

Don't use to_sql a simple query can work

query = text(f""" INSERT INTO test_db VALUES {','.join([str(i) for i in list(df0.to_records(index=False))])} ON CONFLICT ON CONSTRAINT test_db_pkey DO NOTHING""")

self.engine.connect().execute(query)

For each DataFrame change df0 to df1

Follow these link for a better understanding

• Insert values if records don't already exist in Postgres

• How to upsert pandas DataFrame to PostgreSQL table?

For example in my Sqlite 3 database I used temporary table:

I inserted all the dataframe data into temporary table:

df0.to_sql(con=con, name='temptable', if_exists='append', index=False)
df1.to_sql(con=con, name='temptable', if_exists='append', index=False)

Then I copy only new data and drop (delete) the table:

con.executescript('''
INSERT INTO test_db
SELECT test_db.* FROM temptable 
LEFT JOIN test_db on 
   test_db.Col1 = temptable.Col1
WHERE test_db.Col1 IS NULL; -- only items, that not presented in 'test_db' table

DROP TABLE temptable;
''')

There are two ways:

1. if the data from database is not big, read the data from database into the dataframe, and combine the two columns (Col1 and Col2) to create a new column, i.e. combined_column, and save this into a list combined_column_list. Filter out those rows from df0 and df2, whose corresponding combined_column does not appear in the combined_column_list, and inserted the filtered rows directly to the database table.

2. Insert df1 and df2 to a temporary table, e.g. with name "temp". Using python pymysql to run the following code:

   conn = pymysql.connect(host=DB_ip, user=DB_user,passwd=DB_password,db=DB_name)    
   cur = conn.cursor()
   
   temp_query =" insert into  test_db (select * from temp where ( `Col1`, `Col2`) not in (select `Col1`, `Col2` from test_db ));"
   cur.execute(temp_query)
   conn.commit()  
   

This will only insert the new data to the database table.

add this code to your function

remove_duplicate = 'EXEC remove_duplicate'
cursor.execute(remove_duplicate)
cursor.commit()

and create procedure in your db:

    CREATE PROCEDURE remove_duplicate AS
BEGIN
;WITH duplicates as (SELECT col1, col2,
                    ROW_NUMBER() OVER (PARTITION BY col1, col2, ORDER BY col1) AS number_of_duplicates
    FROM dbo.table)
    DELETE FROM duplicates WHERE number_of_duplicates > 1

END
go