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

PyMySQL is a python library which can connect to MySQL database. But these days when i execute select sql command through PyMySQL, i found the execution do not return any records in the database table, but the data is really exist 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 Goal.

The goal of 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 has existed 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 above sql use PyMySQL, then you need install 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
[email protected]:~$ 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: [email protected]
License: "MIT"
Location: /home/zhaosong/.local/lib/python3.6/site-packages
Requires: 

2.2 Run SQL Use PyMySQL.

Then import PyMySQL connect, cursor class into your Python code and execute above select sql command with it.

# 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()

But then you get None from the execution result, even when you debug into the code, you can find that before cursor.fetchall() is invoked, if you add cursor.fetchall() in the eclipse PyDev expression debug window, you can see the correct result in the expression value column. But when the cursor.fetchall() function invoked, the returned result is a empty list.

READ :   How To Get Database Meta Data

pymysql cursor fetchall value in eclipse pydev debug expression window new

But 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 which user_name is ‘jerry’ the row_count = cursor.execute(sql)is still 1.

After some investigation i found this issue exist in below scenarios.

  1. Exist only when i debug above code, when i run 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.

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

(Visited 32 times, 2 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.