Update rows if data exists with executemany

Question

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.

I ran 2nd code block and result is: 'cursor' is not defined. — Mário César Fracalossi Bais, May 29 '22 at 00:04
you need to initialize a connection to a DB and then a cursor: `conn = sqlite3.connect('name_of_database.db')` and then `curr = conn.cursor()` — antpngl92, May 29 '22 at 00:06

score 1 · Answer 1 · answered May 29 '22 at 03:30

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()

score 1 · Accepted Answer · answered May 29 '22 at 05:30

1

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)

answered May 29 '22 at 05:30

forpas

160,666
10
38
76

This is what I was looking for. I tried to find some resources regarding the `EXCLUDED` table qualifier, but I couldn't find any. Do you mind elaborating a bit on what it does under the hood? – antpngl92 May 29 '22 at 09:32
1

@antpngl92 the documentation for SQLite's UPSERT is this: http://www.sqlite.org/draft/lang_upsert.html where it is mentioned: "...To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name" – forpas May 29 '22 at 09:36
Appreciate it, thanks. – antpngl92 May 29 '22 at 09:37

Update rows if data exists with executemany

2 Answers2