Python/MySQL execute in for gives error:- TypeErro

Question

I get the following error when I execute cursor in for loop.

Traceback (most recent call last):
  File "mysql_select_query.py", line 35, in <module>
    for row in cur.execute(sql_select):
TypeError: 'int' object is not iterable

Here is my code that gives error:

sql_select = "SELECT * FROM T_EMP"
for row in cur.execute(sql_select):
    print("{}, {}, {}, {}".format(row[0], row[1], row[2], row[3]))

It works well when I execute & use fetchall():

sql_select = "SELECT * FROM T_EMP where ID>%s"
rows = cur.execute(sql_select, [0])
for row in cur.fetchall() :
    print("{}, {}, {}, {}".format(row[0], row[1], row[2], row[3]))

My MySQL table schema:

mysql> desc T_EMP;
+------------+-------------+------+-----+-------------------+----------------+
| Field      | Type        | Null | Key | Default           | Extra          |
+------------+-------------+------+-----+-------------------+----------------+
| ID         | int(11)     | NO   | PRI | NULL              | auto_increment |
| CREATED    | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| EMP_CODE   | varchar(20) | YES  |     | NULL              |                |
| USER_ID    | int(11)     | YES  |     | NULL              |                |
| FIRST_NAME | varchar(50) | NO   |     | NULL              |                |
| LAST_NAME  | varchar(50) | NO   |     | NULL              |                |
| DEPT_ID    | int(11)     | YES  |     | NULL              |                |
| ADDR_ID    | int(11)     | YES  |     | NULL              |                |
+------------+-------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)

Any clue is grateful.

Thanks.

Answer 1

PyMySQL Cursor.execute returns an int, i.e. a number that tells the number of lines affected. It doesn't match the behaviour of MySQLConnector/Python, and certainly you cannot loop over a single number using a for loop.

Use fetchall like in the other example:

sql_select = "SELECT * FROM T_EMP"
cur.execute(sql_select)
for row in cur.fetchall():
    print("{}, {}, {}, {}".format(row[0], row[1], row[2], row[3]))

Or iterate from the cursor row by row:

sql_select = "SELECT * FROM T_EMP"
cur.execute(sql_select)
for row in cur:
    print("{}, {}, {}, {}".format(row[0], row[1], row[2], row[3]))

Notice also that the DBAPI requires that you create a new cursor for each query, i.e.

cur = connection.cursor()

before each cur.execute.

Answer 2

What you're doing is fundamentally wrong:

1- The SELECT statement returns data as it is but the function cur.execute() returns the iterator which is an integer, then using that integer in row[0], row[1], row[2], row[3] as if it can be iterated (which clearly can't be for a simple integer, there's no first index, second index, third index, fourth index)

2- The other function cur.fetchall() is equal to a SELECT * FROM table but more complicated because it syncs with what hasn't been used yet of that data by this cursor cur and the code that is working for you isn't but the following part (not 100% sure so test by commenting out the other two lines)

for row in cur.fetchall() :
    print("{}, {}, {}, {}".format(row[0], row[1], row[2], row[3]))

So, the best answer would be knowing what's written above and then going back to the "classroom" if you know what I mean, or at least read how to use python cursor class, peace.