Sqlite operationalError: syntax for IF NOT EXISTS

Question

I am getting a sql lite operational syntax error for this code:

def checkIn(uname, title):
   bookid = findBookID(title) #returns an int bookid given the title
   print bookid
   with libDB:
      checkCur = libDB.cursor()
      checkCur.execute(
         "IF NOT EXISTS(SELECT 1 FROM Checks WHERE Username =? AND bookID =?) INSERT INTO Checks VALUES(?,?)",
         (uname, bookid, uname, bookid))
      checkCur.close()
      mess = "OK::CHKIN::", uname, "::", title
      return mess 

The error is:

  sqlite3.OperationalError: near "IF": syntax error

This is how I defined the table:

   with libDB:
     checkCur = libDB.cursor()
     checkCur.execute(
         "CREATE TABLE Checks(bookID INTEGER, Username TEXT, FOREIGN KEY(bookID) REFERENCES Books(bookID),FOREIGN KEY(Username) REFERENCES Users(Username))")
     checkCur.close()

My apologies if I am missing something simple. I looked over the code several times and search online and I don't see where the syntax error is. I compared my query to those I found online and it seems to match. The only thing I can think of that could be wrong is if my parameters are not correct but I tried altering them and I still can't get it to work.

Thank you in advance for any help.

-CJ

Answer 1

IF NOT EXISTS is incompatible with sqlite. The insert statement you want is as follows:

INSERT INTO Checks (bookID, Username)
  SELECT 7, 'Bob'  /* for example */
    WHERE NOT EXISTS (SELECT 1 FROM Checks WHERE bookID = 7 and Username = 'Bob');

Note that NOT EXISTS is in the WHERE clause. This sort of insert statement is compatible with sqlite. You can play with the sql fiddle here.

So in your Python function, try this instead:

insert_stmt = ("INSERT INTO Checks (bookID, Username) "  # note the space at end of string
                  "SELECT ?, ? "
                  "WHERE NOT EXISTS (SELECT 1 FROM Checks WHERE bookID = ? and Username = ?)")
checkCur.execute(insert_stmt, (bookid, uname) * 2)  # no need to repeat the bookid, uname combo twice; just multiply the tuple by 2