Python MySQL更新的正确语法？

Question

我最近尝试将MySQL更新特性调整为Python MySQL更新特性。我正在尝试修改一个条件语句，但找不到正确的语法。例如，case“a”=“x”then 2，如果有人能看看我的MySQL代码，看看你是否能识别正确的语法，我将不胜感激。 首先，我的Python MySQL：

mycursor = db.cursor()

mycursor.execute("CREATE TABLE trial7 (Name TEXT NULL , Age INT NULL , BPsystolic INT NULL , BPdiastolic INT NULL , ClinicalFeaturesOfTheTIA TEXT NULL , DurationOfSymptoms INT NULL , HistoryOfDiabetes TEXT NULL , ABCD²ScoreForTIA FLOAT NULL )")

sql = "INSERT INTO trial7 (Name, Age, BPsystolic, BPdiastolic, ClinicalFeaturesOfTheTia, DurationOfSymptoms, HistoryOfDiabetes, ABCD²ScoreForTIA) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
val = [
  ('Person A', '71', '137', '85', 'Speech disturbance without weakness', '17', 'Yes', None),
  ('Person B', '92', '125', '78', 'Other symptoms', '43', 'Yes', None),
  ('Person C', '27', '130', '90', 'Other symptoms', '34', 'No', None)
]
mycursor.executemany(sql, val)

db.commit()

print(mycursor.rowcount, "was inserted.")

sql = "UPDATE trial7 SET ABCD²ScoreForTIA = ((Age >= 60) + (BPsystolic >= 140) + (BPdiastolic >= 90) + (case ClinicalFeaturesOfTheTia when = 'Unilateral weakness' then 2 when = 'Speech disturbance without weakness' then 1 when = 'Other symptoms' then 0 end) + (case when DurationOfSymptoms >= 60 then 2 when DurationOfSymptoms >= 10 then 1 when DurationOfSymptoms < 10 then 0 end) + (HistoryOfDiabetes = 'Yes')) / 8 where ABCD²ScoreForTIA is null)"

mycursor.execute(sql)
db.commit()
print(mycursor.rowcount, "record(s) affected")

其次是我收到的错误

3 was inserted.
Traceback (most recent call last):
  File "C:\Users\User\Desktop\python\demo.py", line 28, in <module>
    mycursor.execute(sql)
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 651, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 538, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'then 2) (case ClinicalFeaturesOfTheTiawhen = 'Speech disturbance without weaknes' at line 1

最后是我从中派生的MySQL代码

CREATE TABLE `trial`.`trial` ( `Name` TEXT NULL , `Age` INT NULL , `BP systolic` INT NULL , `BP diastolic` INT NULL ,`Clinical features of the TIA` TEXT NULL , 
`Duration of symptoms` INT NULL , `History of diabetes` TEXT NULL , `ABCD² Score for TIA` FLOAT NULL ) ENGINE = InnoDB;

INSERT INTO `trial` (`Name`, `Age`, `BP systolic`, `BP diastolic`, `Clinical features of the TIA`, `Duration of symptoms`, `History of diabetes`, 
`ABCD² Score for TIA`) VALUES ('Person A', '71', '137', '85', 'Speech disturbance without weakness', '17', 'Yes', NULL);
INSERT INTO `trial` (`Name`, `Age`, `BP systolic`, `BP diastolic`, `Clinical features of the TIA`, `Duration of symptoms`, `History of diabetes`, 
`ABCD² Score for TIA`) VALUES ('Person B', '92', '125', '78', 'Other symptoms', '43', 'Yes', NULL);
INSERT INTO `trial` (`Name`, `Age`, `BP systolic`, `BP diastolic`, `Clinical features of the TIA`, `Duration of symptoms`, `History of diabetes`, 
`ABCD² Score for TIA`) VALUES ('Person C', '27', '130', '90', 'Other symptoms', '34', 'No', NULL);

update trial 
set `ABCD² Score for TIA` = ( 
  (Age >= 60) + (`BP systolic` >= 140) + (`BP diastolic` >= 90) +
  case `Clinical features of the TIA`
    when 'Unilateral weakness' then 2 
    when 'Speech disturbance without weakness' then 1 
    when 'Other symptoms' then 0 
  end +  
  case
    when `Duration of symptoms` >= 60 then 2
    when `Duration of symptoms` >= 10 then 1
    when `Duration of symptoms` < 10 then 0
  end + 
  (`History of diabetes` = 'Yes')
) / 8 
where `ABCD² Score for TIA` is null

Answer 1

CASE表达式的语法错误。
试试这个：

UPDATE trial7 
SET ABCD²ScoreForTIA = (
  (Age >= 60) + (BPsystolic >= 140) + (BPdiastolic >= 90) + 
  (
    case ClinicalFeaturesOfTheTia
       when 'Unilateral weakness' then 2 
       when 'Speech disturbance without weakness' then 1 
       when 'Other symptoms' then 0 
     end
  ) + 
  (
    case 
      when DurationOfSymptoms >= 60 then 2 
      when DurationOfSymptoms >= 10 then 1 
      when DurationOfSymptoms < 10 then 0 
    end
  ) + 
  (HistoryOfDiabetes = 'Yes')
) / 8 
WHERE ABCD²ScoreForTIA IS NULL

请参阅demo。
结果:

> Name     | Age | BPsystolic | BPdiastolic | ClinicalFeaturesOfTheTIA            | DurationOfSymptoms | HistoryOfDiabetes | ABCD²ScoreForTIA
> :   - |  : |     -: |      : | :                  |         -: | :         |         :
> Person A |  71 |        137 |          85 | Speech disturbance without weakness |                 17 | Yes               |               0.5
> Person B |  92 |        125 |          78 | Other symptoms                      |                 43 | Yes               |             0.375
> Person C |  27 |        130 |          90 | Other symptoms                      |                 34 | No                |              0.25