Data extraction and transform efficiency
Question: I've got a Python script that connects to a MySQL database and executes a
number of nested SELECT queries. It's basically a giant for loop. The database
is structured such that Businesses have Menus, Menus have Sections, and
Sections have Items. The script queries all the Businesses, and for each
Business, it queries all of its Menus, and so on. It builds a big dictionary
along the way that it then spits out as JSON.

It looks something like this:

    
    
    #!/usr/bin/env python
    
    from bottle import route, run
    import mysql.connector
    import json
    import collections
    import datetime
    
    def getBusinesses():
        conn = mysql.connector.connect(user="APIUser", password="abc123", host="12.34.56.78", port="54321", database="businesses")
        cursor = conn.cursor()
        objects = {}
    
        businessesQuery = ("SELECT * FROM business")
        cursor.execute(businessesQuery)
        businessRows = cursor.fetchall()
    
        businessObjects = []
        for businessRow in businessRows:
            print businessRow[0]
            businessDict = collections.OrderedDict()
            businessDict['id'] = businessRow[0]
            businessDict['business_name'] = businessRow[1]
            businessDict['business_address1'] = businessRow[2]
            businessDict['business_address2'] = businessRow[3]
            businessDict['business_city'] = businessRow[4]
            businessDict['business_state'] = businessRow[5]
            businessDict['business_zip'] = businessRow[6]
            businessObjects.append(businessDict)
    
            menuQuery = ("SELECT * FROM menu WHERE business_id = %s" % businessRow[0])
            cursor.execute(menuQuery)
            menuRows = cursor.fetchall()
    
            menuObjects = []
            for menuRow in menuRows:
                menuDict = collections.OrderedDict()
                menuDict['id'] = menuRow[0]
                menuDict['menu_name'] = menuRow[1]
                menuDict['menu_description'] = menuRow[2]
                menuDict['menu_note'] = menuRow[3]
                menuDict['business_id'] = menuRow[4]
                menuObjects.append(menuDict)
    
            businessDict['menus'] = menuObjects
    
            for menuIdx, menuRow in enumerate(menuRows):
                sectionQuery = ("SELECT * FROM menu_section WHERE menu_id = %s" % menuRow[0])
                cursor.execute(sectionQuery)
                sectionRows = cursor.fetchall()
    
                sectionObjects = []
                for sectionIdx, sectionRow in enumerate(sectionRows):
                    sectionDict = collections.OrderedDict()
                    sectionDict['id'] = sectionRow[0]
                    sectionDict['section_name'] = sectionRow[1]
                    sectionDict['section_note'] = sectionRow[2]
                    sectionDict['section_description'] = sectionRow[3]
                    sectionDict['menu_id'] = sectionRow[4]
                    sectionObjects.append(sectionDict)
    
                    businessDict['menus'][menuIdx]['sections'] = sectionObjects
    
                    itemQuery = ("SELECT * FROM menu_item WHERE section_id = %s" % sectionRow[0])
                    cursor.execute(itemQuery)
                    itemRows = cursor.fetchall()
    
                    itemObjects = []
                    for itemIdx, itemRow in enumerate(itemRows):
                        itemDict = collections.OrderedDict()
                        itemDict['id'] = itemRow[0]
                        itemDict['item_name'] = itemRow[1]
                        itemDict['item_description'] = itemRow[2]
                        itemDict['item_note'] = itemRow[3]
                        itemDict['item_price'] = itemRow[4]
                        itemDict['section_id'] = itemRow[5]
                        itemObjects.append(itemDict)
    
                        businessDict['menus'][menuIdx]['sections'][sectionIdx]['items'] = itemObjects
    
    
        objects['businesses'] = businessObjects
        return objects
    
    @route('/test')
    def index():
        return json.dumps(getBusinesses())
    
    run(host='192.168.1.70', port=7070)
    

I want to know if this is an efficient way of doing things. When I deployed my
database remotely (WebFaction) and ran the Bottle server locally, it took
almost 40 seconds to return a few hundred rows. So it seems like something is
amiss. I have a gut feeling that there could be a better way of doing this.
Just not sure what that way is!


Answer: if I had to venture a guess: notice the rough structure of your code is:

    
    
    def getBusinesses():
        businessesQuery = ("SELECT * FROM business")
        businessRows = cursor.fetchall()
    
        businessObjects = []
        for businessRow in businessRows:
            menuQuery = ("SELECT * FROM menu WHERE business_id = %s" % businessRow[0])
            menuRows = cursor.fetchall()
    
    
            for menuIdx, menuRow in enumerate(menuRows):
                sectionQuery = ("SELECT * FROM menu_section WHERE menu_id = %s" % menuRow[0])
                cursor.execute(sectionQuery)
                sectionRows = cursor.fetchall()
    
                sectionObjects = []
                for sectionIdx, sectionRow in enumerate(sectionRows):
                    itemQuery = ("SELECT * FROM menu_item WHERE section_id = %s" % sectionRow[0])
                    itemRows = cursor.fetchall()
    

That is, you execute nearly identical queries in a loop for `menu`,
`menu_section` and especially `menu_item`. Also, you're using `fetchall()` to
return the full contents of the result set but examine each element only once,
in a loop, where you create _another_ list of objects.

what you might want instead is something more like:

    
    
    businesses = []
    cursor.execute("select * from business")
    row = cursor.fetchone()
    while row is not None:
        business.append(...(row))
        row = cursor.fetchone()
    
    cursor.execute("select * from menu")
    row = cursor.fetchone()
    while row is not None:
        business[row['business_id']].menus.append(...(row))
        row = cursor.fetchone()
    
    cursor.execute("select menu.business_id, menu_section.*"
                   " from menu_section"
                   " join menu on menu.id = menu_section.menu_id")
    row = cursor.fetchone()
    while row is not None:
        business[row['business_id']][row['menu_id']].sections.append(...(row))
        row = cursor.fetchone()
    
    cursor.execute("select menu.business_id, menu_section.menu_id, menu_item.*"
                   " from menu_item"
                   " join menu_section on menu_section.id = menu_item.section_id"
                   " join menu on menu.id = menu_section.menu_id")
    row = cursor.fetchone()
    while row is not None:        
        business[row['business_id']][row['menu_id']][row['section_id'].items.append(...(row))
        row = cursor.fetchone()
    

so that you're issuing a much smaller number of queries, and only loading the
amount of data you can process in one go.

