MySQL table to Python list of lists using fetchall()

Question

how can I transform this table:

ID ITEM_CODE
--------------------
1 1AB
1 22S
1 1AB
2 67R
2 225
3 YYF
3 1AB
3 UUS
3 F67
3 F67
3 225
......

..to a list of lists, each list being a distinct ID containing its allocated item_codes?

in the form: [[1AB,22S,1AB],[67R,225],[YYF,1AB,UUS,F67,F67,225]]

Using this query:

SELECT ID, ITEM_CODE 
FROM table1 
ORDER BY ID;

and doing cursor.fetchall() in python does not return it as a list nor ordered by ID

Thank you

Answer 1

You probly will have less post-processing in Python using that query:

SELECT GROUP_CONCAT(ITEM_CODE) 
FROM table1
GROUP BY ID 
ORDER BY ID;

That will directly produce that result:

1AB,22S,1AB
67R,225
YYF,1AB,UUS,F67,F67,225

After that, cursor.fetchall() will directly return more or less what you expected, I think.

---

EDIT:

result = [ split(row, ',') for row in cursor.fetchall()]