CODEWITHSUNDEEP
pythondatabasesqlite
rbeatt
rbeatt

Reputation: 73

Multiplying columns in SQLite3 from two different tables

I am currently working on a school project where I must develop a database application with a graphical user interface using Python, Tkinter, and SQLite3. The user enters the ProductID and quantity into a form, and I am trying to get the program to select the price of the product from the product table using the ProductID, and multiply this price by the quantity input by the user to give the total cost of the order and insert this result into the total field on the order table.

I am receiving the following error:

  File "c:\Users\Ryan\OneDrive - C2k\A2 2020-2021\Computer Science\A2 Unit 5\Code\OrderForm.py", line 81, in CalculatePrice
    price = ((int(quantity)*(int(x) for x in results)))
TypeError: unsupported operand type(s) for *: 'int' and 'generator'

I have carried out research into this error, however, I still cannot understand how to get this to work or if it is even possible. If anybody could take a look at my code below and try help me out with doing this, I would really appreciate it.

    def CalculatePrice(self):
        orderid = self.OrderIDEntry.get()
        productid = self.ProductIDEntry.get()
        quantity = self.QuantityEntry.get()
        with sqlite3.connect("LeeOpt.db") as db:
            cursor = db.cursor()
            searchprice = ('''SELECT Price FROM Products WHERE ProductID = ?''')
            cursor.execute(searchprice, [(productid)])
            results = cursor.fetchall()
            
            if results:
                for i in results:
                    price = ((int(quantity)*(int(x) for x in results)))
                    addprice = ('''INSERT INTO Orders(OrderTotal)
                    VALUES (?)''')
                    cursor.execute(addprice, [(price)])
                    self.ClearEntries()
            else:
                tkinter.messagebox.showerror("Error", "No product was found with this ProductID, please try again.")
                self.ClearEntries()

Order Form

Upvotes: 0

Views: 618

Answers (2)

Prem
Prem

Reputation: 46

Your expressions (int(x) for x in results) and (int(quantity)*(int(x) for x in results)) evaluates to a generator objects as you have enclosed it within parenthesis. See here

>>> (quant for quant in range(5))
<generator object <genexpr> at 0x1021e1780>
>>>

So remove your parenthesises and break it down into smaller chunks. Also there should only be one price returned for a single productId, so you're looping multiple times and don't have to use tuples unless you're input itself is multiple product ids and you're multiplying each price and product and computing order total. A simplified version might look like this

        cursor.execute('SELECT Price FROM Products WHERE ProductID = ?', (productid,))
        result = cursor.fetchone()
        
        if result:
            price = quantity * float(result[0])
            cursor.execute('INSERT INTO Orders(OrderTotal) VALUES (?)', (price,))
            self.ClearEntries()
        else:
            tkinter.messagebox.showerror("Error", "No product was found with this ProductID, please try again.")
            self.ClearEntries()

Upvotes: 1

Serge Ballesta
Serge Ballesta

Reputation: 148900

You are mixing an external loop over the results iterable, inserting 1 value at a time, and an execution of the same INSERT query over an iterable of values.

First way, external loop (I have removed some useless parentheses):

            for i in results:
                price = int(quantity)*int(i)
                addprice = ('''INSERT INTO Orders(OrderTotal)
                VALUES (?)''')
                cursor.execute(addprice, [price])

Second way, using executemany:

            prices = ([int(quantity)*int(x)] for x in results)  # prices is an iterable of lists
            addprice = ('''INSERT INTO Orders(OrderTotal)
            VALUES (?)''')
            cursor.executemany(addprice, prices)

But if you are learning SQL and database accesses, you could directly use joined queries:

addprice = '''INSERT INTO Orders(OrderTotal)
    SELECT :quantity * Price FROM Products
    WHERE Products.ProductID = :productid'''
cursor.execute(addprice, {'quantity': quantity, 'productid': productid})

And as your code contains an (unused) orderid variable, I think that what you want is:

addprice = '''INSERT INTO Orders(OrderID, OrderTotal)
    SELECT :orderid, :quantity * Price FROM Products
    WHERE Products.ProductID = :productid'''
cursor.execute(addprice, {'quantity': quantity, 'productid': productid,
                          'orderid': orderid})

Upvotes: 2

Related Questions