Why does cursor.fetchone() return {'1': 1} instead of row values?
I expect something like {'username': 'Bob', 'password': 'hunter2'}.
from flaskext.mysql import MySQL
from pymysql.cursors import DictCursor
# ...
mysql = MySQL(cursorclass=DictCursor)
# ...
cursor = mysql.get_db().cursor()
# ...
cursor.execute('SELECT 1 FROM users WHERE username = %s AND password = %s', [name, password])
row = cursor.fetchone()
print(row) # {'1': 1}
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
You’re selecting a column named 1 (because it’s an implicit name for a constant “column” selected) that has a value 1.
If you did SELECT 2 FROM ... you’d get {"2": 2}.
If no rows match the FROM, you’d get None (and cursor.rowcount would be zero).
I expect something like (Bob, hunter2) because that’s the row’s values.
Then you’ll need to SELECT username, password, not SELECT 1. Then you’d get a dict {"username": "Bob", "password": "hunter2"}.
… and not multiple rows from the table
With your query, if there are multiple rows with the WHERE’d username and password, you’d get multiple rows of {"1": 1}. If you only want the first matching row, add LIMIT 1.
Method 2
Instead of selecting a column from the table you are selecting a literal value, which is 1. So MySQL must assign a column name to the value and uses a string that is the same value as the column value. That would be ‘1’ in this case.
You can explicitly assign a column name by using AS:
SELECT 1 AS some_name …
It would appear that the SELECT statement is verifying a user id/password combination by seeing if a row is returned which will only occur if the user id and password match. The content of the row returned is not important.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0