sqlite - Error with parameters in Python with SQLite3 TypeError: unsupported operand type(s) for +: 'sqlite3.Cursor' and 'str'

Question

I am a beginner in coding and this new community.I need your help to understand why it is not possible to get this solution. I would like to get a number from an entry and then sum up with the last record of the same item, so I can aggregate it.

Here is my code:

def add_stock(self):
    time = datetime.now().strftime("%B %d, %Y")
    hour = datetime.now().strftime("%I:%M%p")
    query = 'SELECT totalstock FROM stocks WHERE name = ? AND MovementID = ( SELECT max( MovementID ) FROM stocks)'
    parameters = (self.name.get(),)
    lastrecord = self.run_query(query, parameters)
    total = lastrecord + self.quantity.get()
    if self.validation():
        query = 'INSERT INTO stocks VALUES(NULL, ?, ?, ?, ?, ?)'
        parameters = (self.name.get(), self.quantity.get(), total, time, hour)
        self.run_query(query, parameters)
        self.message['text'] = 'Movement {} added succesfully'.format(self.name.get())
        self.name.delete(0, END)
        self.quantity.delete(0, END)
    else:
        self.message['text'] = 'Name and Quantity required'
    self.get_product()

But there is something wrong with it, can anybody help me out?

This is the entire code:

from tkinter import *
from tkinter import ttk
from datetime import datetime
import sqlite3

class Main:

    db_name = 'materiales.db'
    
    def __init__(self,window):
        self.wind = window
        self.wind.title('Stock App')

        #create frame
        frame = LabelFrame(self.wind, text = 'Add stock')
        frame.grid(row = 0, column = 0, columnspan = 3, pady = 20)

        # Name Input
        Label(frame, text = 'Name: ').grid(row = 1, column = 0)
        self.name = Entry(frame)
        self.name.focus()
        self.name.grid(row = 1, column = 1)

        # Quantity Input
        Label(frame, text = 'Quantity: ').grid(row = 2, column = 0)
        self.quantity = Entry(frame)
        self.quantity.grid(row = 2, column = 1)

        # Button Add Stock
        ttk.Button(frame, text = 'Add Stock', command = self.add_stock).grid(row = 3, columnspan = 2, sticky = W + E)

        #Log Message
        self.message = Label(text = '', fg = 'red')
        self.message.grid(row = 3, column = 0, columnspan = 2, sticky = W + E)
        
        # Table
        self.tree = ttk.Treeview(height = 10, columns = 2)
        self.tree.grid(row = 4, column = 0, columnspan = 2)
        self.tree.heading('#0', text = 'Name', anchor = CENTER)
        self.tree.heading('#1', text = 'Stock', anchor = CENTER)

        #Buttons
        ttk.Button(text = 'DELETE', command = self.delete_product).grid(row = 5, column = 0, sticky = W + E) 
        ttk.Button(text = 'MODIFY', command = self.modify_product).grid(row = 5, column = 1, sticky = W + E)       
        self.get_product()


    def run_query(self, query, parameters = ()):
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.cursor()
            result = cursor.execute(query, parameters)
            conn.commit()
        return result

    def get_product(self):
        records = self.tree.get_children()
        for element in records:
            self.tree.delete(element)
        query = 'SELECT * FROM product ORDER BY name DESC'
        db_rows = self.run_query(query)
        for row in db_rows:
            self.tree.insert('', 0, text = row[1], values = row[2])

    def validation(self):
        return len(self.name.get()) != 0 and len(self.quantity.get()) !=0

    def add_stock(self):
        time = datetime.now().strftime("%B %d, %Y")
        hour = datetime.now().strftime("%I:%M%p")
        query = 'SELECT totalstock FROM stocks WHERE name = ? AND MovementID = ( SELECT max( MovementID ) FROM stocks)'
        parameters = (self.name.get(),)
        lastrecord = self.run_query(query, parameters)
        total = lastrecord + self.quantity.get()
        if self.validation():
            query = 'INSERT INTO stocks VALUES(NULL, ?, ?, ?, ?, ?)'
            parameters = (self.name.get(), self.quantity.get(), total, time, hour)
            self.run_query(query, parameters)
            self.message['text'] = 'Movement {} added succesfully'.format(self.name.get())
            self.name.delete(0, END)
            self.quantity.delete(0, END)
        else:
            self.message['text'] = 'Name and Quantity required'
        self.get_product()

    def delete_product(self):
        try:
            self.tree.item(self.tree.selection())['text'][0]
        except IndexError as e:
            self.message['text'] = 'Please Select a Record'
            return
        name = self.tree.item(self.tree.selection())['text']
        query = 'DELETE FROM product WHERE name = ?'
        self.run_query(query,(name,))
        self.message['text'] = 'Product {} deleted succesfully'.format(name)
        self.get_product()

    def modify_product(self):
        try:
            self.tree.item(self.tree.selection())['text'][0]
        except IndexError as e:
            self.message['text'] = 'Please Select a Record'
            return
        name = self.tree.item(self.tree.selection())['text']
        old_price = self.tree.item(self.tree.selection())['values'][0]
        self.edit_wind = Toplevel()
        self.edit_wind.title = 'Edit Product'

        #Old Name
        Label(self.edit_wind, text = 'Old Name: ').grid(row = 0, column = 1)
        Entry(self.edit_wind, textvariable = StringVar(self.edit_wind, value = name), state = 'readonly').grid(row = 0, column = 2)

        #New Name
        Label(self.edit_wind, text = 'New Name: ').grid(row = 1, column = 1)
        new_name = Entry(self.edit_wind)
        new_name.grid(row = 1, column = 2)

        #Old Price
        Label(self.edit_wind, text = 'Old Price: ').grid(row = 2, column = 1)
        Entry(self.edit_wind, textvariable = StringVar(self.edit_wind, value = old_price), state = 'readonly').grid(row = 2, column = 2)

        #New Price
        Label(self.edit_wind, text = 'New Price: ').grid(row = 3, column = 1)
        new_price = Entry(self.edit_wind)
        new_price.grid(row = 3, column = 2)

        Button(self.edit_wind, text = 'Update', command = lambda: self.edit_records(new_name.get(), name, new_price.get(), old_price)).grid(row = 4, column = 2, sticky = W)


    def edit_records(self, new_name, name, new_price, old_price):
        query = 'UPDATE product SET name = ?, precio = ? WHERE  name = ? AND precio = ?'
        parameters = (new_name, new_price, name, old_price)
        self.run_query(query, parameters)
        self.edit_wind.destroy()
        self.message['text'] = 'Record {} updated successfully'.format(name)
        self.get_product()

        
if __name__ == '__main__':
    window = Tk()
    application = Main(window)
    window.mainloop()

Thanks in advance and kind regards.

Answer 1

The exception is raised on line 72 of your code. The code attempts to add a string object to a sqlite.Cursor object, which fails because these types are incompatible for that operation.

Instead you need to extract the data held in the returned Cursor object which can be done like this:

total = lastrecord.fetchone()[0]

fetchone() returns the first row of the data which is of type tuple. The first item in the tuple is the queried value, which is assumed to be an integer in this case. You can't add the string returned by self.quantity.get(), so you need to convert that to an integer (assuming that it is an integer.. it could be another numeric type) to calculate the new total:

total += int(self.quantity.get())