Populate dictionary from MySQL python

Question

I am pulling data from a MySQL db which comes in as a list.

'''
    my_cursor.execute(sql_str)
    my_results = my_cursor.fetchall()
    print(my_results)
'''

OUTPUT [('Allen, Jamie', 2), ('Anderson, Abbie', 1391), ('Anderson, Marcie', 1380), etc.,etc.

Instead of a list, I want to populate a dictionary.

'''
    my_cursor.execute(sql_str)
    my_result = {}
    my_result = [{'Name': row[0], 'ID': row[1]} for row in my_cursor.fetchall()]
    print(my_result)
'''

OUTPUT [{'Name': 'Allen, Jamie', 'ID': 2}, {'Name': 'Anderson, Abbie', 'ID': 1391}, etc.

As you can see I am getting a list of directories not a directory. I really would appreciate your help.

Thanks,

John

Daniser · Accepted Answer · 2020-07-27 19:26:29Z

1

You can convert the list to a dictionary like this:

L = [('Allen, Jamie', 2), ('Anderson, Abbie', 1391), ('Anderson, Marcie', 1380)]

D = dict(L)

print(D)

Now the dictionary D looks like this:

{'Allen, Jamie': 2, 'Anderson, Abbie': 1391, 'Anderson, Marcie': 1380}

answered Jul 27, 2020 at 19:26

Daniser

1624 bronze badges

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

2 Comments

John Kirkman Over a year ago

after hours of trying, the function worked great. many thanks

Daniser Over a year ago

Happy to help !, can you please mark my answer as correct (the green check image) ?

erik258 · Accepted Answer · 2020-07-27 19:28:09Z

0

You're using a list comprehension, so you're getting a list:

[{'Name': row[0], 'ID': row[1]} for row in my_cursor.fetchall()]

if you want a dictionary, use a dictionary comprehension instead:

$ python3 ./t.py
{'Dan': 1, 'John': 2}

data = [
  [ "Dan", 1 ],
  [ "John", 2 ]
]
print({row[0]: row[1] for row in data})

answered Jul 27, 2020 at 19:28

erik258

16.4k2 gold badges32 silver badges47 bronze badges

Comments

Emad Mohamadi · Accepted Answer · 2020-07-27 19:30:05Z

0

You could do as follows:

my_cursor.execute(sql_str)
my_result = {}
for x, y in a:
    my_result[x] = y
print(my_result)

answered Jul 27, 2020 at 19:30

Emad Mohamadi

1111 silver badge9 bronze badges

Comments

cmyui · Accepted Answer · 2020-07-27 19:38:31Z

0

Depending on what library you're using for SQL, you may actually just be able to pass dictionary = True as an argument when creating your cursor object - I know this at least exists in mysql-connector-python.

my_cursor = conn.cursor(dictionary = True)

answered Jul 27, 2020 at 19:38

cmyui

814 bronze badges

Comments

SamR · Accepted Answer · 2020-07-27 21:21:22Z

0

You don't say what MySQL package you're using. If it's mysql-connector-python, you can use my_cursor.description or my_cursor.column_names:

colnames = my_cursor.column_names
# Alternate for older library versions: colnames = [desc[0] for desc in my_cursor.description]
my_results = [dict(zip(colnames,record)) for record in my_results]

answered Jul 27, 2020 at 21:21

SamR

3361 silver badge3 bronze badges

Collectives™ on Stack Overflow

Populate dictionary from MySQL python

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related