ON DUPLICATE KEY UPDATE mysql 添加或更新

目的：当表中unique索引或者primary key(主键)出现重复时，执行update操作，当不出现重复时，执行insert操作

例子：

INSERT INTO tp_circle_recommend 
(userid, circleids, nohad, bothhad, had)
VALUES( {a}, '{b}', '{c}', '{d}', '{e}') 
ON DUPLICATE KEY UPDATE 
circleids = VALUES(circleids),
nohad = VALUES(nohad),
bothhad = VALUES(bothhad),
had = VALUES(had); 

sql语句， 使用sqlalchemy orm excute 执行sql 语句 插入数据

def insert_update_df(df: pd.DataFrame, conn):
    sql_template = '''
        INSERT INTO tp_circle_recommend 
        (userid, circleids)
        VALUES( {a}, '{b}') 
        ON DUPLICATE KEY UPDATE 
        circleids = VALUES(circleids)
    '''
    for i in df.iterrows():
        sub = i[1]
        this_sql = sql_template.format(a=sub["userid"], b=sub["circleids"])
        conn.execute(this_sql)

或者先清空表，再删除表