insert + upsert works in sqlite3 console, but on python it shows syntax error

Question

I'm using sqlite version 3.25.1. When I execute the following query in sqlite console, it works:

insert into cases (bi, age, shape, margin, density, severity) 
values (5, 67, 3, 5, 3, 1)
on conflict(bi, age, shape, margin, density, severity) 
DO UPDATE SET frequency=frequency+1;

But when I execute it on python, it says:

sqlite3.OperationalError: near "on": syntax error

Here is my code on python:

C.execute('insert into cases (bi, age, shape, margin, density, severity) '
          'values (5, 67, 3, 5, 3, 1)'
          'on conflict(bi, age, shape, margin, density, severity) '
          'DO UPDATE SET frequency=frequency+1', ())

I don't know how to make it work.

Try a space inside the quotes at the end of your second line. — sjw, Dec 03 '18 at 13:16
@thesilkworm, I tried it and it still show that syntax error — Bentaiba Miled Basma, Dec 03 '18 at 13:21

score 3 · Accepted Answer · answered Dec 03 '18 at 13:23

3

It's possible UPSERT is not included in the sqlite3 version that python loads. From the sqlite3 doc:

UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04).

To find the sqlite version in python:

>>> import sqlite3
>>> sqlite3.sqlite_version

answered Dec 03 '18 at 13:23

DinoCoderSaurus

6,110
2
10
15

1

Yes you're right. the version is 3.20.1. How can I upgrade it? – Bentaiba Miled Basma Dec 03 '18 at 13:25
1

That, I'm afraid, is out of my ken. [This old post](https://stackoverflow.com/questions/1545479/force-python-to-forego-native-sqlite3-and-use-the-installed-latest-sqlite3-ver) may be a good starting point. – DinoCoderSaurus Dec 03 '18 at 13:37

insert + upsert works in sqlite3 console, but on python it shows syntax error

1 Answers1