MySQL dump a table or saving table contents as a python dictionary

Question

I have a very simple and small db table with 2 columns only: Id and name. Given the id, I have to find and return the corresponding name. I want to save the contents in a dictionary and look for the corresponding value from there instead of querying the database each time. I have came across this How to convert SQL query results into a python dictionary but they are aiming to save each row as a dict, whilst I think I do not need a list of dictionaries just a dict with key value pairs.

def get_name(db_conn):
    cursor = db_conn.cursor()
    cursor.execute("SELECT id, name FROM table")
    rows = cursor.fetchall()
    d = {}
    for row in rows:
       id = row[0]
       name = row[1]
       d[id]= name
print(d)

What would be the best approach given the task I have?

Why are you appending? Can multiple names associate to a single id? — MatBailie
– MatBailie, Commented Dec 13, 2022 at 21:09
Each id is associated to a single and unique name. I just do not know how to save the data in a dictionary, to avoid constant quering of the db. I have edited slightly the code. — user19441790
– user19441790, Commented Dec 13, 2022 at 21:13

MatBailie · Accepted Answer · 2022-12-13 21:14:03Z

0

Dictionary comprehensions should be straight forward enough...

d = { row[0]: row[1] for row in cursor.fetchall() }

or...

d = { id: name for (id, name) in cursor.fetchall() }

This processes every row in cursor.fetchall() and yields key: value pairs directly into a dictionary.

It's very similar to your loop, but the looping is done in C rather than natively in python.

answered Dec 13, 2022 at 21:14

MatBailie

87.4k19 gold badges112 silver badges144 bronze badges

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

1 Comment

user19441790 Over a year ago

Thank you, yes- that is more elegant and succinct solution.

Collectives™ on Stack Overflow

MySQL dump a table or saving table contents as a python dictionary

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related