pandas dataframe to sql - append data to exisitng table without duplication

Question

I have a MySQL Table feinstaub with the Columns (created_at,PM 2.5,PM 10,entry_id) c4 is unique. And i have a pandas dataFrame with equal column names. In this dataframe are new values and already existing values compare to the sql table. I use this line to send the dataframe to sql server.

df.to_sql("Feinstaub", con=engine, if_exists="append", index=False)

It worked only when in the dataframe are no duplicated vlaues. If there are some dupl. values it doesent work. I found this solution: Pandas to_sql() to update unique values in DB?

with engine.begin() as cn:
   sql = """INSERT INTO myFinalTable (Col1, Col2, Col3, ...)
            SELECT t.Col1, t.Col2, t.Col3, ...
            FROM myTempTable t
            WHERE NOT EXISTS 
                (SELECT 1 FROM myFinalTable f
                 WHERE t.MatchColumn1 = f.MatchColumn1
                 AND t.MatchColumn2 = f.MatchColumn2)"""

   cn.execute(sql)

I end up with this one:

df.to_sql("temp_feinstaub_wohnzimmer", con=engine, if_exists="replace", index=False)
with engine.begin() as cn:
   sql = """INSERT INTO feinstaub (created_at, 'PM 2.5' , 'PM 10', entry_id)
            SELECT t.Column1, t.Column2, t.Column3 ,t.Column4
            FROM temp_feinstaub_wohnzimmer t
            WHERE NOT EXISTS
                (SELECT 1 FROM feinstaub f
                 WHERE t.MatchColumn1 = f.MatchColumn1
                 AND t.MatchColumn2 = f.MatchColumn2
                 AND t.MatchColumn3 = f.MatchColumn3
                 AND t.MatchColumn4 = f.MatchColumn4)"""

   cn.execute(sql)

It raised an sql syntax error. I try to rename f.MatchColumn also but still give me an sql syntax error?

EDIT: I use this code now and it worked with the backticks thanks! But it raised another error ;)

#Send the Data to SQL database
df.to_sql("temp_feinstaub_wohnzimmer", con=engine, if_exists="replace", index=False)
with engine.begin() as cn:
   sql = """INSERT INTO feinstaub (created_at, `PM 2.5` , `PM 10`, entry_id)
            SELECT t.created_at, t.`PM 2.5`, t.`PM 10` ,t.entry_id
            FROM temp_feinstaub_wohnzimmer t
            WHERE NOT EXISTS
                (SELECT 1 FROM feinstaub f
                 WHERE t.created_at = f.created_at
                 AND t.`PM 2.5` = f.`PM 2.5`
                 AND t.`PM 10` = f.`PM 10`
                 AND t.entry_id = f.entry_id)"""

   cn.execute(sql)

and now i get the folloing error:

sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry '3825' for key 'entry_id'") [SQL: 'INSERT INTO feinstaub_wohnzimmer (created_at, `PM 2.5` , `PM 10`, entry_id)\n            SELECT t.created_at, t.`PM 2.5`, t.`PM 10` ,t.entry_id\n            FROM temp_feinstaub_wohnzimmer t\n            WHERE NOT EXISTS\n                (SELECT 1 FROM feinstaub_wohnzimmer f\n                 WHERE t.created_at = f.created_at\n                 AND t.`PM 2.5` = f.`PM 2.5`\n                 AND t.`PM 10` = f.`PM 10`\n                 AND t.entry_id = f.entry_id)']

And what is the syntax error? Column names should not be single quoted. Use backticks to escape spaces and special characters. Also, obviously, you have to rename MatchColumn to actual column names which are placeholders here. Add or remove if more or less than 4 columns. — Parfait
– Parfait, Commented Dec 6, 2018 at 22:00

till Kadabra · Accepted Answer · 2018-12-07 11:26:36Z

3

With this it works for me... I can excecute the script several times and only the new values find their way into the mysql database.

from sqlalchemy import exc
num_rows = len(df)
#Iterate one row at a time
for i in range(num_rows):
    try:
        #Try inserting the row
        df.iloc[i:i+1].to_sql(name="feinstaub_wohnzimmer",con = engine,if_exists = 'append',index=False)
    except exc.IntegrityError:
        #Ignore duplicates
        pass

answered Dec 7, 2018 at 11:26

till Kadabra

48811 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pandas dataframe to sql - append data to exisitng table without duplication

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related