pandasでデータフレームをto_sqlする
pandasでDataframeをto_sqlする時、
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) duplicate key value violates unique constraint "experiment_pkey"
が出て困りました。
エラーが出た経緯としては
- 下記のtableをread_sqlでDataframeにする
sql = 'select id, word FROM experiment' df = pd.read_sql(sql=sql, con=connection, index_col='id')
- Dataframeに行を複数追加し、to_sqlで再びtableに書き込む
for idx, w in enumerate(['this', 'is', 'a test']): df.loc[idx+2] = [w] df.to_sql('experiment', engine, if_exists='append')
行を追加後のdfは
word id 1 test 2 this 3 is 4 a test
となるのですが、
このdfをto_sqlする時にエラーがでます。idにユニーク制約があるのに、tableに既に存在する1番目の行をinsertしようとしているのが原因のようです。
pandasのドキュメント見てもduplicateが存在する場合、skipするようなオプションはありませんでした。
pandas.DataFrame.to_sql — pandas 0.22.0 documentation
この場合、if_exists='replace'
にして既に存在するtableを一度dropして作り直すのが方法みたいです。
他にいい方法はないか探していると、stackoverflowでこんなことをしている人が、、、これは遅くなるはず。
There is unfortunately no option to specify "INSERT IGNORE". This is how I got around that limitation to insert rows into that database that were not duplicates (dataframe name is df)
for i in range(len(df)): try: df.iloc[i:i+1].to_sql(name="Table_Name",if_exists='append',con = Engine) except IntegrityError: pass #or any other action
にしても、データをdbから読み込んでpandasでゴネゴネしてその結果をdbに戻すという作業はデータ分析の現場レベルではやりそうなことだと思うんですが、if_existsオプションを使って毎回tableをdropしてまた作るやり方効率悪くないですかね...