0

I am learning python and trying to use it to create a PDF report based on the results of a query from MySQL database. This is the part of coding I am having trouble with

qry = "SELECT <irrelevant>" 

cursor.execute(qry)
result = cursor.fetchone()
if result is not None:
    result = cursor.fetchall()
    numrows = cursor.rowcount
      return result, numrows

This should have yielded 3 results, as shown in the screenshot when the SQL query was run directly on the database as shown in screenshot 1. enter image description here

However, when the python script is run it only retrieves two rows as shown below. enter image description here

Interestingly, the cursor.rowcount returns 3 although only two records are shown. I would love to know what I am doing wrong. Thank you.

Masklinn
  • 34,759
  • 3
  • 38
  • 57
Utopiasl
  • 1
  • 1
  • 1
    You call `result = cursor.fetchone()` giving you the first row and then you call `result = cursor.fetchall()` which fetches the remaining two. Why select a single result first? – Iain Shelvington Apr 06 '22 at 02:55
  • I was following a guide, which suggested to check if the result set is empty before proceeding as otherwise it will generate an error. I was under the impression that fetchall() would retrieve all results rather than the point from which fetchone(). I am using the MySQLdb module in Python. https://stackoverflow.com/questions/16561362/how-to-check-if-a-result-set-is-empty – Utopiasl Apr 06 '22 at 03:03
  • The question you link to is about 1. sqlite peculiarities in 2. the case of side-effect queries (without `RETURNING`). Such queries don't have results to return, and the dbapi2 leaves the behaviour ill-defined, as a result some drivers don't just default to an empty record set, but *no record set*. This is not an issue for a `SELECT` query, they always return a record set (possibly empty). – Masklinn Apr 06 '22 at 06:02
  • Furthermore as one of the answers notes, mysqldb specifically extends dbapi2 by having `execute` directly return the number of affected rows (do note that this is absolutely not portable, psycopg2 returns the cursor object instead). – Masklinn Apr 06 '22 at 06:06

1 Answers1

0

You fetch one result, which you do not use, with

result = cursor.fetchone()

you then fetch and use the other rows

if result is not None:             
   result = cursor.fetchall()          
   numrows = cursor.rowcount          
      return result, numrows

but the first row has been discarded
you could modify to

qry = "SELECT <irrelevant>" 

cursor.execute(qry)
result = cursor.fetchall()
if result is not None:
    numrows = cursor.rowcount
      return result, numrows

or to preserve your line

qry = "SELECT <irrelevant>" 

cursor.execute(qry)
result = cursor.fetchone()
if result is not None:
    result += cursor.fetchall()
    numrows = cursor.rowcount
      return result, numrows
  • Or just `cursor.fetchall()` to start with, the first fetch seems entirely unnecessary since you're fetching the results anyway, if there are any. – Masklinn Apr 06 '22 at 05:58
  • Thank you @Masklinn for guiding me. You were absolutely correct. – Utopiasl Apr 06 '22 at 07:19