SQLite3 upsert always fails when called from python

Question

I’ve a sqlite3 db: phone book (name_id, phone_nb). I want to insert (“Kyl”, +33661) if Kyl-entry doesn’t exist yet, or, if Kyl already exists, I want to update his phone number to +33770. This is called upsert.

SQLite upsert is:

INSERT INTO table(...) 
VALUES (...)
ON CONFLICT (...) DO UPDATE SET expression

My issue:

1. The above statement works perfectly when I use sqlite3, but it doesn’t work at all when I call the same from python.
2. On the other hand, if from python I use pure INSERT INTO table VALUES it works (without ON CONFLICT)
3. In addition, if from python I use classical UPDATE table SET col WHERE condition, it works too
4. Using SQLite upsert, I always have the same error: near "ON": syntax error

This is my table:

CREATE TABLE phone_book (
    author_id  INTEGER PRIMARY KEY
                       UNIQUE,
    short_name TEXT    NOT NULL,
    join_date  DATE    NOT NULL,
    email      TEXT    NOT NULL,
    phone_nb   STRING
);

From SQL Studio, I run

INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb) 
VALUES (13, "kyl", "2020-12-20", "kyl@domain.net", 33670668832)
ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;

This insert works. Then as Kyl changed his phone nb, I update his phone nb, using the same:

INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb) 
VALUES (13, "kyl", "2020-12-20", "kyl@domain.net", 33677755231)
ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;

This update work too. Everything’s in place! It’s time now to run all that from python. The bad news is that, when called from python, this precise statement doesn’t work at all.

What I’ve tried all the combinations:

1. cursor.execute(...)
2. cursor.executemany(...)
3. With explicit parameters
4. With ‘?’ placeholder

I always have the same error: near "ON": syntax error. My non-working code with ‘?’ placeholder:

try:
    sqliteConnection = sqlite3.connect('my.db')
    cursor = sqliteConnection.cursor()
    #print("Connected to SQLite")
    
    author_id = 13
    short_name = "mike" 
    join_date = "2021-01-12"
    email = "mike@domain.net"
    phone_nb = "00336"
    
    tupple = []
    tupple.append((author_id, short_name, join_date, email, phone_nb))
    
    statement_ON_CONF = """INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
                    VALUES(?,?,?,?,?)
                    ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;"""
    
    print("statement_ON_CONF: " + statement_ON_CONF) # check my statement
    cursor.executemany(statement_ON_CONF, tupple)
    sqliteConnection.commit()
    
except sqlite3.Error as error:
    print("Failed to insert or update into sqlite table: ", error)
finally:
    if (sqliteConnection):
        sqliteConnection.close()
        #print("The SQLite connection is closed")  

On the other hand, using pure INSERT and then UPDATE all's ok: my working code:

try:
    sqliteConnection = sqlite3.connect('my.db')
    cursor = sqliteConnection.cursor()
    
    author_id = 2
    short_name = "mike" 
    join_date = "2021-01-12"
    email = "mike@domain.net"
    phone_nb = "00336"
                 
    # Insert a new entry: Mike             
    statement = """INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
                    VALUES(?,?,?,?,?)"""
    print("statement: " + statement)
    cursor.execute(statement, (author_id, short_name, join_date, email, phone_nb))
    sqliteConnection.commit()
    
    # Update Mike phone nb
    phone_nb = "+3310"
    statement_ON_CONF = """INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
                    VALUES(?,?,?,?,?)
                    ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;"""
    
    statement_UPDATE = "UPDATE phone_book SET phone_nb=? WHERE author_id=?;"
    cursor.execute(statement_UPDATE, (phone_nb, author_id))
    sqliteConnection.commit()
    
except sqlite3.Error as error:
    print("Failed to insert or update into sqlite table: ", error)
finally:
    if (sqliteConnection):
        sqliteConnection.close()

I use SQLite version 3.34.0 2020-12-01, and python version 3.7.2rc1, on Windows 7 Pro

Does anyone know why upsert always throws an error when called from python? Thanks!

Answer 1

According to a comment:

"SQLite supports UPSERT since version 3.24.0" – forpas.

Answer 2

You try to pass this tupple:

[(author_id, short_name, join_date, email, phone_nb)]

as VALUES in your statement, this results to error as it's not readable from sqlite

Try this instead:

statement_ON_CONF = """INSERT INTO phone_book(author_id, short_name, join_date, email, phone_nb)
                    VALUES ?
                    ON CONFLICT(author_id) DO UPDATE SET phone_nb=excluded.phone_nb;"""
    
    print("statement_ON_CONF: " + statement_ON_CONF) # check my statement
    cursor.executemany(statement_ON_CONF, tupple[0])