Correct syntax for Python MySQL update?

Question

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

I have no idea what you meant by mysqli, but I assume you meant MySQL. Please clarify — Dharman
– Dharman ♦, Commented Nov 20, 2020 at 19:20
@Dharman yes, in relation to the syntax changing when written in a py file, ive got no idea how to resolve the problem — Lamtheram
– Lamtheram, Commented Nov 20, 2020 at 19:28

forpas · Accepted Answer · 2020-11-20 19:34:06Z

2

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

answered Nov 20, 2020 at 19:34

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Martin Burch Over a year ago

Yes, this is a good answer. The only thing I would add here: it would be nice to show the query wrapped in a multi-line python string, with triple quotes. askpython.com/python/string/…

Lamtheram Over a year ago

sql = """UPDATE trial14 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"""- ^ SyntaxError: invalid syntax

forpas Over a year ago

@Lamtheram what is the syntax error? Post the rest of the error message. As you can see in the demo, the query I posted is syntactically correct.

forpas Over a year ago

@Lamtheram You did not copy and paste my query as it is. There is no ClinicalFeaturesOfTheTiawhen in my code.

Lamtheram Over a year ago

@forpas my apologies, that was taken from the wrong attempt, I could swear I tried your code and it failed but I was wrong, it went through, thank you.

|

Collectives™ on Stack Overflow

Correct syntax for Python MySQL update?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related