Tkinter sqlite insert error

[Mattaqua]

Hello

Can’t find a way to get my code to work… probably simply homework project but can’t find it. My next assignment starts with this code working so…

I need to insert a query out of entry fields into a sqlite database file. This is my code:

I speak Dutch so that is why some code (names) is not in English.

With this code I get a syntax error on the “cursor.execute” line, but I also got other errors because I keep looking for the code to work so changing a lot. Making beginner’s mistakes probably…

from tkinter import *
import sqlite3

root = Tk()
root.geometry('500x300')
root.title('Registration Form')
voor_naam = StringVar()
tussen_voegsel = StringVar()
achter_naam = StringVar()
mo_biel = StringVar()

def database():
    voornaam = voor_naam.get
    tussenvoegsel = tussen_voegsel.get
    achternaam = achter_naam.get
    mobiel = mo_biel.get
    
    conn = sqlite3.connect("SQLite_Python.db")
    with conn:
        cursor = conn.cursor()
    cursor.execute(insert into persoon(voornaam, tussenvoegsel, achternaam, mobiel) values(voornaam, tussenvoegsel, achternaam, mobiel));
    conn.commit()

reg_label = Label(root,text="Registration Form", width =20, font=("bold", 20))
reg_label.place(x=90, y=53)

voor_naam_label = Label(root,text="Voornaam", width =20, font=("bold", 10))
reg_label.place(x=68, y=130)

voor_naam_entry = Entry(root,textvar=voor_naam)
voor_naam_entry.place(x=240, y=130)

tussenv_label = Label(root,text="Tussenvoegsel", width =20, font=("bold", 10))
tussenv_label.place(x=68, y=160)

tussenv_entry = Entry(root,textvar=tussen_voegsel)
tussenv_entry.place(x=240, y=160)

achter_naam_label = Label(root,text="Achternaam", width =20, font=("bold", 10))
achter_naam_label.place(x=68, y=190)

achter_naam_entry = Entry(root,textvar=achter_naam)
achter_naam_entry.place(x=240, y=190)

mo_biel_label = Label(root,text="Mobiel", width =20, font=("bold", 10))
mo_biel_label.place(x=68, y=220)

mo_biel_entry = Entry(root,textvar=mo_biel)
mo_biel_entry.place(x=240, y=220)

button = Button(root, text="Submit", width=20,bg='brown', fg='black', command=database).place(x=180, y=250)

root.mainloop()

I also tried this code instead of the “conn part” of the Def database, but that didn’t work either…

def insert_persoon:
    try:
      sqliteConnection =sqlite3.connect("SQLite_Python.db")
      insert_persoon_query = insert into persoon(voornaam, tussenvoegsel, achternaam, mobiel) values(voornaam, tussenvoegsel, achternaam, mobiel);
     
      cursor = sqliteConnection.cursor()
      print("Successfully connected to SQLite")
            
      cursor.execute(insert_persoon_query)
      sqliteConnection.commit()
      print('Persoon ingevoerd')
    
      cursor.close()
    
    except sqlite3.Error as error:
        print("Error while creating a sqlite table", error)

    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

Thank you so much in advance for looking at my code and answering my topic.

Mattaqua

[marcusparsons]

Hi @Mattaqua,

One thing I noticed is that in the assignment to the insert_persoon_query variable, the query itself isn’t in a string. It needs to be in a string because otherwise, that line is interpreted by Python as more commands in Python instead of a SQL statement.

Also, the values in your insert statement are unfortunately incorrectly placed. Once you put that query into a string, you can then format it as you need with the values of the variables you put into the script:

"insert into persoon (voornaam, tussenvoegsel, achternaam, mobiel) VALUES({},{},{},{})".format(voornaam, tussenvoegsel, achternaam, mobiel)

[menator01]

@Mattaqua

voornaam = voor_naam.get
tussenvoegsel = tussen_voegsel.get
achternaam = achter_naam.get
mobiel = mo_biel.get

In tkinter this is wrong when accessing StringVar()

should be

voornaam = voor_naam.get()
tussenvoegsel = tussen_voegsel.get()
achternaam = achter_naam.get()
mobiel = mo_biel.get()

