Why is fetchone() returning none here?

Question

Why does the fetchone() return None when onLogin() is called, even though the database has a row with a value? When I use the same query in the sql database it returns the correct value. Using fetchall() and using a for loop with it returns nothing on the terminal.

import mysql.connector
#SQL CONNECTION
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="python"
)
cursor = db.cursor(buffered=True)


#main
def onStart():
    global username
    print("Enter a username to continue")
    username = input()
    #Checking the database for the username
    query = "SELECT username FROM userdata"
    cursor.execute(query)
    result = cursor.fetchall()
    for x in result:
        if x == (username,):
            onLogin()
        else:
            print("Error Occurred!")
def onLogin():
    print("Enter a password")
    password = input()
    #comparing pwds
    query = "SELECT password FROM userdata WHERE username = '%s'"
    cursor.execute(query, username)
    result = cursor.fetchone()
    print(result)
onStart()

You mustn't quote the placeholder (`%s`, not `'%s'`). You must pass a *tuple* of values to `execute`, not a single value. — deceze, Sep 30 '20 at 14:31
@deceze `username` is global variable here so it works, checking by using `print(username)` in `onLogin`, using `%s` instead of `'%s'` gives a syntax error. Can you give an example how to pass username as a tuple correctly? — Hassaan Shafqat, Sep 30 '20 at 14:35

score 1 · Answer 1 · answered Sep 30 '20 at 14:35

As deceze said, you shouldn't quote the '%s' placeholder in the prepared query.

In addition, you should avoid doing getting every single user in your database just to figure out if this one exists - and while you're at it, you can get the password in the same query and just compare it in your program. (Naturally in a real-life situation you'd use a password derivation function and not store cleartext passwords in the database.)

import mysql.connector

# SQL CONNECTION
db = mysql.connector.connect(
    host="localhost", user="root", password="", database="python"
)
cursor = db.cursor(buffered=True)


def authenticate():
    username = input("Enter a username to continue")

    cursor.execute(
        "SELECT username, password FROM userdata WHERE username = %s LIMIT 1",
        (username,),
    )
    result = cursor.fetchone()
    if not result:
        print("No such user.")
        return None

    username, correct_password = result
    password = input("Enter a password")
    if password == correct_password:
        print("Access granted!")
    return username

username = authenticate()
# If `username` is none, authentication failed

Why is fetchone() returning none here?

1 Answers1