CODEWITHSUNDEEP
pythonsqlitedictionary
ch4rl1e97
ch4rl1e97

Reputation: 686

How do I turn this data from an SQL database table into a dict?

The SQL table is simple, 3 columns.

id | guild | word

id is just your usual db counter that I'm not interested in, the others are strings.

Multiple words belong to one guild, e.g.

1 |  5597  |  egg
2 |  5597  |  cheese
3 |  5534  |  flour
4 |  5534  |  milk

I'd like to take the result of SELECT * FROM table and turn it into a dictionary, structured such that the guild is used as keys, the corresponding dictionary value is a list of all the words with common guilds. Two different guilds could share a common word, or might not, this is not significant.

In this dict structure, you'd access the word "cheese" from line 2 above as my_dict["5597"][1]. (I don't intend to work with the list data explicitly like this in practice, just doing this for for example sake.)

Hopefully that made sense.

I've obtained rows (tuples) of the SQL data using sqlite3 as such:

>>> conn = sqlite3.connect("file.db")
>>> c = conn.cursor()
>>> c.execute("SELECT guild,word FROM table")
>>> rows = c.fetchall()
>>> print(rows[0])
(5597, egg)

What's an efficient way to go about building the dict of word lists from here? Not against using non-standard libs if needed. The use is such that I can access a guild's list of words from knowing only the guild's name.

Upvotes: 1

Views: 2235

Answers (2)

Pynchia
Pynchia

Reputation: 11580

You can use defaultdict and iterate on the cursor directly

d = defaultdict(list)
for row in c:
    d[row[0]].append(row[1])

To make it more readable change the loop to

d = defaultdict(list)
for guild, word in c:
    d[guild].append(word)

Upvotes: 2

Aleksander Krauze
Aleksander Krauze

Reputation: 6061

Try this:

d = {}
for row in rows:
    key, value = row
    try:
        d[key].append(value)
    except KeyError:
        d[key] = [value]

This works as follows. For each guild if it's not in the dictonary we add it and set value to a list with one element (word). If guild is already in the dictonary we simply append ned word to it's value list.

Upvotes: 0

Related Questions