【詳しく解説】MySQLにおけるUpsertの書き方（サンプル付き）

-- table_A に1行 Upsert する場合(valuesを使う例）
insert into table_A 
values(1,'2023/01/01 09:00:00',10000,5)
on duplicate key update count=values(count)

-- table_A に1行 Upsert する場合（エイリアスを使う例）
insert into table_A 
values(1,'2023/01/01 09:00:00',10000,5) as t
on duplicate key update count=t.count

-- table_B からtable_A に Upsert する場合
-- ※テーブルのエイリアスは利用できない 
insert into table_A 
select * from table_B 
on duplicate key update table_A.count=table_B.count

--サンプルテーブルの作成とテストデータ登録
create table oders
(
    shop_no  integer not null,
    order_date timestamp not null,
    product_id integer not null,
    count integer not null,
    constraint oders_p_key primary key (shop_no,order_date,product_id)
);
alter table oders add constraint oders_uniquekeys unique(shop_no,order_date,product_id)
insert into oders values(1,'2023/01/01 09:00:00',10000,5);
insert into oders values(1,'2023/01/01 09:10:00',10000,15);
insert into oders values(1,'2023/01/01 09:20:00',10000,25);

--下記はテーブル間のUpsert確認用(odersを丸ごとコピー）
create table old_oders as select * from oders

insert into oders values(1,'2023/01/01 09:00:00',10000,15)
on duplicate key update count=values(count)

insert into oders values
(1,'2023/01/01 09:01:00',10000,15),
(1,'2023/01/01 09:02:00',10100,12),
(1,'2023/01/01 09:03:00',10200,18)
on duplicate key update count=values(count)

insert into oders select * from old_oders 
on duplicate key update oders.count=old_oders.count

-- oders に向けてインサートを行うが、既に存在する場合は何もしない
insert ignore into oders values(1,'2023/01/01 09:00:00',10000,15)

-- old_odersから oders に向けてインサートを行うが、既に存在するレコードには何もしない
insert ignore into oders select * from old_oders

datas = [
   [1,'2023/01/01 09:00:00',10000,55],
   [1,'2023/01/01 09:10:00',10000,65],
   [1,'2023/01/01 09:20:00',10000,75]
]

df = pd.DataFrame(datas,columns=['shop_no','order_date','product_id','count'])
sqls = create_upsert(df,'oders',['shop_no','order_date','product_id'])
for sql in sqls:
    print(sql)

import pandas as pd
def create_upsert(df,table,key_columns):
    '''
    DataFrameの内容からUpser文を作成する

    Parameters
    ----------
    df : DataFrame
        データを格納したDataFrame
    table : str
        Upsert対象のテーブル名
    key_columns : list of str
        ユニークキーのカラム
        例:['shop_no','order_date',・・・]"
        
    Returns
    ----------
    res:str
        生成されたUpsertインサート文  
    '''
    # df から全てのカラム名を取り出す 
    columns = ",".join([i for i in df.columns])

    # シングルクォート格納用のリストを作り、カラムの型がobjectの場合のみシングルクォートをセット
    quote = ["'" if str(dtype) == 'object' else '' for dtype in df.dtypes]

    sqls = []
    # 全ての行を取り出す
    for row in df.itertuples():

        # 0カラム名（行インデックス）を飛ばし、必要に応じて各カラム値をシングルクォートで括る。
        items = [quote[i] + str(row[i+1]) + quote[i] for i in range(len(df.columns))]

        # Insert句の作成       
        values = ','.join(items).replace("'nan'", "NULL")
        insert = f'insert into {table}({columns}) values({values}) '

        # conflict句の作成
        keys = ','.join(key_columns)
        conflict = f'on conflict ({keys}) '

        # update句の作成
        set_list = [f'{x}=excluded.{x}' for x in df.columns if x not in key_columns]
        update = f"do update set " + ','.join(set_list)

        # insert、conflict、updateを結合
        sqls.append(insert + conflict + update)

    return sqls