Python Forum
Database Submit Entry Syntax Error
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database Submit Entry Syntax Error
#21
Well I've had a look and read through the document you referred me to, after reading through it all either its outdated as close() doesn't seem to work but quit() does, on top of that after adding the following adaptation to my code all it does is print what I've inserted but still refuses to input the data into the table I created, when I remove the quotations on the create table function, it tells me that 'table expenses' already exists however removing my adaptation (Line 4+5) the error that comes back is 'the table does not exist' so I'm just muddled..

Here's my adaptation of the code:
def submit():
    payload = (name_text.get(), Monthly_Income_text.get(), Monthly_Budget.get(), Monthly_Expenses.get())
    c.execute("INSERT INTO expenses VALUES (?, ?, ?, ?)", payload)
    for row in c.execute('SELECT * FROM expenses'):
        print(row)
All I need is to figure out why this doesn't insert the information into the preexisting table, once its inserted I'll make the larger box use pandas to view what has been inserted and that'll be that but its so difficult to understand when I read through it and nothing works, irritating and demoralising which is why I love and hate coding at the same time
Reply
#22
really, I this is the last answer I post in this thread, sorry...
why do you remove the line conn.commit() which I explicitly stated that I have indented?

def submit():
    payload = (name_text.get(), Monthly_Income_text.get(), Monthly_Budget.get(), Monthly_Expenses.get())
    c.execute("INSERT INTO expenses VALUES (?, ?, ?, ?)", payload)
    c.commit()
    for row in c.execute('SELECT * FROM expenses'):
        print(row)
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#23
Well the reason I removed it is because it says:
Error:
File "C:/Users/jumbu/Desktop/python/test.py", line 24, in submit c.commit() AttributeError: 'sqlite3.Cursor' object has no attribute 'commit'
This error occurs after inputting data so if you were to ask, I'm not just clicking Submit without filling in the boxes
Reply
#24
sorry, conn.commit() - I was so frustrated...
(Jan-22-2020, 05:41 PM)buran Wrote:
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()
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#25
Like, I understand your frustration like I get I'm asking a lot of questions and I'm trying not to make stupid wasteful ones hence why there's usually a 10 minute delay on my responses, not only that but I'm also noting down what the changes do in a separate notepad document so I can refer back to the notes whenever I need them so you're doing me a massive favor by helping me out.

After inserting conn.commit(), it works however now the database has locked itself for whatever reason looking into it now unless you know what it is

Ah okay, so apparently if I have the DB Browser open whilst inputting data it just locks itself, strange but it works now; I'd ask more questions but I think you're pretty burnt out from this so thanks for your help its been greatly appreciated.
Reply
#26
buran Wrote:@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.
Actually @buran your response shows that you are the one that is clueless passing a parameterized list (aka sting) inserting variables (that could be strings) is exactly the same as concatenating a string within a string and thus creates the same issue -- further the documentation you quote does not take into the fact that would only be the case if the data had not already been scrubbed prior to being passed to the database class -- which is how it ought to be handled -- meaning that by the time I get to the actual database where I am building the data I am guaranteed that the data has nothing in it that would be dangerous including things that would simply cause a query to choke without it being an actual injection attack. Further due to the lameness of this response I am going to guess I have far more years of experience under my belt in a professional capacity than you do.

Still again since you seem to be rather slow on the pick up -- if you handle the data properly the threat of injection has already been dealt with long before you get to the where you create the database query because it has already been completely validated and scrubbed but if you are concerned that this is not the case then you add a validator/scrubber -- which by the way you cannot do using the methodology you are using but I can -- by simply passing the values into a validator/scrubber prior to concatenating them to the string query that is being built -- but then again I have already handled this long before I get to my actual database class as that kind of manipulation takes place in the controller

@Melford frustration is not okay if your object is to help someone as this frustration is most often due to the fact that the tutor is not communicating the information clearly as is the case with buran -- not only that he is instructing you improperly on how you ought to be doing this. Have you ever heard of MVC?

Also big note -- the only bad or stupid question is the unasked one if you are truly wanting to learn -- so always ask if you find your tutor is inadequate then find another one that can explain it to you -- but always be leery as not everyone knows how to code properly and they propagate a lot of misinformation because they have a false sense of superiority about themselves

