How to Iterate Through Cur.Fetchall() in Python

iterating fetchall() dictionary in for loop isn't working

The logic in this loop does not work:

    for user in users:
        if username != user[1]:   ## user[1] gives username
            api.abort(404, "Username: {} doesn't exist".format(username))
        if password != user[2]:
            api.abort(401, "Wrong password")

We are iterating over all the users, but if any user's name doesn't match the name from the request we immediately return a 404. Likewise, if the password doesn't match, we immediately return a 401.

This is better:

    ...
    for user in users:
        if username == user[1]:   ## user[1] gives username
            # Assume user names are unique
            # Only check password if the username matches
            if password == user[2]:
                # FIXME: in the question generate_token appears to be 
                # a method of this class, but is outside the class
                return {"token": self.generate_token(username)}
            api.abort(401, "Wrong password")
     api.abort(404, "Username: {} doesn't exist".format(username))

However we can do this work in a single database query, by asking the database whether there is a row in the table that matches the user name and password from the request.

First of all, let's make sure that no two users can have the same username, by making it unique in the database:

CREATE UNIQUE INDEX idx_unique_user_name ON authorized_user_table (user_name);

Now in the Python code:

    ...
    # Count the rows that match username AND password
    cursor.execute("""SELECT COUNT(*) FROM public.authorized_user_table """
                   """WHERE user_name = %s AND user_password = %s""",
                   (username, password))
    # COUNT will always return just one row
    user = cursor.fetchone()
    if user[0] == 1:
        return {"token": self.generate_token(username)}
    # Don't let an attacker know what they have got right or wrong.
    api.abort(401, "Invalid user or password")

The above variant returns less information in the event of an error. This is usually a good idea for login handlers, because if an attacker is guessing usernames and passwords you don't want to let them know whether they have found a valid username.

If you want responses to distinguish between incorrect usernames and passwords, you can combine the two approaches.

    ...
    cursor.execute("""SELECT user_password FROM public.authorized_user_table """
                   """WHERE user_name = %s""",
                   (username,))
    user = cursor.fetchone()
    if not user:
        api.abort(404, "Username: {} doesn't exist".format(username))
    if user[0] == password:
        return {"token": self.generate_token(username)}
    api.abort(401, "Wrong password")

Whichever solution you choose, the takeaway is that when matching data with data in a database, you want to avoid fetching lots of rows from the database and matching in your application. It's usually much faster to let the database do the work, by crafting a suitable query.

How to iterate through cursor after mysql select * query in python?

MySQLdb conforms to PEP-249.

Therefore,execute must return an iterator. You can just do:

for tupl in cursor.execute(query):
    pass

where tupl is a tuple.

---

*Alternatively, you can use fetchone, fetchmany(n) or fetchall() to evaluate at once.

Python mysql.connector cursor.execute() and connection.commit() not working in a loop

Here's some sample code that shows how the "Commands out of sync" error can occur:

from mysql.connector import connect, Error
# replace asterisks in the CONFIG dictionary with your data
CONFIG = {
    'user': '*',
    'password': '*',
    'host': '*',
    'database': '*',
    'autocommit': False
}
try:
    with connect(**CONFIG) as conn:
        try:
            with conn.cursor() as cursor:
                cursor.execute('select * from ips')
                # cursor.fetchall()
        finally:
            conn.commit()
except Error as e:
    print(e)

Explanation:

The code selects all rows from a table called "ips" the contents of which are irrelevant here.

Now, note that we do not attempt to get a rowset (fetchall is commented out). We then try to commit the transaction (even though no changes were made to the table).

This will induce the "Commands out of sync" error.

However, if we take out the comment line and fetch the rowset (fetchall) this problem does not arise.

Explicitly fetching the rowset is equivalent to iterating over the cursor.

If we change the autocommit parameter to True and remove the explicit commit(), we get another error:- "Unread result found".

In other words, it seems that MySQL requires you to get the rowset (or iterate over the cursor) whenever you select anything!

Note that even if autocommit is enabled (True) explicit calls to commit() are permitted

Solutions:

Either ensure that the client application iterates over the entire cursor after SELECT or in the CONFIG dictionary add: 'consume_results': True

Python: Iterating over rows in a MySQL table

Do this with a single database query, not a Python loop. You can use a CASE expression to handle the cases where the location doesn't map directly to an organization ID.

UPDATE ost_user AS u
JOIN ost_user__cdata AS c ON u.id = c.user_id
SET u.org_id = 
    CASE c.location
        WHEN '1' THEN '8'
        WHEN '8' THEN '9'
        ELSE c.location
    END

---

Related Topics