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.

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.

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 -

Edit2 - @Barmar solution gives output like this -

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

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

Use JSON_ARRAY_AGG and JSON_OBJECT instead of constructing JSON by hand. — Barmar
– Barmar, Commented Aug 10, 2021 at 7:34
Specifically, it looks like you forgot to quote all the " after : — Barmar
– Barmar, Commented Aug 10, 2021 at 7:36
@Barmar I am new to mysql, could you please help me to correct the query using json_object or json_array_agg. — Jay Prakash Thakur
– Jay Prakash Thakur, Commented Aug 10, 2021 at 7:36

Barmar · Accepted Answer · 2021-08-10 07:45:01Z

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.

edited Aug 10, 2021 at 7:45

answered Aug 10, 2021 at 7:42

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

9 Comments

Jay Prakash Thakur Over a year ago

getting error - Function JSON_ARRAY__AGG does not exist. :(

Barmar Over a year ago

Typo, it should be JSON_ARRAYAGG

Jay Prakash Thakur Over a year ago

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 Over a year ago

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 Over a year ago

I need both json as well as xml. I am saving file as json then reading that json to convert xml.

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related