MySQL-Python connector attribute error for cursor object

Question

I'm using the mysql-connector-python package for a project in Flask. For the signup routing portion, I want MySQL to check if a username already exists, and if it does, I'll set an error. My code looks like this:

@bp.route('/signup', methods=('POST',))
def signup():

    ...

    username = reqObj["username"]
    password = reqObj["password"]
    error = None

    db = get_db()
    cursor = db.cursor()
    query = ("SELECT * FROM userinfo " 
        "WHERE username=%s")

    existingUser = cursor.execute(query, (username,)).fetchone()

    if existingUser is not None:
        error = "Username already exists"

    if error is None:
        cursor.execute(
            'INSERT INTO userinfo (username, password) VALUES (%s, %s)',
            (username, generate_password_hash(password))
        )

        cursor.close()
        db.commit()

        res = {'actionSuccess': True}
        return jsonify(res)
    else:
        res = {'actionSuccess': False, 'error': error}
        return jsonify(res)

The get_db() function returns the mysql.connector.connect() object. However, when I run this on a server and test it, I get an error in Flask saying:

...
existingUser = cursor.execute(query, (username,)).fetchone()
AttributeError: 'NoneType' object has no attribute 'fetchone'

So I figure my error is somewhere with my cursor.execute(), which I'm guessing is returning a NoneType. So I delete the fetchone() function and try again, but now I get this error instead:

...
File "e:\flasktest2\lib\site-packages\mysql\connector\connection.py", line 1128, in handle_unread_result
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

This is confusing even further, because it's now saying that there are unread results, when a moment ago the cursor.execute() was a NoneType. I don't know what's causing the error. I then tried commenting out the whole SELECT query section, and just tested to see if the INSERT would work, and sure enough it did. What's causing my errors?

You are breaking the statement here query = ("SELECT * FROM userinfo " "WHERE username=%s"). try something like this query = ("SELECT * FROM userinfo WHERE username=%s") — Arihant
– Arihant, Commented Sep 19, 2018 at 8:12
Apparently execute() on a mysql.connector Cursor object returns None unless it is called with multi=True. Try cursor.execute(query, (username,)) and then existingUser = cursor.fetchone() individually. As for the Unread results found, this is somehwat explained here. — shmee
– shmee, Commented Sep 19, 2018 at 8:27
@shmee Hey yea I just figured this out myself! For some reason, cursor.execute() will complete the action, return None, and then store the results in the cursor object itself. So I have to use fetchone on the cursor obj, not on cursor.execute() — albert
– albert, Commented Sep 19, 2018 at 8:32
This behavior seems to differ between the modules. With sqlite3 it is perfectly fine to do cursor.execute(<SQL>).fetchone() because execute returns the cursor object it is called from. In pymysql, execute returns the number of affected rows. PEP 249 does not define return values for execute. The result of a query is always at least referenced in the cursor, so that c.execute*(); c.fetch*() will work with any DBAPI2 compliant module. — shmee
– shmee, Commented Sep 19, 2018 at 8:48
@albert: check this out: stackoverflow.com/questions/30421466/… — Abhi
– Abhi, Commented Sep 19, 2018 at 10:33

albert · Accepted Answer · 2018-09-19 08:37:26Z

0

So apparently, with the mysql connector, cursor.execute(...) will return None, no matter what the results of executing the statements are. However, the results WILL be stored in the cursor object itself, even if the result is an empty set. You must also fetch all results for the current query before using the cursor to execute new statements on the new connection as well. This means doing:

cursor.execute(...)
cursor.fetchone()

as opposed to:

cursor.execute(...).fetchone()

answered Sep 19, 2018 at 8:37

albert

1,3763 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL-Python connector attribute error for cursor object

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related