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

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

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating