checking a variable against a record in a sqlite3 database to see if data entered is unique-CodePudding

so I am trying to create a function that allows a user to create a profile with personal information, in this they will enter a username that will act as primary key and requires to be unique, so when entering this username I am trying to check the data entered to see if it already exists in the sqlite3 database, if it does, the user is asked to try another username, if not the function will continue.

I was certain this should work because i used similar code to check values entered when a user logs in in a login function i coded, so i am quite stumped.

Any help would be greatly appreciated...

the code in question:

def signupInfo():
  #takes input for username and checks it is alphanumeric, username will also act as primary key so a validation for it being unique will occur
  username = input("Choose username: ")
  #check for if the username is unique
  uniqueUserCheck = '''SELECT * FROM users WHERE `username` = ?'''
  cursor.execute(uniqueUserCheck, [username])
  user = cursor.fetchone()
  if user is not None:
    username = input("Choose a unique user: ")
  else:
    #check if username is alphanumeric or has enough characters
    while username.isalpha() == False or len(username) <= 3:
      username = input("invalid username, try again: ")

CodePudding user response：

It looks like you're trying to check if the username already exists in the database. To do this, you can use an SQL SELECT query to check if the username exists in the users table. If the query returns a result, then the username already exists and you can prompt the user to enter a different username.

Here's one way you could implement this:

def signupInfo():
  #takes input for username and checks it is alphanumeric, username will also act as primary key so a validation for it being unique will occur
  username = input("Choose username: ")

  #check if username is alphanumeric or has enough characters
  while username.isalpha() == False or len(username) <= 3:
    username = input("invalid username, try again: ")

  #check for if the username is unique
  uniqueUserCheck = '''SELECT * FROM users WHERE `username` = ?'''
  cursor.execute(uniqueUserCheck, [username])
  user = cursor.fetchone()
  if user is not None:
    username = input("Choose a unique user: ")

Note that in your current implementation, if the username is not unique, you will only prompt the user to enter a new username once, but the code does not check if the new username is unique. To fix this, you can use a while loop to keep prompting the user until they enter a unique username.

Here's one way you could do this:

def signupInfo():
  #takes input for username and checks it is alphanumeric, username will also act as primary key so a validation for it being unique will occur
  username = input("Choose username: ")

  #check if username is alphanumeric or has enough characters
  while username.isalpha() == False or len(username) <= 3:
    username = input("invalid username, try again: ")

  #check for if the username is unique
  uniqueUserCheck = '''SELECT * FROM users WHERE `username` = ?'''
  cursor.execute(uniqueUserCheck, [username])
  user = cursor.fetchone()

  # keep prompting the user until they enter a unique username
  while user is not None:
    username = input("Choose a unique user: ")
    cursor.execute(uniqueUserCheck, [username])
    user = cursor.fetchone()

CodePudding user response：

You are checking if user is None and if it is, you are asking the user to enter a new username. However, this means that if the username is not unique, the user will be asked to enter a new username indefinitely.

Instead, you should create a while loop that continues until the user enters a unique username. You can do this by checking if user is None, and if it is not, asking the user to enter a new username and then running the query again.

Here is an example of how you could implement this:

def signupInfo():
  #takes input for username and checks it is alphanumeric, username will also act as primary key so a validation for it being unique will occur
  username = input("Choose username: ")

  #check if username is alphanumeric or has enough characters
  while username.isalpha() == False or len(username) <= 3:
    username = input("invalid username, try again: ")

  #check for if the username is unique
  uniqueUserCheck = '''SELECT * FROM users WHERE `username` = ?'''
  cursor.execute(uniqueUserCheck, [username])
  user = cursor.fetchone()

  # keep asking for a new username until the user enters a unique one
  while user is not None:
    username = input("This username is already taken, choose another: ")
    cursor.execute(uniqueUserCheck, [username])
    user = cursor.fetchone()

  # at this point, the username is unique and can be used for the new user
  print("This username is available and can be used for your profile")

I hope this helps!