How can I check if a record exists for a given username in this table?

Question

How can I check if a record exists where the username of the record has the value of the variable usernameentryentry?

I want to create an if statement so that if there is already a record with the username of the same value as the usernameentryentry variable then the user is told that the username is taken already.

How can I do this?

import sqlite3

with sqlite3.connect("userdatabase.db") as db: 
    cursor = db.cursor() 

usernameentryentry = "username1"

cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
userID INTEGER PRIMARY KEY,
username VARCHAR(20) NOT NULL, 
password VARCHAR(20) NOT NULL) ;
''') 

#variable defined below for use in the if statement:
#variable = true if there isnt a record with the username "username1" and 
#false if there is a record with the username "username1"

Do a google search on the WHERE clause in SQL. Then, give it a shot and people will help you out if you get stuck. — Sparky, Feb 24 '19 at 02:18
I've used the code: cursor.execute('''SELECT username, password FROM users WHERE username=?''', [usernameentryentry]) userdetails = cursor.fetchone() . When I print userdetails when there is no record then it prints "None" but if I use an if statement for if userdetails = "None" then do this command, when there is no record and userdetails should equal "None", the command isnt executed — , Feb 24 '19 at 03:33

score 0 · Answer 1 · answered Feb 24 '19 at 05:43

This post shows how to check if a record exists in a optimized way

To check if usernameentryentry already is taken:

cursor.execute('''SELECT 1 FROM users WHERE username = (?)''',[usernameentryentry])

If there is no record, "cursor.fetchone()" has a value of "None". You can use it in a conditional expression (None has the value of false).

So your code would look like this:

def insertUser(userID, usernameentryentry, password):
    cursor.execute('''SELECT 1 FROM users WHERE username = (?)''',[usernameentryentry])
    userAlreadyExists = cursor.fetchone()
    if(userAlreadyExists):
        print("Username already exists")
    else:
        cursor.execute('''INSERT INTO users(userID, username, password) VALUES(?,?,?)''', (userID,usernameentryentry,password))
        db.commit()

You can try inserting a user two times and check for yourself:

insertUser(123,'james','pwd123')
insertUser(124,'james','blink182')

To see all users:

cursor.execute('''SELECT * FROM users''')
result = cursor.fetchall()
print(result)

How can I check if a record exists for a given username in this table?

1 Answers1