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}
CodePudding user response:
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
.
CodePudding user response:
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.