You should start getting the in the habit of formatting queries in this manner as well
query = 'insert into table (arg1, arg2, arg3) values (?,?,?)'
cursor execute(query,  (arg1, arg2, arg3))

[Mattaqua]

Thanks!! Trying it on desktop in a few hours!

Getting back to you afterwards.

Mattaqua

[Mattaqua]

Thank you! Will try it out on my desktop in a few hours and get back to you afterwards.

[marshray48]

@Mattaqua

def insert_persoon:
    try:
      sqliteConnection =sqlite3.connect("SQLite_Python.db")
      insert_persoon_query = insert into persoon(voornaam, tussenvoegsel, achternaam, mobiel) values(voornaam, tussenvoegsel, achternaam, mobiel);
     
      cursor = sqliteConnection.cursor()
      print("Successfully connected to SQLite")
            
      cursor.execute(insert_persoon_query)
      sqliteConnection.commit()
      print('Persoon ingevoerd')
    
      cursor.close()
    
    except sqlite3.Error as error:
        print("Error while creating a sqlite table", error)

    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

1. Syntax Error in SQL Query: In the database function, you have a syntax error in your SQL query. The execute method expects a string containing a valid SQL query. You should enclose the query in double quotes. Also, you need to use placeholders for the values you want to insert. Here’s how to correct it:

cursor.execute("INSERT INTO persoon (voornaam, tussenvoegsel, achternaam, mobiel) VALUES (?, ?, ?, ?)",
               (voornaam, tussenvoegsel, achternaam, mobiel))

This code uses placeholders (?) to safely insert the values into the query.
2. Function Calls Missing Parentheses: In the lines where you retrieve values from Tkinter entry fields, you need to call the functions to get the values. For example:

voornaam = voor_naam.get()

You missed the parentheses after .get.
3. Function Definition Missing Parentheses: In the insert_persoon function definition, you need to include parentheses after the function name:

def insert_persoon():

The parentheses are required for function definitions in Python.

With these corrections, your code should work more effectively. Here’s an example of how the database function should look after the changes:

def database():
    voornaam = voor_naam.get()
    tussenvoegsel = tussen_voegsel.get()
    achternaam = achter_naam.get()
    mobiel = mo_biel.get()
    
    conn = sqlite3.connect("SQLite_Python.db")
    with conn:
        cursor = conn.cursor()
        cursor.execute("INSERT INTO persoon (voornaam, tussenvoegsel, achternaam, mobiel) VALUES (?, ?, ?, ?)",
                       (voornaam, tussenvoegsel, achternaam, mobiel))
        conn.commit()

Make similar changes in the insert_persoon function as well. These changes should help you insert data into the SQLite database correctly.

[Mattaqua]

Hello

Thank you everyone for answering. I know I put the sqlite3 connection lines too much in my code, but wanted to see whether I got the same error as before.

For some reason my code doesn’t create a new table “persoon” when it doesn’t exist, I guess because there’s still incorrect code. I don’t get any messages either (the print lines).

from tkinter import *
import sqlite3

root = Tk()
root.geometry('500x300')
root.title('Registration Form')
voor_naam = StringVar()
tussen_voegsel = StringVar()
achter_naam = StringVar()
mo_biel = StringVar()

def database():
    voornaam = voor_naam.get()
    tussenvoegsel = tussen_voegsel.get()
    achternaam = achter_naam.get()
    mobiel = mo_biel.get()
  
    conn = sqlite3.connect("SQLite_python.db")
    with conn:
        cursor = conn.cursor()
        cursor.execute("insert into persoon(voornaam,tussenvoegsel,achternaam,mobiel) values (?,?,?,?)",(voornaam,tussenvoegsel,achternaam,mobiel));
      
    conn.commit()
        
def insert_persoon():
    sqliteConnection =sqlite3.connect("SQLite_Python.db")
    insert_persoon_query =("insert into persoon (voornaam, tussenvoegsel, achternaam, mobiel) VALUES(?,?,?,?)",(voornaam, tussenvoegsel, achternaam, mobiel));
     
    cursor = sqliteConnection.cursor()
    print("Successfully connected to SQLite")
            
    cursor.execute(insert_persoon_query)
    sqliteConnection.commit()
    print("Persoon ingevoerd")
    
    cursor.close()
  
 
