Question

Create a report that lists customers with the total value of their orders from the database. Each row of the table should list the customer name and the total value of all orders. Rows should be in descending order according to the total. Just list the first 5 customers, those with the highest total orders.

This part is more complex and, so, can be approached in many ways. You may decide to use simple queries and put the information together using Python code. Alternatively, you might create a single SQL command that will produce the data needed.

Just one strategy will be outlined here, then some variants will be mentioned.

• Start an empty list for all customers
• Get a list of customers
  • For each customer get a list of invoices for that customer
    • initialize a variable to get the total of all invoices for the customer
    • For each invoice get a list of pizzas on that invoice
      • For each pizza, get a list of toppings on the pizza
        • Add up the costs of the toppings and add to the customer total
        • Add the crust price and sauce price to the customer total
    • Make a list of data about that customer and add to the list for all customers
• Sort the list for all customers
• Print the data

pizza_service.py

import sqlite3

class PizzaServices:

    def __init__(self):
        self.connection = sqlite3.connect("pizza-190807A.sqlite")

    def __enter__(self):
        return self

    def __exit__(self, exe_type, exc_val, exl_tb):
        crs = self.connection.cursor()

    def do_query(self, query, parameters=None):
        crs = self.connection.cursor()
        if parameters:
            crs.execute(query, parameters)
        else:
            crs.execute(query)

        return crs.fetchall()

    def customer(self):
        return self.do_query("select * from customer")

Answer 1

import sqlite3

class PizzaServices:

        def __init__(self):
                self.connection = sqlite3.connect("pizza-190807A.sqlite")

        def __enter__(self):
                return self

        def __exit__(self, exe_type, exc_val, exl_tb):
                crs = self.connection.cursor()

        def do_query(self, query, parameters=None):
                crs = self.connection.cursor()
                if parameters:
                        crs.execute(query, parameters)
                else:
                        crs.execute(query)
                return crs.fetchall()  # Do not indent it inside, it should execute in both if-else cases

        def customer(self):
                return self.do_query("select * from customer")

with PizzaServices() as cs:
        
        toppingPrices = {}
        for row in cs.do_query('select toppingId, price from topping'):                          
                toppingPrices[row[0]] = row[1]
        
        crustPrices = {}
        for row in cs.do_query('select crustId, price from crust'):                              
                crustPrices[row[0]] = row[1]
        
        saucePrices = {}
        for row in cs.do_query('select sauceId, price from sauce'):                              
                saucePrices[row[0]] = row[1]
        
        result = []
        for row in cs.do_query('select customerId, firstName, lastName from customer'):
        
                custId = row[0]
                invoices = cs.do_query('select invoiceId from invoice where customerId = (?)', (custId, ))
                totalAmount = 0
                
                for invoiceId in (list(map(lambda x: x[0], invoices))):
                        pizzas = cs.do_query('select pizzaId, crustId, sauceId, discount from pizza where invoiceId = (?)', (invoiceId, ))
                        
                        for pizza in pizzas:
                                pizzaId = pizza[0]
                                toppings = cs.do_query('select toppingId, pizzaId from pizzaTopping where pizzaId = (?)', (pizzaId, ))
                                
                                for topping in toppings:
                                        totalAmount += toppingPrices[topping[0]]
                                        
                                totalAmount += crustPrices[pizza[1]]
                                totalAmount += saucePrices[pizza[2]]
                                totalAmount -= pizza[3]
                        
                result.append((row[1], row[2], totalAmount))
        
        result = sorted(result, key=lambda x: x[2], reverse=True)
        print('{:<20} {:<20} {:<20}'.format('FirstName', 'LastName', 'OrderAmount'))
        for r in result:
                print('{:<20} {:<20} {:<20}'.format(r[0], r[1], r[2]))



                
                
                
                
                
                
                

**************************************************

Thanks for your question. We try our best to help you with detailed answers, But in any case, if you need any modification or have a query/issue with respect to above answer, Please ask that in the comment section. We will surely try to address your query ASAP and resolve the issue.

Please consider providing a thumbs up to this question if it helps you. by Doing that, You will help other students, who are facing similar issue.