こちらの SQL を python で実装しました。
PostgreSQL: Upsert
プログラム
postgre_upsert.py
#! /usr/bin/python
#
# postgre_upsert.py
#
# Jul/17/2024
#
# --------------------------------------------------------
import sys
import os
import psycopg2
from dotenv import load_dotenv
#
#
# --------------------------------------------------------
def sql_upsert_string_gen_proc ():
insert_sql = '''
INSERT INTO cities (id, name, population, date_mod)
VALUES (%s, %s, %s, %s)
ON CONFLICT (id) DO UPDATE SET
(name, population, date_mod) = (EXCLUDED.name, EXCLUDED.population, EXCLUDED.date_mod);
'''
return insert_sql
# --------------------------------------------------------
sys.stderr.write ("*** 開始 ***\n")
#
dotenv_path = '.env'
load_dotenv(dotenv_path)
USER=os.environ.get("user")
PASSWORD=os.environ.get("password")
DATA_BASE=os.environ.get("data_base")
#
conn = psycopg2.connect("dbname=" + DATA_BASE + " user=" + USER + " password=" + PASSWORD)
#
cur = conn.cursor()
#
sql_str=sql_upsert_string_gen_proc()
print(sql_str)
cur.execute(sql_str,('t3501','山口',113549,'2024-7-17'))
cur.execute(sql_str,('t3502','下関',113762,'2024-7-17'))
cur.execute(sql_str,('t3503','宇部',111725,'2024-7-17'))
conn.commit ()
#
cur.close ()
conn.close ()
#
sys.stderr.write ("*** 終了 ***\n")
#
# --------------------------------------------------------
.env
user='scott'
password='******'
data_base='city'