Sorting Data Returned From Database

[Timbo03]

Hi

Firstly, sorry if it didn't sound like I had tried to solve the problem myself & this is for a personal learning project as a newbie.

noisefloor I did initially try something like that with 3 tables as you suggested, but I think with a large amount of data it was quite slow using a SQL query.

I do already have something similar working in a simpler way where only the recipe id's sorted by matches to ingredients in input string are returned see underneath. The second database table column is simpler for this with just the recipe ids for each ingredient:

UsersIngredientList= UsersIngredientList.split(",")
    recipeidlist=()
    counter=0
    while counter<len(UsersIngredientList):
        cursor.execute("select recipe_ids from ingredient where display_name = '"+UsersIngredientList[counter]+"'")        
        recipeidlist = sum((recipeidlist, cursor.fetchone()), ())
        counter+=1
    cursor.close()
    recipeidlist = ''.join(recipeidlist)   
    recipeidlist=recipeidlist.replace("</id>","")
    recipeidlist = list(recipeidlist.split("<id>")) 
    recipeidlist.pop(0)
    runtime = round(time.time() - start_time, 2)
    print ("Getting list of recipe ID's from database for users ingredients took ", runtime," seconds to run")

    start_time = time.time()  
    CleanedandSortedRecipeIDList = Counter(recipeidlist).most_common()
    runtime = round(time.time() - start_time, 2)
    print ("Sorting the recipe ID list by frequency then removing duplicates took ", runtime," seconds to run")

    start_time = time.time()
    if len(CleanedandSortedRecipeIDList)>500:
        CleanedandSortedRecipeIDList= CleanedandSortedRecipeIDList[0:500]     
    runtime = round(time.time() - start_time, 2)
    print ("Limiting recipe ID list to 500 items took ", runtime," seconds to run")

    start_time = time.time()
    counter = 0
    CleanedandSortedRecipeIDListString=""
    while counter<len(CleanedandSortedRecipeIDList):
        CleanedandSortedRecipeIDListString= CleanedandSortedRecipeIDListString+CleanedandSortedRecipeIDList[counter][0]+","
        counter = counter+1
    CleanedandSortedRecipeIDListString = CleanedandSortedRecipeIDListString[:-1]
    runtime = round(time.time() - start_time, 2)
    print ("Building a string from all the recipe ID's for the SQL statement took ", runtime," seconds to run")