operational error (syntax) in a sqlite3 database using a tkinter gui loop

Question

currently working on a database using SQLite 3 and I have received the following error. I didn't have the error before I added all of the resultsfromsearch:

line 96, in addrecord
    cursor.execute(query + fields)
sqlite3.OperationalError: near ",": syntax error

any help would be appreciated:

from tkinter import *
import sqlite3


class databasewindow:

    def __init__(self,master,resultsfromsearch):
        self.resultsfromsearch = resultsfromsearch
        self.master = master
        master.title("Students")



        self.cardnumberlbl = Label(master,text="Card Number:- ")
        self.firstnamelbl = Label(master,text="firstname: - ")
        self.surnamelbl = Label(master,text="Surname: - ")
        self.currentgradelbl = Label(master,text="Current Grade: -")
        self.targetgradelbl = Label(master,text="Target Grade: -")


        self.cardnumberentry = Entry(master)
        self.firstnameentry = Entry(master)
        self.surnameentry = Entry(master)
        self.currentgradeentry = Entry(master)
        self.targetgradeentry = Entry(master)




        self.cardnumberlbl.grid(row=1, column=0, sticky=W)
        self.cardnumberentry.grid(row=1, column=1, sticky=W+E)

        self.firstnamelbl.grid(row=2, column=0, sticky=W)
        self.firstnameentry.grid(row=2, column=1, sticky=W+E)

        self.surnamelbl.grid(row=3, column=0, sticky=W)
        self.surnameentry.grid(row=3, column=1, sticky=W+E)

        self.currentgradelbl.grid(row=4, column=0, sticky=W)
        self.currentgradeentry.grid(row=4, column=1, sticky=W+E)

        self.targetgradelbl.grid(row=5, column=0, sticky=W)
        self.targetgradeentry.grid(row=5, column=1, sticky=W+E)



        self.submit_button = Button(master, text="add record", command=self.addrecord)
        self.submit_button.grid(row=9,column=0,sticky=W+E)


        self.firstrecord()

    def firstrecord(self):
        global position

        position = 0

        for customer in self.resultsfromsearch:

            if position >=1:
                position = 0
                break;
            else:
                self.cardnumberentry.delete(0, 'end')
                self.firstnameentry.delete(0, 'end')
                self.surnameentry.delete(0, 'end')
                self.currentgradeentry.delete(0, 'end')
                self.targetgradeentry.delete(0, 'end')


                self.cardnumberentry.insert(END, student[0])
                self.firstnameentry.insert(END, student[1])
                self.surnameentry.insert(END, student[2])
                self.currentgradeentry.insert(END, student[3])
                self.targetgradeentry.insert(END, student[4])

                position = position + 1



    def addrecord(self):

        with sqlite3.connect("student.db") as db:
            cursor = db.cursor()
            cardnumber = self.cardnumberentry.get()
            firstname = self.firstnameentry.get()
            surname = self.surnameentry.get()
            currentgrade = self.currentgradeentry.get()
            targetgrade = self.targetgradeentry.get()


        query = ('INSERT INTO customers( cardnumber, firstname, surname,currentgrade, targetgrade) VALUES' )
        fields = ("("'+ cardnumber +'","' + firstname +'","'+ surname +'","'+ currentgrade'","'+ targetgrade'");")


        cursor.execute(query + fields)

        db.commit()

        query = (' SELECT * FROM students')
        cursor.execute(query)
        self.resultsfromsearch = (cursor.fetchall())

        self.firstrecord()

sorry I had to add a lot of thanks or I couldn't post the question as there is a lot of code. — Jack Burgess
– Jack Burgess, Commented Feb 27, 2018 at 12:49
That's not the correct way to write sql requests in code docs.python.org/2/library/sqlite3.html — polku
– polku, Commented Feb 27, 2018 at 14:03

Parfait · Accepted Answer · 2018-02-27 15:20:31Z

3

Consider parameterization using the second params argument of cursor.execute() and avoid any string concatenation or interpolation of data values.

with sqlite3.connect("student.db") as db:
    cursor = db.cursor()
    cardnumber = self.cardnumberentry.get()
    firstname = self.firstnameentry.get()
    surname = self.surnameentry.get()
    currentgrade = self.currentgradeentry.get()
    targetgrade = self.targetgradeentry.get()

    # PREPARED STATEMENT WITH ? PLACEHOLDERS
    query = 'INSERT INTO customers(cardnumber, firstname, surname, currentgrade, targetgrade)' + \
            ' VALUES(?, ?, ?, ?, ?);'

    # DATA VALUES IN TUPLE
    fields = (cardnumber, firstname, surname, currentgrade, targetgrade)

    cursor.execute(query, fields)
    db.commit()

    ...

answered Feb 27, 2018 at 15:20

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jayvee · Accepted Answer · 2018-02-27 14:45:57Z

1

It seems that the error is because query+fields is resulting in an invalid sql statement:

>>> query = ('INSERT INTO customers( cardnumber, firstname, surname,currentgrade, targetgrade) VALUES' )
>>> fields = ("("'+ cardnumber +'","' + firstname +'","'+ surname +'","'+ currentgrade'","'+ targetgrade'");")
>>> query+fields
'INSERT INTO customers( cardnumber, firstname, surname,currentgrade, targetgrade) VALUES(+ cardnumber +, + firstname +,+ surname +,+ currentgrade,+ targetgrade);'
>>>

answered Feb 27, 2018 at 14:45

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Collectives™ on Stack Overflow

operational error (syntax) in a sqlite3 database using a tkinter gui loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related