How to raise error message on duplicate entry while inserting data into sqlite3 with python?

Question

Guys I'm using sqlite3 with python tkinter as front end. The database is a simple one with two fields, username and password. I want to make a registration sign up page. where data given in the two fields to be stored in a sqlite3 database. Data is inserting properly. But I want to display a messagebox when the username provided already exist in the database. I tried the below code.

MY CODE:

def signup():
    userID = username.get()
    passwd = password.get()
    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    result = c.execute("SELECT * FROM userstable")

    for i in result:
        if i[0] == userID:
            messagebox.showerror("DUPLICATE", "USER ALREADY EXISTS!")

    else:
        conn = sqlite3.connect('test.db')
        c = conn.cursor()
        c.execute("INSERT INTO userstable VALUES (?, ?)", (userID, passwd))
        conn.commit()
        c.close()
        conn.close()
        username.delete(0,END)
        password.delete(0,END)
        username.focus()
        messagebox.showinfo("SUCCESS", "USER CREATED SUCCESSFULLY")

This works but still the duplicate data is being stored after the error message. My requirement is to throw the error and stop executing if the username is already available. If the username is not available already it should insert the data.

Where am I going wrong? could some one explain me by pointing out or is there any other way I can achieve this? It seems I need to modify something to my function. Please guide me.

EDIT 1

If i try to use three conditions the break is not working.

MY CODE

def data_entry():
    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    c.execute('CREATE TABLE IF NOT EXISTS userstable(username TEXT, password TEXT)')
    username = uname.get()
    password = passwd.get()

    result = c.execute("SELECT * FROM userstable")

    if username != '' or password != '':

        for i in result:
            if i[0] == username:
                tkinter.messagebox.showerror("DUPLICATE", "USER ALREADY EXISTS!")
                break
        else: 
            c.execute('INSERT INTO userstable (username, password) VALUES(?, ?)',(username,password))
            conn.commit()
            c.close()
            conn.close()
            another_clear()
            tkinter.messagebox.showinfo("Success", "User Created Successfully,\nPlease restart application.")

    else:
        tkinter.messagebox.showerror("ERROR", "Fill both fields!")

Answer 1

A better way to approach this would be to create a unique constraint (index, in sqlite) on the table to prevent the insertion of a duplicate username. That way, you can try/except the insert statement instead of looping through a list of all users to see if it already exists (that's not scalable). This would also prevent you from having to "select *", which is generally bad practice (try to be explicit).

https://sqlite.org/lang_createtable.html

So, you could add the constraint either as a unique index, or as a primary key. If you only have 2 columns in this table, or if you have more than 2 but no additional IDs, your username can be your primary key. If you are introducing a system ID for your users, I'd use that as your primary key and username as a unique index. Either way, you'll need to alter your table to add the constraint.

CREATE UNIQUE INDEX username_uidx ON userstable (username);

Again, because you're not explicitly letting us know the column names, you'll have to fill that in.

After that:

try:
    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    c.execute("INSERT INTO userstable VALUES (?, ?)", (userID, passwd))
    conn.commit()
except: # I'm not sure the exact error that's raised by SQLite
    messagebox.showerror("DUPLICATE", "USER ALREADY EXISTS!")
finally:
    c.close()
    conn.close()

I typically wrap my cursor and connections in a finally so that they close even if there's an exception. That's not 100% of what you need, but it should get you there in one step with better DB design to enforce the uniqueness on a user.

Answer 2

I advise against using a loop with an else statement, it can be confusing.
See this post why-does-python-use-else-after-for-and-while-loops for more info.

If you want to use for - else you can add a break, so else will not be executed :

for i in result:
    if i[0] == userID:
        messagebox.showerror("DUPLICATE", "USER ALREADY EXISTS!")
        break
else:
    ...

Or you can use a true / false flag :

user_exists = False
for i in result:
    if i[0] == userID:
        messagebox.showerror("DUPLICATE", "USER ALREADY EXISTS!")
        user_exists = True
if not user_exists : 
    ...