Python/SQLite possible issue

I’m running sqlite query in python:

sql_query = “”"
SELECT TransType, SerialNr, RITM, TimeStamp, AssetID,
Sector, ComputerModel, ComputerManufacturer, ComputerPlatform, ComputerClass,
ComputerName, AgeInMonths, WarrantyStartDate, null, DepotID,
Depot, null, null, RequestedFor, RequestedForMyID,
StockType, PO, OrderType, OrderNr, ShipAddress1,
ShipAddress2, ShipAddress3, ShipCity, ShipState, ShipZip,
OrderDate, NeedByDate, DeliveryDate, null, SignedBy,
ShipDate, InvoiceNr, TrackingNr, Description, NewHireFlag,
null, null, null, null, FileDate
FROM DepotTransImport WHERE true
ON CONFLICT(TransType, SerialNr, RITM, TimeStamp)
DO UPDATE SET Transtype=excluded.TransType,
SerialNr=excluded.SerialNr,
RITM=excluded.RITM,
TimeStamp=excluded.TimeStamp,
AssetID=excluded.AssetID,
Sector=excluded.Sector,
ComputerModel=excluded.ComputerModel,
ComputerManufacturer=excluded.ComputerManufacturer,
ComputerPlatform=excluded.ComputerPlatform,
ComputerClass=excluded.ComputerClass,
ComputerName=excluded.ComputerName,
AgeInMonths=excluded.AgeInMonths,
WarrantyStartDate=excluded.WarrantyStartDate,
DepotID=excluded.DepotID,
Depot=excluded.Depot,
RequestedFor=excluded.RequestedFor,
RequestedForMyID=excluded.RequestedForMyID,
StockType=excluded.StockType,
PO=excluded.PO,
OrderType=excluded.OrderType,
OrderNr=excluded.OrderNr,
ShipAddress1=excluded.ShipAddress1,
ShipAddress2=excluded.ShipAddress2,
ShipAddress3=excluded.ShipAddress3,
ShipCity=excluded.ShipCity,
ShipState=excluded.ShipState,
ShipZip=excluded.ShipZip,
OrderDate=excluded.OrderDate,
NeedByDate=excluded.NeedByDate,
DeliveryDate=excluded.DeliveryDate,
SignedBy=excluded.SignedBy,
ShipDate=excluded.ShipDate,
InvoiceNr=excluded.InvoiceNr,
TrackingNr=excluded.TrackingNr,
Description=excluded.Description,
NewHireFlag=excluded.NewHireFlag,
FileDate=excluded.FileDate;“”"
cr.execute(sql_query)
cn.commit()
which runs fine in sqlite directly but receive an error:

OperationalError: near “ON”: syntax error
Traceback (most recent call last):
File “”, line 121, in <module

Wondering if the version of python used in knime (3.9) may not be able to do an upsert?

Jeff

@Jeff ChatGPT suggests to check the version of the driver since the UPSERT feature has been introduced with version 3.24 and has been expanded with 3.35.

import sqlite3
print(sqlite3.sqlite_version)

On another note: why not run SQL directly with a knime node.

3 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.