Error in SQLite ‘all VALUES must have the same number of terms’

Glad to be a part of this platform among you, highly experienced programmers, Nowadays I am working on a POS system project in python, using tkinter for frontend & Sqlite as the database. While putting data into it, I am facing this issue. ”’

def proceed_raise_invoice():
    execute_query(connection, create_invoices)

ttk.Label(tab1, text="Raise Invoice", font=('', 20)).grid(column=0, row=0)
ttk.Label(tab1, text="General Details:", font=('', 15)).grid(column=0, row=4)
invoice_number = tk.StringVar()
ttk.Label(tab1, text="Invoice Number:", font=('', 12)).grid(column=0, row=5)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=invoice_number).grid(column=3, row=5)
invoice = invoice_number.get()
invoice_date = date.today()
ttk.Label(tab1, text="Date:", font=('', 12)).grid(column=0, row=6)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=invoice_date).grid(column=3, row=6)
ttk.Label(tab1, text="Details of receiver/billed to:", font=('', 15)).grid(column=0, row=7)
name = ""
ttk.Label(tab1, text="Name:", font=('', 12)).grid(column=0, row=8)
ttk.Combobox(tab1, width=30, textvariable=name).grid(column=3, row=8)
customer_gstin = "07"
ttk.Label(tab1, text="GSTIN:", font=('', 12)).grid(column=0, row=9)
ttk.Label(tab1, text="", font=('', 12), textvariable=customer_gstin).grid(column=3, row=9)
ttk.Label(tab1, text="Purchasing:", font=('', 15)).grid(column=0, row=10)
product = ""
ttk.Label(tab1, text="Name of product:", font=('', 12)).grid(column=0, row=11)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=product).grid(column=3, row=11)
hsn = ""
ttk.Label(tab1, text="HSN ACS:", font=('', 12)).grid(column=0, row=12)
ttk.Combobox(tab1, width=30, textvariable=hsn).grid(column=3, row=12)
uom = ""
ttk.Label(tab1, text="Units of Measure:", font=('', 12)).grid(column=0, row=13)
ttk.Entry(tab1, width=33, textvariable=uom).grid(column=3, row=13)
quantity = int(1)
ttk.Label(tab1, text="Quantity:", font=('', 12)).grid(column=0, row=14)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=quantity).grid(column=3, row=14)
rate = int(0)
ttk.Label(tab1, text="Rate:", font=('', 12)).grid(column=0, row=15)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=rate).grid(column=3, row=15)
supplier = ""
ttk.Label(tab1, text="Supplier:", textvariable=supplier, font=('', 15)).grid(column=0, row=18)
ttk.Combobox(tab1, width=20, font=('', 12)).grid(column=3, row=18)
ttk.Button(tab1, text='Proceed', command=proceed_raise_invoice).grid(column=3, row=19)

amount = quantity * rate

list = ['invoice_number', 'invoice_date', 'name', 'customer_gstin', 'product', 'hsn', 'uom', 'quantity', 'rate', 'amount', 'supplier']

create_invoices_table = """
CREATE TABLE IF NOT EXISTS invoices (
    invoice_no TEXT PRIMARY KEY NOT NULL,
    date TEXT NOT NULL,
    customer_name TEXT NOT NULL ,
    gstin TEXT NOT NULL,
    product TEXT NOT NULL,
    hsn INTEGER NOT NULL,
    uom TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    rate INTEGER NOT NULL,
    amount INTEGER NOT NULL,
    supplier TEXT NOT NULL
);
"""
execute_query(connection, create_invoices_table)

create_invoices = """
INSERT INTO
    invoices (invoice_no, date, customer_name, gstin, product, hsn, uom, quantity, rate, amount, supplier)
VALUES
    (?,?),('list')
"""

”’

What I am trying to do here is retrieving data from the tkinter widget and then uploading it to the database. I would be grateful if you could point out where am going wrong, also if you could tell me if I am using the correct method to retrieve data from the tkinter widget. Thanks Aneesh Mehta

Answer

As far as your code is concerned, ur code has some problems. Tkinter only sends values through buttons.

list is a keyword in python so rename it to list1.

No. of ? is also not matching. You should also not use .get() function of StringVar() within the same frame and instead use it in the proceed_raise_invoice() method.

