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 [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.
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.
- Exist only when I debug the above code, when I run the above code directly, the result is correct.
- 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.
- 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.