python sqlite3 IF EXIST() ELSE not working

Question

我正在检查

如果有特定条件的行存在:更新

Else:插入

使用python sqlite3库

下面是我的代码:

DbCursor.execute(
"BEGIN TRANSACTION "
"IF EXISTS (SELECT ID FROM Cart WHERE UID = ? AND Status = 'open') "
"   BEGIN"
"       UPDATE Cart SET Price = Price + ?, Products = Products || ?, Expire = 10*60"
"   END "
"ELSE"
"   BEGIN"
"       INSERT INTO Cart(UID, Products, Price, Status, Expire) VALUES(?, ?, ?, ?, 10*60)"
"   END "
"END TRANSACTION;", (uid, price, f"{product_id},", uid, f"{product_id},", price, 'open')
)
database.commit()

但是我得到这个错误:

sqlite3.OperationalError: near "EXISTS": syntax error

有什么好办法吗?

Answer 1

所以我得出结论，在sqlite中没有IF EXIST()这样的东西。

这就是为什么我决定用Python的方式来做这个操作。

代码如下:

open_cart = DbCursor.execute("SELECT ID FROM Cart WHERE UID = ? AND Status = 'open';", [uid]).fetchone()
if open_cart:
DbCursor.execute(
"UPDATE Cart SET Price = Price + ?, Products = Products || ?, Expire = 10*60 WHERE UID = ?;",
[price, f"{product_id},", uid]
)
else:
DbCursor.execute(
"INSERT INTO Cart(UID, Products, Price, Status, Expire) VALUES(?, ?, ?, 'open', 10*60)",
[uid, f"{product_id},", price]
)
database.commit()