Inside below function, I have used .get() method of StringVar to get the values stored in StringVar (values typed by users)

Always use get() method inside the function that you have called through tkinter button. It will get the values of StringVar after button is clicked. And dont use .get() of StringVar globally.

you can use sqlite as this:

import sqlite3 as db

conn = db.connect('dbname.db')
cur = conn.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS invoices (
    invoice_no TEXT PRIMARY KEY NOT NULL,
    date TEXT NOT NULL,
    customer_name TEXT NOT NULL ,
    gstin TEXT NOT NULL,
    product TEXT NOT NULL,
    hsn INTEGER NOT NULL,
    uom TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    rate INTEGER NOT NULL,
    amount INTEGER NOT NULL,
    supplier TEXT NOT NULL
)
""")
cur.close()
conn.commit()
conn.close()

invoice_number = tk.StringVar()
customer_gstin = tk.StringVar("07")
today = date.today()
today = str(today)
invoice_date = tk.StringVar(today)
name = tk.StringVar()
product = tk.StringVar()
quantity = tk.IntVar(1)
rate = tk.IntVar(0)
hsn = tk.StringVar()
uom = tk.StringVar()
supplier = tk.StringVar()


def proceed_raise_invoice():
    conn = db.connect('dbname.db')
    cur = conn.cursor()

    amount = quantity.get() * rate.get()

    list1 = (invoice_number.get(), invoice_date.get(), name.get(), customer_gstin.get(), product.get(), hsn.get(), uom.get(), quantity.get(), rate.get(), amount, supplier.get())

    cur.execute("""
    INSERT INTO
    invoices (invoice_no, date, customer_name, gstin, product, hsn, uom, quantity, rate, amount, supplier)
    VALUES (?,?,?,?,?,?,?,?,?,?,?)""",list1)


    cur.close()
    conn.commit()
    conn.close()


ttk.Label(tab1, text="Raise Invoice", font=('', 20)).grid(column=0, row=0)
ttk.Label(tab1, text="General Details:", font=('', 15)).grid(column=0, row=4)

ttk.Label(tab1, text="Invoice Number:", font=('', 12)).grid(column=0, row=5)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=invoice_number).grid(column=3, row=5)

ttk.Label(tab1, text="Date:", font=('', 12)).grid(column=0, row=6)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=invoice_date).grid(column=3, row=6)
ttk.Label(tab1, text="Details of receiver/billed to:", font=('', 15)).grid(column=0, row=7)

ttk.Label(tab1, text="Name:", font=('', 12)).grid(column=0, row=8)
ttk.Combobox(tab1, width=30, textvariable=name).grid(column=3, row=8)

ttk.Label(tab1, text="GSTIN:", font=('', 12)).grid(column=0, row=9)
ttk.Label(tab1, text="", font=('', 12), textvariable=customer_gstin).grid(column=3, row=9)
ttk.Label(tab1, text="Purchasing:", font=('', 15)).grid(column=0, row=10)

ttk.Label(tab1, text="Name of product:", font=('', 12)).grid(column=0, row=11)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=product).grid(column=3, row=11)

ttk.Label(tab1, text="HSN ACS:", font=('', 12)).grid(column=0, row=12)
ttk.Combobox(tab1, width=30, textvariable=hsn).grid(column=3, row=12)

ttk.Label(tab1, text="Units of Measure:", font=('', 12)).grid(column=0, row=13)
ttk.Entry(tab1, width=33, textvariable=uom).grid(column=3, row=13)

ttk.Label(tab1, text="Quantity:", font=('', 12)).grid(column=0, row=14)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=quantity).grid(column=3, row=14)

ttk.Label(tab1, text="Rate:", font=('', 12)).grid(column=0, row=15)
ttk.Entry(tab1, width=22, font=('', 12), textvariable=rate).grid(column=3, row=15)

ttk.Label(tab1, text="Supplier:", textvariable=supplier, font=('', 15)).grid(column=0, row=18)
ttk.Combobox(tab1, width=20, font=('', 12)).grid(column=3, row=18)
ttk.Button(tab1, text='Proceed', command=proceed_raise_invoice).grid(column=3, row=19)