CODEWITHSUNDEEP
pythonmysqlpandaspandas-to-sql
Anonymous Anonymous
Anonymous Anonymous

Reputation: 121

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

I have some experience with python but very new to the SQL thing and trying to use pandas.to_sql to add table data into my database, but when I add I want it to check if the data exists before append

This are my 2 dataframes

>>> df0.to_markdown()
|    |   Col1 |   Col2 |
|---:|-------:|-------:|
|  0 |      0 |     00 |
|  1 |      1 |     11 |

>>> df1.to_markdown()
|    |   Col1 |   Col2 |
|---:|-------:|-------:|
|  0 |      0 |     00 |
|  1 |      1 |     11 |
|  2 |      2 |     22 |

So here I use the pandas to_sql

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

Here I check my data inside the database file

>>> df_out = pd.read_sql("""SELECT * FROM test_db""", con)
>>> df_out.to_markdown()
|    |   Col1 |   Col2 |
|---:|-------:|-------:|
|  0 |      0 |      0 |
|  1 |      1 |     11 |
|  2 |      0 |      0 | # Duplicate
|  3 |      1 |     11 | # Duplicate
|  4 |      2 |     22 |

But I want my database to look like this, so I don't want to add the duplicate data to my database

|    |   Col1 |   Col2 |
|---:|-------:|-------:|
|  0 |      0 |      0 |
|  1 |      1 |     11 |
|  3 |      2 |     22 |

Is there any option I can set or some line of code to add to make this happend?

Thankyou!

edit: There are some SQL code to only pull unique data, but what I want to do is don't add the data to the database in the first place

Upvotes: 12

Views: 14527

Answers (4)

XYZ
XYZ

Reputation: 382

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.

Upvotes: 0

Mike
Mike

Reputation: 489

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;
''')

Upvotes: 0

Sanchit Singh
Sanchit Singh

Reputation: 81

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

Upvotes: 8

Bart77
Bart77

Reputation: 103

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

Upvotes: -2

Related Questions