Inserting/Updating sqlite table from python program

Question

I have a sqlite3 table as shown below

Record(WordID INTEGER PRIMARY KEY, Word TEXT, Wordcount INTEGER, Docfrequency REAL). I want to create and insert data into this table if the table not exists else I want to update the table in such a way that only 'Wordcount' column get updated on the basis(Reference) of data in the column 'Word'. I am trying to execute this from a python program like

import sqlite3
conn = sqlite3.connect("mydatabase")

c = conn.cursor()
#Create table
c.execute("CREATE TABLE IF NOT EXISTS  Record(WordID INTEGER PRIMARY KEY, Words TEXT, Wordcount INTEGER, Docfrequency REAL)")

#Update table
c.execute("UPDATE TABLE IF EXISTS Record")

#Insert a row of data
c.execute("INSERT INTO Record values (1,'wait', 9, 10.0)")
c.execute("INSERT INTO Record values (2,'Hai', 5, 6.0)")

#Updating data
c.execute("UPDATE Record SET Wordcount='%d' WHERE Words='%s'" %(11,'wait') )

But I can't update the table. On running the program I am getting the error message as

c.execute("UPDATE TABLE IF EXISTS Record") sqlite3.OperationalError: near "TABLE": syntax error

How should I write the code to update the table ?

Answer 1

Your SQL query for UPDATE is invalid - see the documentation.

Also, I don't understand why you'd want to check for the table's existence when updating, given that just before that you're creating it if it doesn't exist.

If your goal is to update an entry if it exists or insert it if it doesn't, you might do it either by:

• First doing an UPDATE and checking the number of rows updated. If 0, you know the record didn't exist and you should INSERT instead.
• First doing an INSERT - if there's an error related to constraint violation, you know the entry already existed and you should do an UPDATE instead.