What's a better way to handle this in Python

Question

Simple problem, I have it solved .. but python has a million ways to solve the same problem. I don't want the most terse solution, I just want one that makes more sense than the following:

# sql query happens above, returns multiple rows
rows = cursor.fetchall()
cursor.close()
cntdict = {}
for row in rows:
 a, b, c = row[0], row[1], row[2]
 cntdict = {
  a : { "b":b, "c":c }
 }
print dict(cntdict)

note, a is always unique above. I want a dictionary created from the result set. Later on in my script I need to reference the values in the dictionary for each row that occurred. I'm trying to index the dictionary by making the key the value from the var a. that should point to another dictionary with two more key/value pairs that describe a... unless there's a smarter/shorter way to do it.

Answer 1

You can do the same thing with a generator expression and the dictionary constructor:

dict((row[0], {"b": row[1], "c": row[2]}) for row in rows)

And here is the code in action:

>>> rows = [[1, 2, 3], [4, 5, 6]]
>>> dict((row[0], {"b": row[1], "c": row[2]}) for row in rows)
{1: {'c': 3, 'b': 2}, 4: {'c': 6, 'b': 5}}

I can't remember if MySQLdb returns rows as list of tuples, if that is the case then you can do this:

dict((a, {"b": b, "c": c}) for a, b, c in rows)

Answer 2

You might want to look into using an ORM to abstract your database. I can't tell for certain from such a short snippet, but it looks like your data might work well with an ORM if you are just going to build a dict out of it anyway.

My three favorite ORMs: the one in Django (easy to use, works best with data that is regular), SQLAlchemy (best choice if your database has a complex schema), and Autumn (small and simple, ideal for use with SQLite).

Answer 3

As Nadia alluded to, tuple expansion is really handy when working with query results. Her dict() expressions are really nice in this case, though for other queries when you need to do something different with the results just use tuple expansion in the for loop:

for a, b, c in rows:
   # do something