Load drinks and their ingredients into dictionaries

I'm working on a web application using Bottle, and it is, at this point, functional.

The gist of it is that I have a database of drinks, with associated IDs, and associated sets of ingredients. the name and ID of the drink are passed to the client, which displays them.

Currently, when the program loads up, all drinks and their associated ingredient amounts are loaded into a list of dictionaries, like so:

SampleDrinkDict = {'name': 'Screwdriver', 'id': 4, 'vodka':2, 'orange juice':6}
DrinkDictList = []
DrinkDictList.append(SampleDrinkDict)

Once the DrinkDictList is fully populated, I pass the names and IDs of every one to the client, which populates those in a list. When a drink is selected, it makes a callback to scan the DrinkDictList and find the matching 'id' field. Once it finds it in the list, the ingredient amounts and details are populated on the page.

Would it be faster/better/more proper for me to only query ingredients from the DB once a drink is selected?

For example, would it be faster/more pythonic/better to do this?

SampleDrinkDict = {'name': 'Screwdriver', 'id': 4,}
DrinkDictList = []
DrinkDictList.append(SampleDrinkDict)

@bottle.route('/drinkSelected/:id'):
def getIng(id,db):
    sqlString = '''SELECT ingredients WHERE Drink_id = ?'''
    argString = (id,)
    ingredientList = db.execute(SqlString, argString).fetchall()

This is all running on a Raspberry Pi, and I'm worried about taxing the RAM once the drink list becomes huge. Is there any reason to go with one approach over the other?

Whether you need to store data in the DB is really gonna depend on your testing, but here are a few thoughts about how to reduce memory usage while still building the entire drink table at startup:

• While they're convenient, you don't necessarily need to use dictionaries for this. You could replace each dictionary with a tuple where the first item is the drink name, the second item is the drink id, and the rest of the items are tuples of size 2 with the ingredient name and the ratio for that ingredient. This would change your example to ('Screwdriver', '4', ('vodka', 2), ('orange juice', 6)). sys.getsizeof gives 280 for the dictionary and only 88 for the tuple, a significant reduction.

• If you're still pressed for space, you can take advantage of the fact that you probably have a lot of overlap in terms of your ingredients. Build a list with a string for each ingredient that one of your drinks uses. Then each tuple can simply have an int referring to the index of the ingredient in your ingredient list. This prevents you from storing a string like 'vodka' 20 different times. Once again using sys.getsizeof gives a value of 42 for the string 'vodka' and a value of 24 for the number 100. This will of course only work if you have enough overlap in your ingredients.

If those ideas aren't enough to reduce your memory usage to an acceptable amount, you're probably going to need to go with your second solution(unless some other folks have some better ideas!). I don't really see one of your solutions as more pythonic than the other, so I wouldn't worry about that.

And enjoy the raspberry pi!

StackExchange Code Review Q#26127, answer score: 3