Example of using the 'callable' method in pandas.to_sql()?

Question

I'm trying to make a specific insert statement that has an ON CONFLICT argument (I'm uploading to a Postgres database); will the df.to_sql(method='callable') allow that? Or is it intended for another purpose? I've read through the documentation, but I wasn't able to grasp the concept. I looked around on this website and others for similar questions, but I haven't found one yet. If possible I would love to see an example of how to use the 'callable' method in practice. Any other ideas on how to effectively load large numbers of rows from pandas using ON CONFLICT logic would be much appreciated as well. Thanks in advance for the help!

Answer 1

Here's an example on how to use postgres's ON CONFLICT DO NOTHING with to_sql

# import postgres specific insert
from sqlalchemy.dialects.postgresql import insert

def to_sql_on_conflict_do_nothing(pd_table, conn, keys, data_iter):
    # This is very similar to the default to_sql function in pandas
    # Only the conn.execute line is changed
    data = [dict(zip(keys, row)) for row in data_iter]
    conn.execute(insert(pd_table.table).on_conflict_do_nothing(), data)

conn = engine.connect()
df.to_sql("some_table", conn, if_exists="append", index=False, method=to_sql_on_conflict_do_nothing)