2

This question in stack overflow answers how would one get dictionary from tables using pymysql. However, this method outputs column header as keys and its value as data in that column.

Whats the best way to have actual data as keys and values?

For example:

 Name    |Age
 -------------
 John    |25
 Tom     |45
 Tammy   |18

I want

  {John:25, Tom:45, Tammy:18}

NOT 

 [{Name:John},{Age:25},....]

This is what i have right now:

def name2dict(name_list):
    name_list_tuple = tuple(name_list)
    conn = pymysql.connect()
    cur = conn.cursor(pymysql.cursors.DictCursor)
    Name2pos = """SELECT Tables.ID, Tables.Position FROM Tables where Tables.Name in %s"""
    cur.execute(Name2pos, [name_list_tuple])
    query_dict = cur.fetchall()
    cur.close()
    conn.close()
    return query_dict
Community
  • 1
  • 1
msakya
  • 9,311
  • 5
  • 23
  • 31
  • 1
    If your query is "SELECT name, age FROM some_table" (eg: it returns two columns - so don't use a cursordict), then just use `dict(your_cursor_that_executes_the_query)` Could you show your current code? – Jon Clements Oct 23 '14 at 15:07
  • Sorry, what is the structure of your current dict? Is it a list of dictionaries where each dict contains both a name and an age key? Or is it a list of dictionaries all having exactly one key, sometimes "Name" and sometimes "Age"? Or something else? – Kevin Oct 23 '14 at 15:09
  • Wow dont understand why this deserve down votes? @Joe, I just added the code. And, i think your suggestion should work. Thanks – msakya Oct 23 '14 at 15:21
  • @msakya didn't realise I'd changed my name to Joe :) - anyway, adapted your code slightly, as long as you're getting a sequence of 2-tuples, passing it to `dict` should just do as you expect – Jon Clements Oct 23 '14 at 15:33
  • 1
    oh my bad Jon. Real sorry. Jon sounds better. – msakya Oct 23 '14 at 15:36

2 Answers2

4

Don't use a dictionary cursor - instead use the normal one. A simple example slightly adapting your code (assuming it runs okay as can't check), but can certainly be improved:

def name2dict(name_list):
    name_list_tuple = tuple(name_list)
    conn = pymysql.connect()
    cur = conn.cursor()
    Name2pos = """SELECT Tables.ID, Tables.Position FROM Tables where Tables.Name in %s"""
    cur.execute(Name2pos)
    query_dict = dict(cur.fetchall())
    cur.close()
    conn.close()
    return query_dict
Jon Clements
  • 138,671
  • 33
  • 247
  • 280
  • Yup, this is what i ended up doing after a cup of coffee. Thanks a lot. Still bummed about (-ve) votes...lol – msakya Oct 23 '14 at 15:34
  • @msakya and instead of `.fetchall()` you may want to consider trying `query_dict = dict(cur.execute(Name2pos, multi=True))` to save loading the result set into memory first... (not sure if the pymysql api supports that though... don't have it handy to check)... it's probably possible to just use `query_dict = dict(cur)` (assuming `cur` is iterable and not `None`) – Jon Clements Oct 23 '14 at 15:35
3

It's not clear to me what the structure of your current data is, so I guess I'll just write a separate answer for each one!

d = {
    "Name": ["John", "Tom", "Tammy"], 
    "Age": [25,45,18]
}
new_d = dict(zip(d["Name"], d["Age"]))
print new_d

rows = [
    {"Name": "John", "Age": 25},
    {"Name": "Tom", "Age": 45},
    {"Name": "Tammy", "Age": 18},
]
new_d = {row["Name"]: row["Age"] for row in rows}
print new_d

data = [
    {"Name": "John"}, 
    {"Age": 25},
    {"Name": "Tom"}, 
    {"Age": 45},
    {"Name": "Tammy"}, 
    {"Age": 18},
]
d = {
    "Name": [item["Name"] for item in data if "Name" in item],
    "Age": [item["Age"] for item in data if "Age" in item],
}
new_d = dict(zip(d["Name"], d["Age"]))
print new_d

In any case, the result is:

{'John': 25, 'Tammy': 18, 'Tom': 45}
Kevin
  • 74,910
  • 12
  • 133
  • 166