Issue with SELECT query in MySQL Connector: Attempted conversion to float for multiple arguments beyond the first

Question

I'm facing an issue when executing a SELECT query in Python (Flask) using the MySQL Connector (From the flask_mysqldb). Specifically, when I include more than one argument in the SELECT statement, starting from the second argument, it attempts to convert the values to float. When I do the same SELECT from the MySQL CLI It works fine.

Here is the table schema:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    hashed_password VARCHAR(255) NOT NULL
);

And here is the code snippet:

cur = mysql.connection.cursor()
print('Connected to MySQL')
cur.execute("SELECT id, email FROM users;")  # This is where the issue occurs
rv = cur.fetchall()
cur.close()

The error message I'm encountering on the localhost page is:

ValueError: could not convert string to float: b'[email protected]'

It seems that the values retrieved from the email column are being represented as byte strings (b'...'), causing the conversion error.

How can I resolve this issue and ensure that the SELECT query correctly retrieves and handles string values from the MySQL database?

I've tried testing the queries through the MySQL CLI and they work but they don't work here if theres more than one statement and the second one is not a number.

Answer 1

The issue you're encountering seems to be related to how the MySQL Connector in Flask interprets the data returned from the database. The bytes-like object (b'[email protected]') suggests that the data is being returned as bytes instead of strings. To resolve this, you can explicitly decode the bytes into strings when fetching the results. Here's how you can modify your code to handle this:

cur = mysql.connection.cursor()
print('Connected to MySQL')
cur.execute("SELECT id, email FROM users;")
rv = cur.fetchall()

# Decode bytes-like objects to strings
rv = [(id, email.decode('utf-8')) for id, email in rv]

cur.close()

In the modified code snippet above, I assume that the encoding used by your database is UTF-8. If it's different, you should use the appropriate encoding.

This modification ensures that the email values are retrieved and handled as strings rather than bytes-like objects, resolving the ValueError you encountered.