CODEWITHSUNDEEP
pythonmysqlmysql-python
Bulbasaur
Bulbasaur

Reputation: 714

Python MySQLdb: How to get the result of a sql select having a group by into a dict?

I have a sql query like this running in python:

select sum(a), date from table group by date

Then I execute this query and get the result:

    cursor.execute (query, queryParameters)
    rows = cursor.fetchall();

As expected rows is an array:

    (('2014-05-13', Decimal('1.6666666667')), ('2014-05-14', Decimal('33.0151515152')), ('2014-05-15', Decimal('66.4850000000')), ('2014-05-16', Decimal('49.8274022154')), ('2014-05-18', Decimal('4.0000000000')))

But i want it as an hash, where the date is the key and the sum is the value(every row is a key-value pair). And since it is a group by date, it makes sense to have it in a hash. Something like this:

{ '2014-05-13' => '1.6666666667', '2014-05-14' => '33.0151515152'....}

How can I achieve this?

Upvotes: 3

Views: 3373

Answers (1)

alecxe
alecxe

Reputation: 473763

Change the order of the fields and pass fetchall() results to dict():

query = "select date, sum(a) from table group by date"
cursor.execute (query, queryParameters)
result = dict(cursor.fetchall())

Demo:

>>> from decimal import Decimal
>>> data = (('2014-05-13', Decimal('1.6666666667')), ('2014-05-14', Decimal('33.0151515152')), ('2014-05-15', Decimal('66.4850000000')),
>>> dict(data)
{'2014-05-18': Decimal('4.0000000000'), '2014-05-13': Decimal('1.6666666667'), '2014-05-15': Decimal('66.4850000000'), '2014-05-14': Decimal('33.0151515152'), '2014-05-16': Decimal('49.8274022154')}

Upvotes: 2

Related Questions