How to create a Python dictionary from two columns in a SQL table query?

Question

Using Python, I've had success with selecting and updating values by row using mysql.connector with variables in my queries and defining variables (strings and integers) from query results.

I'd like to select all values from two columns in a SQL table and pass them to a dictionary, with one column holding the keys and the other column holding the values paired by table row.

I attempted this. It failed:

cursor.execute('SELECT id, name FROM colors')
for (id, name) in cursor:
   test001 = dict([id, name])

where is the `findall()` statement after `execute` ? and if its still failing what is the error ? — Sowjanya R Bhat, May 25 '20 at 21:09
I think you're building the `dict` wrong, try this: `dict([[id, name]])` or like `test001 = {id: name}` — Milan Cermak, May 25 '20 at 21:13
Does this answer your question? [Python: use mysqldb to import a MySQL table as a dictionary?](https://stackoverflow.com/questions/2180226/python-use-mysqldb-to-import-a-mysql-table-as-a-dictionary) — Milan Cermak, May 25 '20 at 21:14

score 0 · Answer 1 · answered May 25 '20 at 21:24

0

You need to initialize an empty dict then add an entry to it for each row in your result set.

cursor.execute('SELECT id, name FROM colors')
test001 = {}
for (id, name) in cursor:
   test001[id] = name

answered May 25 '20 at 21:24

Alan Hoover

1,430
2
9
13

score 0 · Answer 2 · answered Jul 21 '22 at 08:54

0

Assuming, that the fetched rows go to a list of tuples, you can just build a dictionary from it in Python 3, like:

selection = []
exec = cursor.execute('SELECT id, name FROM colors')
selection.extend(exec.fetchall())
d_selection = dict(selection)

answered Jul 21 '22 at 08:54

JeyJey

41
4

How to create a Python dictionary from two columns in a SQL table query?

2 Answers2