Database Submit Entry Syntax Error

[Melford]

Sorry I'm not really sure how to implement that? would you mean replacing
val1 = l1.get()

with

StringVars1 = l1.get()?

[buran]

Sorry, I was on my phone and that's why I didn't elaborate
I mean

payload = (name_text.get(), Monthly_Income_text.get(), Monthly_Budget.get(), Monthly_Expenses.get())

[buran]

@buran that is true we were supposed to be discussing that code snippet and again this piece of code you give has exactly the same issue with injection attacks as the one I gave but yours does not allow for easy validation of the SQL statement prior to using it where mine does.

@Denni, obviously you don't know what you are talking about. I don't care what you do in your code base, but don't confuse and mislead other users.

The documentation is pretty clear

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see https://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

RED is what you are doing - string concatenation
GREEN is what I show - parametrized query with placeholders.

[Melford]

Okay so I've put the code together, I thought maybe it'd be the submit button which is causing the error but apparently its the indent however it doesn't like being alone?

Error:
  File "C:/Users/jumbu/Desktop/python", line 20
    c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", (l1.get(), l2.get(), l3.get(), l4.get()))
    ^
IndentationError: unexpected indent

def submit():
    c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", (l1.get(), l2.get(), l3.get(), l4.get()))
    payload = (name_text.get(), Monthly_Income_text.get(), Monthly_Budget.get(), Monthly_Expenses.get())
    c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", payload)

Maybe I leave payload indented but c.execute elsewhere?

[buran]

first of all - remove line 2. you don't need it

the indentation looks right in what you have posted. check your actual code carefully. I don't know what your whole code looks like now.

[Melford]

Okay so now I'm getting this error:

Error:
Traceback (most recent call last):
  File "C:\Users\jumbu\AppData\Local\Programs\Python\Python37-32\lib\tkinter\__init__.py", line 1705, in __call__
    return self.func(*args)
  File "C:/Users/jumbu/Desktop/python/test.py", line 22, in submit
    c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", (l1.get(), l2.get(), l3.get(), l4.get()))
AttributeError: 'Label' object has no attribute 'get'

Here's my full code although I'm not too sure if the problem is outside of that chunk I sent beforehand

from tkinter import *
import sqlite3

# Database Creation & Filename
conn = sqlite3.connect('Expense_Data.db')

# Create Cursor connecting to database
c = conn.cursor()

# Database Table Creation
'''
c.execute("""CREATE TABLE expenses   (
    name text,
    monthly_income integer,
    monthly_budget integer,
    monthly_cost_in_expenses integer
)""")
'''


def submit():
    c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", (l1.get(), l2.get(), l3.get(), l4.get()))
    c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)")


# Finalise || Commit Changes
conn.commit()

# Create Window Object
window = Tk()

# Define table contents (each row/column)
l1 = Label(window, text="Name")
l1.grid(row=0, column=0)

l2 = Label(window, text="Monthly Income")
l2.grid(row=1, column=0)

l3 = Label(window, text="Monthly Budget")
l3.grid(row=2, column=0)

l4 = Label(window, text="Monthly Expenses")
l4.grid(row=3, column=0)

# Define Entries
name_text = StringVar()
e1 = Entry(window, textvariable=name_text)
e1.grid(row=0, column=1)

Monthly_Income_text = StringVar()
e2 = Entry(window, textvariable=Monthly_Income_text)
e2.grid(row=1, column=1)

Monthly_Budget = StringVar()
e3 = Entry(window, textvariable=Monthly_Budget)
e3.grid(row=2, column=1)

Monthly_Expenses = StringVar()
e4 = Entry(window, textvariable=Monthly_Expenses)
e4.grid(row=3, column=1)

# Define ListBox
list1 = Listbox(window, height=6, width=35)  # check this
list1.grid(row=5, column=0, columnspan=2)

# Attach scrollbar to the list
sb1 = Scrollbar(window)
sb1.grid(row=2, column=2, rowspan=6)

list1.configure(yscrollcommand=sb1.set)
sb1.configure(command=list1.yview)

# Define buttons
b1 = Button(window, text="View All", width=12)
b1.grid(row=2, column=3)

b2 = Button(window, text="Search Entry", width=12)
b2.grid(row=3, column=3)

b3 = Button(window, text="Add Entry", width=12)
b3.grid(row=4, column=3)

b4 = Button(window, text="Update Selected", width=12, command=submit)
b4.grid(row=5, column=3)

