Clever Geek Handbook

Insert ignore pandas dataframe in mysql

I want to "insert ignore" the whole pandas framework into mysql. Is there a way to do this without looping through the lines?

In dataframe.to_sql I only see the if_exists 'append' option, but will it continue with duplicate unique keys?

+3


source to share


2 answers


Consider using a temporary table (with the exact structure of the final table), which is always replaced with pandas, then run INSERT IGNORE

in a cursor call:



dataframe.to_sql('myTempTable', con, if_exists ='replace')

cur = con.cursor()
cur.execute("INSERT IGNORE INTO myFinalTable SELECT * FROM myTempTable")
con.commit()
+1


source


There is no way to do this in pandas prior to the current version pandas (0.20.3)

.

The option if_exists

applies only to the table (not by row) as stated in the documentation .

if_exists : {‘fail’, ‘replace’, ‘append’}, default ‘fail’

fail

: If the table exists , do nothing.

replace

: If the table exists , drop it, re-create and insert the data.



append

: If the table exists , insert the data. Create if doesn't exist.

Through Looping

This will slow down the process when you insert one line at a time

for x in xrange(data_frame.shape[0]):
    try:
        data_frame.iloc[x:x+1].to_sql(con=sql_engine, name="table_name", if_exists='append')
    except IntegrityError:
        # Your code to handle duplicates
        pass
0


source






More articles:


All Articles