Python/MySQL execute in for gives error:- TypeError: 'int' object is not iterable

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.