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

Sylvain Leroux · Accepted Answer · 2013-08-06 18:27:01Z

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

edited Aug 6, 2013 at 18:27

answered Aug 6, 2013 at 18:21

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

MySQL table to Python list of lists using fetchall()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related