Check if username exist or not in python when registering

Question

I want to check if the username exist in the table or not.The code is working when i select everything from the table but i get a error when i only try username.The error i get is

File "\Python\Python37\lib\tkinter__init__.py", line 1705, in call return self.func(*args)

File /Calculator_calorie/login_register.py", line 89, in create

login_backend.insert(self.namere_text.get(),self.passwordr1e_text.get())

File \Calculator_calorie\login_backend.py", line 18, in insert

if cur.execute('SELECT name FROM user WHERE name = ?',(name)):

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.

import sqlite3
from tkinter import *
from tkinter import messagebox
from user import user

def connect():
    conn=sqlite3.connect("login.db")
    cur=conn.cursor()
    cur.execute("CREATE TABLE if NOT exists user(name text,password text)")
    conn.commit()
    conn.close()



def insert(name,password):
    conn=sqlite3.connect('login.db')
    cur = conn.cursor()
    if cur.execute('SELECT * FROM user WHERE name = ?', (name)):
        if cur.fetchone():
          messagebox.showinfo('Error', 'User already exist')
        else:
           cur.execute('INSERT INTO user VALUES(?,?)', (name, password))
           messagebox.showinfo('Register', 'Entry sucess')


    conn.commit()
    conn.close()




def create(self):
         if self.passwordr1e.get() != self.passwordr2e.get():
             messagebox.showinfo('error','Passwords do not match')
         elif len(self.namere.get()) == 0:
             messagebox.showinfo('error', 'Name field is empty')

         elif len(self.passwordr1e.get()) == 0:
               messagebox.showinfo('error', 'PASSWORD field is empty')

         else:
             login_backend.insert(self.namere_text.get(),self.passwordr1e_text.get())

To make a single element tuple, write "(name,)". Without comma it is same as "name" which is interpreted as sequence of characters here. — Michael Butscher, Feb 11 '20 at 23:09
Please don't write passwords without hashing them into your database. — nbk, Feb 11 '20 at 23:16
Is the shown code the one creating problems? If not, show that. Also show the full traceback of the error message. — Michael Butscher, Feb 11 '20 at 23:19
Your database should do this for you. Make sure your username column is `unique` and handle the insert error when you have a duplicate. Querying first and then inserting is (a) unnecessary and (b) not reliable unless you also add the `unique` constraint due to race conditions. — Chris, Feb 11 '20 at 23:27

Schwern · Answer 1 · 2020-02-11T23:58:42.967

As Michael commented, for Python to recognize a tuple with a single value it needs a comma at the end. (name,) not (name). See W3Schools Python Tuples.

if cur.execute('SELECT name FROM user WHERE name = ?',(name,)):

Note that if you're just checking if a row exists, select 1 to save a bit of time.

However, there's a safer and faster way to do this.

As Chris commented, checking for a user and inserting if they're not contains a race condition. If two processes try to create the same user at the same time they will both think it's not there and both insert resulting in a duplicate.

     Proc 1                                       Proc 2
t    SELECT * FROM user WHERE name = ?
i                                                 SELECT * FROM user WHERE name = ?
m    0 records found
e                                                 0 records found
|    INSERT INTO user VALUES(?,?)
v                                                 INSERT INTO user VALUES(?,?)

Instead, set up a unique constraint, do the insert, and catch the unique error.

CREATE TABLE if NOT exists user(
  name text unique not null,
  password text not null
)

def insert(name,password):
    conn=sqlite3.connect('login.db')
    cur = conn.cursor()

    try:
        cur.execute('INSERT INTO user VALUES(?,?)', (name, password))
        messagebox.showinfo('Register', 'Entry sucess')
    except sqlite3.IntegrityError as e:
        if re.match(r'UNIQUE constraint failed', e.args[0]):
            messagebox.showinfo('Error', 'User already exist')
        else:
            raise e

    conn.commit()
    conn.close()

Doing the check for existence and insert in a single query is referred to as being "atomic" because the two parts cannot be separated.

Notes

Writing SQL by hand is good for learning, but for production work use a framework such as SQL Alchemy.

Storing passwords as plain text is insecure. Use a password hashing library such as bcrypt and read up on password hashing.

Reconnecting to the database for every query is inefficient. Connect once and keep the connection open.

Check if username exist or not in python when registering

1 Answers1

Notes