10

I am using mysqldb to connect to mysql database and I get the metadata/columns in a variable and data/row in another. Now I have to consolidate the list and tuple into a dict and also preserve the order. I know that dicts are orderless but is there any alternative to this?

cursor.description = ['userid', 'cid', 'mid', 'did', 'msid']
data = (29L, 35L, None, '', None)

result = {}
for i in data:
    result.update = dict(zip(cols, i))

Expected result

result = {'userid': 29L, 'cid': 35L, 'mid': None, 'did': '', 'msid': None}
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
ronak
  • 1,770
  • 3
  • 20
  • 34
  • "I know that dicts are orderless..." What problem are you hoping to solve by creating the `dict`? – Karl Knechtel Mar 12 '13 at 21:57
  • Note that you can get `dict`s with a custom `Cursor`: http://geert.vanderkelen.org/fetching-rows-as-dictionaries-with-mysql-connectorpython/. You can combine that with `OrderedDict` to get ordered dictionaries from `.fetch` operations and cursor iteration. – nneonneo Mar 12 '13 at 22:00

2 Answers2

16

Use an OrderedDict:

from collections import OrderedDict

result = OrderedDict(zip(cursor.description, data))

Example:

>>> from collections import OrderedDict
>>> cols = ['userid', 'cid', 'mid', 'did', 'msid']
>>> data = (29L, 35L, None, '', None)
>>> result = OrderedDict(zip(cols, data))
>>> result
OrderedDict([('userid', 29L), ('cid', 35L), ('mid', None), ('did', ''), ('msid', None)])
>>> result['userid']
29L
>>> result['cid']
35L
>>> list(result)
['userid', 'cid', 'mid', 'did', 'msid']

From CPython 3.6 onwards, and Python 3.7 onwards, regular dicts are sorted by insertion order, so you can use dict here instead of OrderedDict if you know your code will run under a suitable version.

Python 3.7+ only (or Python 3.6 under CPython):

>>> cols = ['userid', 'cid', 'mid', 'did', 'msid']
>>> data = (29, 35, None, '', None)
>>> result = dict(zip(cols, data))
>>> result
{'userid': 29, 'cid': 35, 'mid': None, 'did': '', 'msid': None}
>>> result['userid']
29
>>> result['cid']
35
>>> list(result)
['userid', 'cid', 'mid', 'did', 'msid']
nneonneo
  • 171,345
  • 36
  • 312
  • 383
  • 1
    For versions of Python older than 2.7, you'll need to install it. `pip install ordereddict` – Carl Smith Mar 12 '13 at 22:38
  • how can I make a dictionary of the result? `[[('userid', 32L), ('cid', 40L), ('mid', None), ('did', ''), ('msid', None)]]` – ronak Mar 12 '13 at 23:02
  • 1
    `result` *is* a dictionary (`OrderedDict` is a subclass of `dict`). It is also ordered, unlike plain `dict`. For example, you can access `result['userid']` and basically treat it like a `dict`. – nneonneo Mar 12 '13 at 23:04
1

forget the dict

>>> cols=['userid', 'cid', 'mid', 'did', 'msid']
>>> data = (29L, 35L, None, '', None)
>>> zip(cols,data)
[('userid', 29L), ('cid', 35L), ('mid', None), ('did', ''), ('msid', None)]

If you have lots of result sets then set up an array first and append to it

>>> myarray.append(zip(cols,data))
Vorsprung
  • 32,923
  • 5
  • 39
  • 63
  • 1
    Good, but it makes looking for a specific column cumbersome. Maybe I just want to write `row['userid']`. – nneonneo Mar 12 '13 at 22:02