MySQL query works in mysql workbench but not working in python script

Question

I am trying to run below query in mysql workbench, it gives correct output.

SELECT Employee.Lname, Employee.Fname, Department.Dname, 
 (SELECT CONCAT('[', GROUP_CONCAT(CONCAT(
 '{"PNAME":"', Project.Pname, '"',
 '"PNUMBER":"', Project.Pnumber, '"',
 ',"HOURS":"', Works_on.Hours, '"}')),']') as 'json'
 FROM Works_on 
 INNER JOIN Project ON Project.Pnumber = Works_on.Pno WHERE Employee.Ssn = Works_on.Essn) AS Projects
FROM Employee
JOIN Department ON Department.Dnumber = Employee.Dno
WHERE Department.Dnumber = Employee.Dno;

This is the output.

enter image description here

But when i put it as a string, the dynamic parameters in the subquery as passed as a static(string) which leads to the wrong output.

query = "SELECT Employee.Lname, Employee.Fname, Department.Dname, (SELECT CONCAT('[', GROUP_CONCAT(CONCAT('{\"PNAME\":"', Project.Pname, '"','\"PNUMBER\":"', Project.Pnumber, '"',',\"HOURS\":"', Works_on.Hours, '"}')),']') as 'json' FROM Works_on INNER JOIN Project ON Project.Pnumber = Works_on.Pno WHERE Employee.Ssn = Works_on.Essn) AS Projects FROM Employee JOIN Department ON Department.Dnumber = Employee.Dno WHERE Department.Dnumber = Employee.Dno;"

I printed the query in the console, it's like this -

SELECT Employee.Lname, Employee.Fname, Department.Dname, (SELECT CONCAT('[', GROUP_CONCAT(CONCAT('{"PNAME":, Project.Pname, ','"PNUMBER":, Project.Pnumber, ',',"HOURS":, Works_on.Hours, }')),']') as 'json' FROM Works_on INNER JOIN Project ON Project.Pnumber = Works_on.Pno WHERE Employee.Ssn = Works_on.Essn) AS Projects FROM Employee JOIN Department ON Department.Dnumber = Employee.Dno WHERE Department.Dnumber = Employee.Dno;

this is output on executing the above query in python.

enter image description here

Edit - @Barmar solution helped me to get the correct records but while converting it to xml, dictionaries are not converting into xml tags.

current output is -

enter image description here

Edit2 - @Barmar solution gives output like this -

enter image description here

Here I want to store like below (without quotes)-

enter image description here

enter image description here

I am fetching this document & storing it as .json file then converting that json file to xml I am looking to get the nested xml tags for projects tag.

Could anyone help me to resolve this ?

Thanks, Jay

• Barmar: Use JSON_ARRAY_AGG and JSON_OBJECT instead of constructing JSON by hand.
• Akina: As I see some " are not quoted...
• Barmar: Specifically, it looks like you forgot to quote all the " after :
• Jay Prakash Thakur: @Barmar I am new to mysql, could you please help me to correct the query using json_object or json_array_agg.

Answer 1

You're missing the backslash before some of the " in the Python version. It would be easier if you used triple quotes around the whole SQL string, then you wouldn't need to escape quotes inside it.

But even easier would be to use the built-in JSON functions to create the JSON array of objects, rather than concatenating strings.

SELECT Employee.Lname, Employee.Fname, Department.Dname, 
 (SELECT JSON_ARRAYAGG(JSON_OBJECT('PNAME', Project.Pname, 'PNUMBER', Project.Pnumber, 'HOURS', Works_On.Hours)) as 'json'
 FROM Works_on 
 INNER JOIN Project ON Project.Pnumber = Works_on.Pno WHERE Employee.Ssn = Works_on.Essn) AS Projects
FROM Employee
JOIN Department ON Department.Dnumber = Employee.Dno

You also don't have to specify the joining relationship in both ON and WHERE.

• Jay Prakash Thakur: getting error - Function JSON_ARRAY__AGG does not exist. :(
• Barmar: Typo, it should be JSON_ARRAYAGG
• Jay Prakash Thakur: its working but while converting it to xml, it gives me one list of dictionaries instead of converting the dictionaries into xml tags. sorry to bother you. please help.
• Barmar: Why are you returning JSON if you want XML? If you need to turn the JSON into XML, that will have to be done in Python, not MySQL.
• Jay Prakash Thakur: I need both json as well as xml. I am saving file as json then reading that json to convert xml.