pythonsqlsqliteupsertexecutemany

Update rows if data exists with executemany

I have the following SQL that aims to create a new record in the DB or update an existing one.

sql = """INSERT INTO table (v1, v2, v3, v4, v5, v6)
         VALUES (?, ?, ?, ?, ?, ?)
         ON CONFLICT(v5) DO UPDATE
         SET v1 = ?, v3 = ?, v6 = ?
"""

self.curr.execute(sql,(
    v1, v2, v3, v4, v5, v6,
   # below are the values for eventual update 
    v1, v3, v6)
)

This works great, however, I am trying to implement batch creation of rows with executemany(), and also keep ON CONFLICT logic.

I know that it would look something similar to this:

data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
sql = """
INSERT INTO employees (first_name, hire_date) 
VALUES (%s, %s)
ON CONFLICT(first_name) DO UPDATE
SET date = %S
"""
cursor.executemany(sql, data)

But I am not quite sure how to pass the columns value (hire_date) that is used for the update if a row with first_name already exists

Do I pass another 3 tuples in the data array with the values for an eventual update?

I couldn't find any specific resource on the web for my need, so any help would be appreciated.

Solution

  • You can add the EXCLUDED table qualifier to the column name:

    data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
sql = """
INSERT INTO employees (first_name, hire_date) 
VALUES (?, ?)
ON CONFLICT(first_name) DO UPDATE
SET hire_date = EXCLUDED.hire_date
"""
cursor.executemany(sql, data)