reg_label = Label(root,text="Registration Form", width =20, font=("bold", 20))
reg_label.place(x=90, y=53)

voor_naam_label = Label(root,text="Voornaam", width =20, font=("bold", 10))
reg_label.place(x=68, y=130)

voor_naam_entry = Entry(root,textvar=voor_naam)
voor_naam_entry.place(x=240, y=130)

tussenv_label = Label(root,text="Tussenvoegsel", width =20, font=("bold", 10))
tussenv_label.place(x=68, y=160)

tussenv_entry = Entry(root,textvar=tussen_voegsel)
tussenv_entry.place(x=240, y=160)

achter_naam_label = Label(root,text="Achternaam", width =20, font=("bold", 10))
achter_naam_label.place(x=68, y=190)

achter_naam_entry = Entry(root,textvar=achter_naam)
achter_naam_entry.place(x=240, y=190)

mo_biel_label = Label(root,text="Mobiel", width =20, font=("bold", 10))
mo_biel_label.place(x=68, y=220)

mo_biel_entry = Entry(root,textvar=mo_biel)
mo_biel_entry.place(x=240, y=220)

button = Button(root, text="Submit", width=20,bg='brown', fg='black', command=database).place(x=180, y=250)

root.mainloop()

[menator01]

You have to create the table explicitly before entering any data.

Here is a basic example. Note: there is not any kind of error checking.

# Do the imports
import sqlite3 as sq
import tkinter as tk

# Define a function to insert records and update the display label
def insert():
    ''' Connect/create the database. Create the table if it doesn't exist '''
    connect = sq.connect('my.db')
    cursor = connect.cursor()
    connect.execute('''
                    create table if not exists person (
                    id integer primary key,
                    name text not null,
                    mobile text not null
                    )
                    ''')

    # Setup, execute, and commit the query
    query = '''insert into person (name, mobile) values (?,?)'''
    cursor.execute(query, (namevar.get(), mobilevar.get()))
    connect.commit()

    # Fetch a count of records in the database
    records = cursor.execute('select count(*) from person').fetchone()[0]

    # Update the display label with text
    infovar.set(f'{namevar.get()} has been entered. Records: {records}')
   
    # Clearing the entry fields
    namevar.set('')
    mobilevar.set('')

# Initiate tk
root = tk.Tk()

# Setup the grid for root
root.columnconfigure(0, weight=1)
root.rowconfigure(0, weight=1)

# Decorate root window
root.title('Registration Form')
root.geometry('300x175+300+300')
root['padx'] = 8
root['pady'] = 8

# Create the StringVars
namevar = tk.StringVar()
mobilevar = tk.StringVar()
infovar = tk.StringVar()

# Container to hold all widgets
container = tk.Frame(root)
container.grid(column=0, row=0, sticky='news')
container.grid_columnconfigure(1, weight=3)
container.grid_rowconfigure(3, weight=3)

# Create the labels and fields
label = tk.Label(container, text='Name:')
label.grid(column=0, row=0, padx=8, pady=4)

name_entry = tk.Entry(container, textvariable=namevar)
name_entry.grid(column=1, row=0, sticky='we', pady=4)

label = tk.Label(container, text='Mobile:')
label.grid(column=0, row=1, padx=8, pady=4)

mobile_entry = tk.Entry(container, textvariable=mobilevar)
mobile_entry.grid(column=1, row=1, sticky='we', pady=4)

# Create the button
button = tk.Button(container, text='Register', command=insert)
button.grid(column=0, columnspan=2, row=2, padx=8, pady=8, sticky='nw')

# A display message label
display = tk.Label(container, textvariable=infovar)
display['font'] = (None, 12, 'normal')
display.grid(column=0, columnspan=2, row=3, sticky='news', pady=8)

root.mainloop()

[Mattaqua]

Ok thanks, will create it first then

[Mattaqua]

Thanks to all of your cooperation I managed to adjust it to the way I like and get it executed properly. Very grateful for your help all!