python - SQLite操作错误: syntax for IF NOT EXISTS

Question

我收到此代码的 sql lite 操作语法错误:

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 

错误是:

  sqlite3.OperationalError: near "IF": syntax error

我是这样定义表格的:

   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()

如果我遗漏了一些简单的东西，我深表歉意。代码看了好几遍，上网查也没有看到哪里有语法错误。我将我的查询与我在网上找到的查询进行了比较，结果似乎相符。我唯一能想到的可能是错误的是如果我的参数不正确但我尝试更改它们但我仍然无法让它工作。

提前感谢您的帮助。

-CJ

Answer 1

IF NOT EXISTS 与 sqlite 不兼容。你想要的插入语句如下:

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

请注意 NOT EXISTS 在 WHERE 子句中。这种插入语句是compatible with sqlite .你可以玩sql fiddle here .

所以在你的 Python 函数中，试试这个:

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