python - 为什么 Sqlite3 表不会更新

Question

我已经尝试对我的 sqlite3 表进行更新，但它似乎不起作用。

marks = "My long name here"
conn = sqlite3.connect('mydb.db')
cur=conn.cursor()
cur.execute("UPDATE '" + str(marks) +"' SET (ENG,KIS,MAT,BIO) = (-1,-1,-1,-1) WHERE (ENG,KIS,MAT,BIO) =('nan','nan','nan','nan')")
conn.commit()
conn.close()

我的代码中看不到任何错误。

标签： pythonsqlite

Answer 1

You don't need 4 separate statements.
You can do it in 1 statement with CASE expressions:

UPDATE tablename 
SET ENG = CASE WHEN ENG = 'nan' THEN -1 ELSE ENG END, 
    KIS = CASE WHEN KIS = 'nan' THEN -1 ELSE KIS END,
    MAT = CASE WHEN MAT = 'nan' THEN -1 ELSE MAT END,
    BIO = CASE WHEN BIO = 'nan' THEN -1 ELSE BIO END
WHERE 'nan' IN (ENG,KIS,MAT,BIO)