CODEWITHSUNDEEP
pythonmysql
Lamtheram
Lamtheram

Reputation: 89

Correct syntax for Python MySQL update?

I've recently tried to adapt a MySQL update feature to a Python MySQL one. I'm trying to adapt a conditional statement but can't find the right syntax as to for e.g. case "A" = "x" then 2, it would be much appreciated if someone could take a look at my MySQL code and see if you can identify the right syntax. Firstly my 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")

Secondly the error I'm receiving

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

Lastly the MySQL code I derived it from

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

Upvotes: 1

Views: 167

Answers (1)

forpas
forpas

Reputation: 164214

The syntax for your CASE expressions is wrong.
Try this:

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

See the demo.
Results:

> 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

Upvotes: 2

Related Questions