CODEWITHSUNDEEP
pythonmysqltkinter
I appreciate your help
I appreciate your help

Reputation: 33

Duplicate entry '0' for key 'PRIMARY' and I cannot use auto_increment since the values do not always increase


**What I am trying to do**

I have been trying to insert a record to a table via tkinter, and I want to see the inserted record on mysql.

Issue
After entering a record to tkinter, I received the following error. I looked into the solutions for the same errors posted online, but it was to use auto_increment for column Code (the one with primary key). However, I cannot use auto_increment since the values of the column Code does not always increase. I have also tried deleting primary key for the column but I still got the same error. Any insights on this?


**Error:**
*mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '0' for key 'PRIMARY'*
**Code to create the table:**
#table
cursor=mycon.cursor(buffered=True)
cursor.execute("use iv")
cursor.execute("drop table if exists salesperson")
create_table='''create table salesperson
(
code int(4) primary key,
name varchar(15),
salary int,
itcode char
)'''
cursor.execute(create_table)

**Code to insert data to mysql from tkinter/python:** 
from tkinter import *
from tkinter.messagebox import showinfo

def add_data():
    code=tcode.get('1.0',END) #retrieve input
    name=tname.get('1.0',END)
    salary=tsal.get('1.0',END)
    itcode=titcode.get('1.0',END)

    #DATABASE CONNECTION
    if code=="" or name=="" or salary=="" or itcode=="":
        messagbox.showinfo("Please fill all the fields")
    else:
        import mysql.connector as sqltor
        connection=sqltor.connect(host="localhost",user="root",password="  ",database="iv")
        tkcursor=connection.cursor()
        tkcursor.execute("Insert into salesperson values (code,'name',salary,'itcode')")
        connection.commit()
        messagebox.showinfo("Records inserted")
        tkcursor.close()

Upvotes: 0

Views: 571

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

The problem is in the INSERT statement:

tkcursor.execute("Insert into salesperson values (code,'name',salary,'itcode')")

When you reference an identifier in an SQL statement like this, it's an SQL identifier, not a Python variable. It's not an error in this case because your table coincidentally has columns named code and salary.

But what is the value of these columns? Since this is an INSERT statement, by definition the row doesn't exist yet as the VALUES() clause is evaluated. So the value of all columns of that row is NULL. It's exactly as if you had done this:

tkcursor.execute("Insert into salesperson values (NULL,'name',NULL,'itcode')")

Since code is the primary key, it must be NOT NULL. Even though the column does not have a DEFAULT defined, MySQL has a concept of a "default default." That is, in absence of an explicitly defined default value, each data type will be converted to an appropriate implicit default value if NULL is not accepted (see https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html for documentation on this). For an integer, the implicit default is 0. So your statement works as if you did this:

tkcursor.execute("Insert into salesperson values (0,'name',NULL,'itcode')")

How to fix this? You should use parameters to help you get the values of your Python variables into your SQL INSERT statement. That way the Python variable code will be used, not the SQL column that is also named code. The comment above from nbk hinted at this.

tkcursor=connection.cursor(prepared=True)
tkcursor.execute("Insert into salesperson values (%s,'name',%s,'itcode')", (code, salary,)
connection.commit()

See https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html for more information.

Upvotes: 1

Related Questions