cursor.fetchone() returns None even though a value exists

Question

I am trying to figure out how to fetch a single value from a UserID and print it in the console. My current piece of code keeps returning "None" even though there is a value for Currency for the UserID I am searching.

cursor.execute("SELECT Currency FROM ServerUsers WHERE USERID = %s" % (userid))
    if cursor.fetchone():
        a = cursor.fetchone()
        print(a)

Solution 1

When you call execute, the results are computed and fetched, and you use fetchone/fetchmany/fetchall to retrieve them.

In your case, your query returns a single row as the result, so calling cursor.fetchone in the if causes the result to be fetched and subsequently thrown away, so another call to fetchone will yield None as the pointer has already advanced past the only row in the result set.

The idiomatic sqlite way of checking if data exists, is to query a row, and test its truthiness -

result = cursor.fetchone()
if result:
    print(result)

Solution 2

I was experiencing the same problem as you had. I used Sqlite and Pycharm Editor, I fixed the problem by refresh the database I use in Pycharm (View -> Tool Windows -> Database -> Refresh). I know the success rate is low but I hope this helps somebody experienced the same problem.

Comments

[Jordan Atkinson]

I was today years old when I learned that the word "truthiness" existed! :') - Cheers for the answer here, didn't realize you could check the true/false of the object like that.