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.
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.
- Exist only when i debug above code, when i run 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.
If you have found any other reasons please add your comments to this article, thanks a lot.