Unable to read from database records sqlite3 python

Question

This is the first time that I am using sqlite3 and that I'm working with databases. I'm making an online tic tac toe game, so that I can play with my friends. In def dataRead(): I'm trying to read the data that the user has entered from the registration window and I'm trying to check if the data has already been saved in the database. If yes, the user has to enter another username.

    def register():
        ##initialising sqlite
        con = sqlite3.connect("userData.db")
        c = con.cursor()
        def submit():
            #creating tables in the database
            def createTable():
                c.execute("CREATE TABLE IF NOT EXISTS userInfo(username TEXT, password TEXT)")

            def dataRead():
                username = user.get()
                password = pword.get()
                c.execute("SELECT username FROM userInfo")
                data = c.fetchall()
                try:
                    for row in data:
                        if row == username:
                            Label(regWin, text = "Sorry, username already in use...\nTry another one.", fg = "red").pack()
                            print(data)
                        else:
                            dataEntry(username, password)
                except TypeError:
                    dataEntry(username, password)
                    
            def dataEntry(username, password):
                c.execute("INSERT INTO userInfo(username, password) VALUES (?, ?)", (username, password))
                con.commit()
                
            createTable()
            dataRead()
        

I tried using c.fetchall() to read the records for username in userInfo, so that the program can check if the username is available or not, but it doesn't seem to work (for me at least).

Answer 1

The records returned are tuples, so you need to use row[0] == username instead:

def dataRead():
    username = user.get()
    password = pword.get()
    
    c.execute("SELECT username FROM userInfo")
    data = c.fetchall()
    found = False
    for row in data:
        if row[0] == username:
            found = True
            Label(regWin, text = "Sorry, username already in use...\nTry another one.", fg = "red").pack()
            print(row)
            break
    if not found:
        dataEntry(username, password)

---

However, you don't need to fetch all records from the database. You can use WHERE clause to get the required record:

def dataRead():
    username = user.get()
    password = pword.get()

    c.execute('SELECT username FROM userInfo WHERE username = ?', (username,))
    data = c.fetchone()
    if data:
        Label(regWin, text = "Sorry, username already in use...\nTry another one.", fg = "red").pack()
        print(data)
    else:
        dataEntry(username, password)

---

Also it is better to make username field an unique field:

def createTable():
    c.execute("CREATE TABLE IF NOT EXISTS userInfo(username TEXT PRIMARY KEY, password TEXT)")

So that no duplicate username in the table.