django - PSQL 中的条件更新语句

Question

我正尝试在我的网站上进行有条件的更新。每当用户单击某个链接时，数据库中的全局计数器变量会自行递增。

复杂的是，当计数器数量超过我记录的网站数量时，我希望计数器回到 1。我有一个更新声明，但它不起作用，我不明白为什么。

#Increment global counter, set it back to one when it exceeds the number of websites on record
cursor.execute("UPDATE url_reroute_counter SET counter_num = "+
                    " CASE"+
                        " WHEN counter_num>(SELECT count(*) FROM url_reroute_targeturl)"+ 
                            " THEN counter_num = 1"+
                        " ELSE"+
                            " counter_num = counter_num+1"+
                    " END"+
                    " WHERE name_of_counter = 'url_count'")`

运行此代码会出现以下异常:

django.db.utils.ProgrammingError: column "counter_num" is of type integer but expression is of type boolean
    LINE 1: UPDATE url_reroute_counter SET counter_num =  CASE WHEN coun...
                                                          ^
    HINT:  You will need to rewrite or cast the expression.

我不习惯在任何 SQL 语言中使用条件，因此不胜感激。

Answer 1

您的查询应该如下所示

cursor.execute("UPDATE url_reroute_counter SET counter_num = "+
                " CASE"+
                    " WHEN counter_num>(SELECT count(*) FROM url_reroute_targeturl)"+ 
                        " THEN 1"+
                    " ELSE"+
                        " counter_num+1"+
                " END"+
                " WHERE name_of_counter = 'url_count'")`

顺便说一句，在多行 python 字符串中，您不需要所有这些 + 符号。这个:https://stackoverflow.com/a/10660443/267540