CODEWITHSUNDEEP
pythonmysql
user2578185
user2578185

Reputation: 437

MySQL table to Python list of lists using fetchall()

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

Upvotes: 0

Views: 1068

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

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()]

Upvotes: 1

Related Questions