Update rows if data exists with executemany-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

Because you have three placeholders in SQL you need to bind three parameters. Therefore, repeat the date value. By the way, sqlite3 uses the qmark, ?, for placeholders as used in your first example and be sure to commit.

data = [
    ('Jane', date(2005, 2, 12), date(2005, 2, 12)), 
    ('Joe', date(2006, 5, 23), date(2006, 5, 23)), 
    ('John', date(2010, 10, 3), date(2010, 10, 3)), 
] 

sql = """INSERT INTO employees (first_name, hire_date) 
         VALUES (?, ?) 
         ON CONFLICT(first_name) DO 
         UPDATE SET hire_date = ? 
      """

cursor.executemany(sql, data)
conn.commit()