Why does the query return a number instead of the row values?-CodePudding

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.