b5 = Button(window, text="Delete Selected", width=12)
b5.grid(row=6, column=3)

b6 = Button(window, text="Close", width=12)
b6.grid(row=7, column=3)
submit_btn = Button(text="Add Record to Database", command=submit)
submit_btn.grid(row=6, column=0, columnspan=2,)
window.mainloop() 

[buran]

I give up (not tested):

from tkinter import *
import sqlite3
 
# Database Creation & Filename
conn = sqlite3.connect('Expense_Data.db')
 
# Create Cursor connecting to database
c = conn.cursor()
 
# Database Table Creation
'''
c.execute("""CREATE TABLE expenses   (
    name text,
    monthly_income integer,
    monthly_budget integer,
    monthly_cost_in_expenses integer
)""")
'''
 
 
def submit():
    payload = (name_text.get(), Monthly_Income_text.get(), Monthly_Budget.get(), Monthly_Expenses.get())
    c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", payload)
    # Finalise || Commit Changes
    conn.commit()
 
# Create Window Object
window = Tk()
 
# Define table contents (each row/column)
l1 = Label(window, text="Name")
l1.grid(row=0, column=0)
 
l2 = Label(window, text="Monthly Income")
l2.grid(row=1, column=0)
 
l3 = Label(window, text="Monthly Budget")
l3.grid(row=2, column=0)
 
l4 = Label(window, text="Monthly Expenses")
l4.grid(row=3, column=0)
 
# Define Entries
name_text = StringVar()
e1 = Entry(window, textvariable=name_text)
e1.grid(row=0, column=1)
 
Monthly_Income_text = StringVar()
e2 = Entry(window, textvariable=Monthly_Income_text)
e2.grid(row=1, column=1)
 
Monthly_Budget = StringVar()
e3 = Entry(window, textvariable=Monthly_Budget)
e3.grid(row=2, column=1)
 
Monthly_Expenses = StringVar()
e4 = Entry(window, textvariable=Monthly_Expenses)
e4.grid(row=3, column=1)
 
# Define ListBox
list1 = Listbox(window, height=6, width=35)  # check this
list1.grid(row=5, column=0, columnspan=2)
 
# Attach scrollbar to the list
sb1 = Scrollbar(window)
sb1.grid(row=2, column=2, rowspan=6)
 
list1.configure(yscrollcommand=sb1.set)
sb1.configure(command=list1.yview)
 
# Define buttons
b1 = Button(window, text="View All", width=12)
b1.grid(row=2, column=3)
 
b2 = Button(window, text="Search Entry", width=12)
b2.grid(row=3, column=3)
 
b3 = Button(window, text="Add Entry", width=12)
b3.grid(row=4, column=3)
 
b4 = Button(window, text="Update Selected", width=12, command=submit)
b4.grid(row=5, column=3)
 
b5 = Button(window, text="Delete Selected", width=12)
b5.grid(row=6, column=3)
 
b6 = Button(window, text="Close", width=12)
b6.grid(row=7, column=3)
submit_btn = Button(text="Add Record to Database", command=submit)
submit_btn.grid(row=6, column=0, columnspan=2,)
window.mainloop()

Please, pay more attention what you are advised

[buran]

I edited the code to indent the commit line (line #25)

[Melford]

My apologies, I skipped the def submit line assuming you had meant the indentations; not too sure why thinking about it, I understand its probably annoying you because it annoys me out of all the subjects the fact it just doesn't click; fortunately next year I won't be touching any of this stuff, moving on I'm receiving this error:

Error:
Traceback (most recent call last):
  File "C:\Users\jumbu\AppData\Local\Programs\Python\Python37-32\lib\tkinter\__init__.py", line 1705, in __call__
    return self.func(*args)
  File "C:/Users/jumbu/Desktop/python/test.py", line 23, in submit
    c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", payload)
sqlite3.OperationalError: no such table: Expense_Data.db

So naturally what I did was rename Expense_Data.db to the name of the table in mySQLite DB Browser which is 'expenses' however after doing so, I click the 'Update Selected' button which is essentially the submit button and no errors occur which is great, however the table doesn't update with the data inputted as shown here, I think that's for a different board but if you're unsure don't worry about it, all I've wanted is for a submit button to insert the data made :(

[buran]

c.execute("INSERT INTO expenses VALUES (?, ?, ?, ?)", payload)

please, read the docs and examples for sqlite3 module
as well as the tkinter tutorial I linked in the other thread