Pymysql Cursor.fetchall() / Fetchone() Returns None

PyMySQL is a python library that can connect to the MySQL database. But these days when I execute the select SQL command through PyMySQL, I found the execution does not return any records from the database table, but the data really exists in the database table. And when I run the SQL command in the database directly, the SQL can execute successfully and the table data can be returned correctly. Below is the reason I found in my case.

1. Select SQL Command Purpose.

The goal of the below SQL command is to return the count number of existed records whose user_name is ‘jerry’, if the count number is bigger than 0 that means the record with the condition exists in the database table, then the code will not insert a new record into MySQL database table. But if the returned count number is 0 that means the data do not exist in the table, then we can insert it into the MySQL database table.

"select count(id) as count from dev2qa_example.user_account where lower(user_name) = 'jerry'"

2. How To Execute Above SQL Use PyMySQL.

2.1 Install PyMySQL Library.

If you want to execute the above SQL use PyMySQL, then you need to install the PyMySQL library first like below.

:~$ pip3 install PyMySQL
Collecting PyMySQL
  Downloading https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl (47kB)
    100% |████████████████████████████████| 51kB 152kB/s 
Installing collected packages: PyMySQL
Successfully installed PyMySQL-0.9.3
zhaosong@zhaosong-VirtualBox:~$ pip3 show PyMySQL
Name: PyMySQL
Version: 0.9.3
Summary: Pure Python MySQL Driver
Home-page: https://github.com/PyMySQL/PyMySQL/
Author: yutaka.matsubara
Author-email: yutaka.matsubara@gmail.com
License: "MIT"
Location: /home/zhaosong/.local/lib/python3.6/site-packages
Requires: 

2.2 Run SQL Use PyMySQL.

Now import PyMySQL module connect, cursors class into your Python code and execute the above select SQL command with it. You can see the below source code.

# import pymysql connect and cursors class.
from pymysql import connect, cursors

# get mysql connection object.
conn = connect(host='127.0.0.1', user='root', password='root', db='dev2qa_example', charset='utf8', cursorclass=cursors.DictCursor)

try:
    # get database cursor object.
    with conn.cursor() as cursor:
         # execute select sql command.
         row_count = cursor.execute(sql)
         print('select return row count = ' + str(row_count))
         # get the execution result and print it out.
         row = cursor.fetchall()
         print(row)
finally:
   # do not forget close mysql connection at the end of the code.
   conn.close()

When you run the above python source code, you will get None from the execution result. When you debug the source code, you can find that if you add cursor.fetchall() in the eclipse PyDev Expressions debug window before cursor.fetchall() is invoked, you can see the row_count value is the correct result value (1) in the Expressions value column. But when you continue to run the cursor.fetchall() function, the returned result is an empty list.

And the row count returned by row_count = cursor.execute(sql)code is also not the record count number whose user_name is ‘jerry’. It is just the execution returned row count, if there are two or more records whose user_name is ‘jerry’ the row_count = cursor.execute(sql)is still 1.

After some investigation, I found this issue exists in the below scenarios.

1. Exist only when I debug the above code, when I run the above code directly, the result is correct.
2. Miss understanding of cursor.fetchall() function, the function will move to the last row after being invoked. And the result will be saved in the temporary variable row.
3. This is a bug of PyMySQL or MySQL, as the StackOverflow topic referred to.

If you have found any other reasons please add your comments to this article, thanks a lot.