How to prevent sqlite3 to Auto-Increment the Primary Key if Null value is specified?

Question

So, I am creating a simple GUI in Python using Tkinter.

Now, when the user leaves the Roll Number (i.e. primary key) label widget empty and tries to insert the data, I will be inserting None instead of an empty string and I want to throw an Error but instead the data gets inserted with an auto-incremented Roll Number value. I don't want the Primary Key to get inserted if the Roll Number label widget is left empty.

This the code I am using for Creating the Table

c.execute("""
    CREATE TABLE students(
    enroll_no integer NOT NULL PRIMARY KEY,
    first_name text NOT NULL,
    middle_name text,
    last_name text,
    course text NOT NULL,
    semester integer NOT NULL
    )
""")

And this the code for Inserting into the Table

    try:
        c.execute("INSERT INTO students VALUES (:inp_rn, :inp_fn, :inp_mn, :inp_ln, :inp_course, :inp_semester)",
            {
                'inp_rn':inp_rn.get() if inp_rn.get() != "" else None,
                'inp_fn':inp_fn.get() if inp_fn.get() != "" else None,
                'inp_mn':inp_mn.get() if inp_mn.get() != "" else None,
                'inp_ln':inp_ln.get() if inp_ln.get() != "" else None,
                'inp_course':inp_course.get() if inp_course.get() != "" else None,
                'inp_semester':inp_semester.get() if inp_semester.get() != "" else None
            }
        )
    except sqlite3.IntegrityError:
        messagebox.showerror("Insertion Failed","Insertion Failed")

ther might b a better way, but what i did was, i took the entry box related to roll number and said if `e1.get() == '':` messagebox or anything will be okay here, `else:` put all the code of databse below this block. Note that this has to go inside of the functio that the button is calling — Delrius Euphoria, May 31 '20 at 09:04
You can't. An `integer primary key` column is an alias for the `rowid` number, which must be present. It's how sqlite works. — Shawn, May 31 '20 at 09:05
Let me know if it did work, so i can add that as an answer:) — Delrius Euphoria, May 31 '20 at 09:07
@CoolCloud Its inp_rn, also can you please tell how to prevent user to insert a string into semester column, it has to be integer. But if the user inserts a string it gets inserted right now! — , May 31 '20 at 09:27
***prevent user to insert a string***: Read [how-to-tell-user-to-only-use-integers-if-they-input-string-in-python-tkinter](https://stackoverflow.com/questions/42848360) — stovfl, May 31 '20 at 10:28

Delrius Euphoria · Accepted Answer · 2020-05-31T15:28:48.253

1

Use a simple if, else statement to tackle this error. Let entrybox related to roll no. be e1. Paste all the blocks of database after and paste it below else block, Please consider upvoting too

if e1.get() == '':
   messagebox.showerror('Please Fill','Please specify roll number to proceed')
else:
................
................

To simply check if an a variable is int() or not, you can use .isdigit() which returns a boolean. First assign a variable to your e1.get() then follow as here. Remeber that only pure digit can be used, any use of hyphen(-) or special characters will result in execution of else block :)

checking = e1.get()
if checking.isdigit() == True:
    .........
else:
    messagebox.showerror('Integrity Error','It must be a integer')

edited May 31 '20 at 15:28

answered May 31 '20 at 09:26

Delrius Euphoria

14,910
3
15
46

Can you please also tell how to prevent user to insert a string into semester column, it has to be integer. But if the user inserts a string it gets inserted right now! – May 31 '20 at 09:46
Updated answer with one of the possible methods but remember that it can make your code more nested inside of `if` statements, that shouldnt be an issue but ya keep in mind.But as a small piece of advice use MySQL so u have more control over the data being entered :) – Delrius Euphoria May 31 '20 at 15:31

How to prevent sqlite3 to Auto-Increment the Primary Key if Null value is specified?

1 Answers1