Python add items into dictionary from database table (sqlite3)

Question

I have created a database table called fruits with 3 columns, id(int primary key), fruits(text) and weight(float).

id	fruit	weight
1	Apple	80.5
2	Pear	150.8
3	Kiwi	69

How do I create a dictionary and add all the fruits and weight as key-value pairs to the dictionary?

import sqlite3

conn = sqlite3.connect("database.db")

sql = """SELECT fruit,weight FROM fruits"""

cursor = conn.execute(sql)
data = cursor.fetchall()

fruitweight= {}
i = 0

while(i < len(data)):
    fruitweight['<fruitname>'] = <fruitweight>
    i = i+1

Jussi Nurminen · Accepted Answer · 2021-10-07 10:38:42Z

2

Something like this:

for row in data:
    name, weight = row
    fruitweight[name] = weight

fetchall() returns a list of tuples of values from the database according to your query. The above unpacks the tuples into two variables.

If you want to get fancy, you can use a dictionary comprehension with tuple unpacking:

fruitweight = {name: weight for name, weight in data}

And finally, execute() actually returns a cursor that you can iterate over, so I think your code can be reduced to the following:

import sqlite3

conn = sqlite3.connect("database.db")

sql = """SELECT fruit,weight FROM fruits"""
fruitweight = {name: weight for name, weight in conn.execute(sql)}

edited Oct 7, 2021 at 10:38

answered Oct 7, 2021 at 10:17

Jussi Nurminen

2,4081 gold badge12 silver badges18 bronze badges

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

3 Comments

Trelaquix Over a year ago

Thanks for the quick answer @Jussi Nurminen. I didn't know you could just use "name, weight = row". Is this a type of string manipulation?

Jussi Nurminen Over a year ago

No, it's called "tuple unpacking". It splits a tuple into several separate variables.

2e0byo Over a year ago

specifically, name and weight are just variable names. This is equivalent to name = row[0]; weight=row[1].

ELAi · Accepted Answer · 2021-10-07 10:35:12Z

0

I don't think that the cursor is correct but you should use for loop instead:

import sqlite3

conn = sqlite3.connect("database.db")
cur = conn.cursor()

sql = """SELECT fruit,weight FROM fruits"""

cur.execute(sql)
data = cur.fetchall()


fruitweight= {}

for i in data:
    fruitweight[i[0]] = i[1]

conn.close()

print(fruitweight)

edited Oct 7, 2021 at 10:35

answered Oct 7, 2021 at 10:24

ELAi

1842 silver badges8 bronze badges

1 Comment

user2668284 Over a year ago

Why is there a need to commit?

score 0 · Accepted Answer · 2021-10-07 10:38:46Z

0

You could do it like this:

import sqlite3

with sqlite3.connect('/Users/andy/andy') as conn:
    cursor = conn.execute('select fruit, weight from fruits')
    D = dict()
    for row in cursor.fetchall():
        D[row[0]] = row[1]
    print(D)

edited Oct 7, 2021 at 10:38

answered Oct 7, 2021 at 10:26

user2668284

Collectives™ on Stack Overflow

Python add items into dictionary from database table (sqlite3)

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related