Taking data from user and saving it in database

Question

I'm trying to take data from the user and insert it in to my database. Everything works fine until I clicked on 'Save' button. Then I get those erros:

Exception in Tkinter callback Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/tkinter/init.py", line 1705, in call return self.func(*args) File "/Users/alidemirkazik/PycharmProjects/GUI_Test/GUI_Test.py", line 51, in add_customer mycursor.execute(sql, val) File "/Users/alidemirkazik/PycharmProjects/GUI_Test/venv/lib/python3.7/site-packages/mysql/connector/cursor.py", line 551, in execute self._handle_result(self._connection.cmd_query(stmt)) File "/Users/alidemirkazik/PycharmProjects/GUI_Test/venv/lib/python3.7/site-packages/mysql/connector/connection.py", line 490, in cmd_query result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) File "/Users/alidemirkazik/PycharmProjects/GUI_Test/venv/lib/python3.7/site-packages/mysql/connector/connection.py", line 395, in _handle_result raise errors.get_exception(packet) mysql.connector.errors.DatabaseError: 1366 (HY000): Incorrect integer value: '' for column 'ID' at row 1

Appreciate if anyone can help me with it. Thanks!

r = Tk()
r.title('Adding New Customer')
r.geometry("175x62")


def quit():
    r.destroy()


def contact():
    contact_window = Toplevel(r)
    Label(contact_window, text='ID').grid(row=0)
    Label(contact_window, text='Name').grid(row=1)
    Label(contact_window, text='Address').grid(row=2)
    Label(contact_window, text='Country').grid(row=3)
    Label(contact_window, text='Age').grid(row=4)
    input1 = Entry(contact_window)
    input2 = Entry(contact_window)
    input3 = Entry(contact_window)
    input4 = Entry(contact_window)
    input5 = Entry(contact_window)
    input1.grid(row=0, column=1)
    input2.grid(row=1, column=1)
    input3.grid(row=2, column=1)
    input4.grid(row=3, column=1)
    input5.grid(row=4, column=1)

    first_id = input1.get()
    first_name = input2.get()
    first_address = input3.get()
    first_country = input4.get()
    first_age = input5.get()

    def add_customer():
        mycursor = mydb.cursor()
        sql = "INSERT INTO customers (ID, Name, Address, Country, Age) VALUES (%s, %s, %s, %s, %s)"
        val = (first_id, first_name, first_address, first_country, first_age)
        mycursor.execute(sql, val)
        mydb.commit()

    button3 = Button(contact_window, text="Save", command=add_customer)
    button3.grid(row=5, column=1)


button1 = Button(r, text="Contact", command=contact).pack(fill=X, ipady=5)
button2 = Button(r, text="Exit", command=quit).pack(fill=X, ipady=5)

r.mainloop()

score 1 · Answer 1 · answered Nov 07 '19 at 10:33

1

Looks like the ID you are adding is not an integer, which is the type expected on the database.

I think you just need to cast the first_id as an integer. So, something like that:

val = (int(first_id), first_name, first_address, first_country, first_age)

Moreover, I don't think is a good idea to allow end-user to enter the ID for the database. I'd suggest you to make the database auto-populate the IDs for the new entries in an incremental way.

answered Nov 07 '19 at 10:33

edgarzamora

1,472
1
9
17

I will edit it later. I still have to add an integer value for 'Age' field anyway. I tried your way however I still get an error which is that: File "/Users/alidemirkazik/PycharmProjects/GUI_Test/GUI_Test.py", line 47, in add_customer val = (int(first_id), first_name, first_address, first_country, int(first_age)) ValueError: invalid literal for int() with base 10: '' – kafkef89 Nov 07 '19 at 10:38
That's because the value you are entering on the `first_id` is not a valid int. Check this thread: https://stackoverflow.com/a/8948303/1130381 I'll suggest to double-check the value you are entering on the first_id, and validate the input as you only can accept integer fields. – edgarzamora Nov 07 '19 at 11:12

score 0 · Answer 2 · answered Nov 07 '19 at 22:46

In this line it looks like you are telling it to expect a string for the first value in VALUES

sql = "INSERT INTO customers (ID, Name, Address, Country, Age) VALUES (%s, %s, %s, %s, %s)"

replace with this to have an integer format placeholders where appropriate

sql = "INSERT INTO customers (ID, Name, Address, Country, Age) VALUES (%d, %s, %s, %s, %d)"

It also may have issue with "ID" itself, I mention this because it says "Incorrect integer value" in the error snippet. When an integer itself is expected it always says (for me at least) "unexpected (string/type) for [x], expected integer." Or "Got string, expected int"

I also think you need to initialize text variables for the entry fields. It also looks like you're using the wildcard import, I would import the tkinter library as tk to help keep track for debugging purposes. But that is just a preference of mine.

import tkinter as tk #for python 3

#for each entry/variable
f_id = tk.StringVar()
input1 = tk.Entry(contact_window, textvariable = f_id) 
first_id = int(input1.get()) #int() when needed

Taking data from user and saving it in database

2 Answers2