If you would care to dial back a bit and look at my example you will find that it covers all the bits and pieces you need -- further it implements debugging prints to help you ascertain perhaps where something is being done not as you expect it would be done.
Reply
#27
(Jan-22-2020, 09:28 PM)Denni Wrote: Actually @buran your response shows that you are the one that is clueless passing a parameterized list (aka sting) inserting variables (that could be strings) is exactly the same as concatenating a string within a string and thus creates the same issue
import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()

# Create table
c.executescript('''DROP TABLE IF EXISTS bank; CREATE TABLE IF NOT EXISTS bank (name TEXT, amount INTEGER)''')

# Insert a row of data
c.execute("INSERT INTO bank VALUES (?, ?)" , ('JOHN DOE', 1000))
c.execute("INSERT INTO bank VALUES (?, ?)" , ('JANE DOE', '2000'))
c.execute("INSERT INTO bank VALUES (?, ?)" , ('Denni', '3000'))
# Save (commit) the changes
conn.commit()

user_input = '3' # normal user input, string
sql_injection = '3 or 1=1' # sql injection

print('Using parametrized statement')
print('\nnormal user input')
c.execute("SELECT * FROM bank WHERE ROWID = ?", (user_input,))
for row in c:
    print(row)

print('\nsql injection')
c.execute("SELECT * FROM bank WHERE ROWID = ?", (sql_injection,))
for row in c:
    print(row)

print('\n====================\nUsing concatenated statement')
print('\nnormal user input')
s = 'SELECT * FROM bank WHERE ROWID = ' + user_input
c.execute(s)
for row in c:
    print(row)

print('\nsql injection')
s = 'SELECT * FROM bank WHERE ROWID = ' + sql_injection
c.execute(s)
for row in c:
    print(row)

conn.close()
Output:
Using parametrized statement normal user input ('Denni', 3000) sql injection ==================== Using concatenated statement normal user input ('Denni', 3000) sql injection ('JOHN DOE', 1000) ('JANE DOE', 2000) ('Denni', 3000)
Do you still think it's the same?
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#28
@buran again you fail to understand the basics of quality programming -- or you are blind -- as I stated you do not have to worry about injection with proper coding -- why? -- because with quality proper coding it becomes a non-issue as you have already (should there be a situation where it is even applicable) validated and scrubbed that incoming data because what you do not want is to have your database engine dealing with it and your casual approach is also circum-navigable if I was a serious hacker which is why you NEED to validate and scrub untrusted incoming data long before you send it to your database -- second and probably THE most important item here -- they are working with a local database WHICH MEANS -- the user has direct access to that database thus absolutely no need to worry about injection -- creating code to fight something that you are not needing to fight is over complicating your code -- deal with the issue at hand not some made dumb stuff that will never even come into being. So okay you may have proved your non-issue point but all it really proves is that you do not understand what proper quality programming ought to look like which was something I was trying to hint at earlier -- I am sorry if I bruised your ego but I feel its better that than mis-guiding a young programmer as you have tried to do -- so chew on that one for a while.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [Tkinter] Error verify data in database TomasSanchexx 2 860 Aug-11-2023, 12:37 PM
Last Post: TomasSanchexx
  [PyGUI] Invalid command error with Entry object eliqm 8 2,027 May-18-2023, 10:14 PM
Last Post: eliqm
  [Tkinter] Trying to add data into a shelf from a submit button TWB 8 1,808 Jan-06-2023, 11:30 PM
Last Post: TWB
  Usborne Coding for Beginners using Python 'Dodge the Bombs' Syntax Error CMSheWolfe 5 3,827 Jun-18-2020, 09:41 PM
Last Post: deanhystad
  Transfer Toplevel window entry to root window entry with TKinter HBH 0 4,420 Jan-23-2020, 09:00 PM
Last Post: HBH
  Problem with Submit button Tkinter Reldaing 2 3,603 Jan-05-2020, 01:58 AM
Last Post: balenaucigasa
  [Tkinter] how to get the entry information using Entry.get() ? SamyPyth 2 3,451 Mar-18-2019, 05:36 PM
Last Post: woooee

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020