PyMySQL execute()返回值异常：为何出现超大数值而非预期行数？

Great question! Let's break down what's happening here:

First, that huge number 18446744073709551615 isn't a random error—it's the maximum value for an unsigned 64-bit integer, which translates to -1 when treated as a signed integer. PyMySQL uses this value as a special marker specifically for server-side cursors like SSCursor.

Why this happens with SSCursor #

Server-side cursors work very differently from regular client-side cursors:

• With a standard cursor (like DictCursor), PyMySQL pulls the entire result set from the MySQL server into your client's memory right after running the query. This lets it count rows easily, so execute() returns the actual number of rows.
• With SSCursor, the full result set stays on the MySQL server. The cursor only fetches rows in small batches (or one at a time) when you call methods like fetchone() or fetchmany(). Since the complete dataset isn't loaded into client memory during execute(), PyMySQL can't know the total row count at that moment. So it returns that uint64 max value to signal "row count isn't available yet".

How to get the actual row count #

If you need the total number of rows, you have two practical options:

1. Run a COUNT query first (most efficient for large datasets):

   import pymysql
   from pymysql.cursors import SSCursor
   
   conn = pymysql.connect(
       host='localhost',
       user='your_user',
       password='your_password',
       db='your_db',
       cursorclass=SSCursor
   )
   
   try:
       with conn.cursor() as cursor:
           # Fetch total row count first
           cursor.execute("SELECT COUNT(*) FROM your_table")
           total_rows = cursor.fetchone()[0]
           print(f"Total rows: {total_rows}")  # Will output 205299
   
           # Run your actual SELECT query
           cursor.execute("SELECT * FROM your_table")
           # Process rows incrementally
           row = cursor.fetchone()
           while row:
               # Handle your row data here
               row = cursor.fetchone()
   finally:
       conn.close()
   

2. Fetch all rows and count (not recommended for massive datasets, as it loads everything into memory):

   try:
       with conn.cursor() as cursor:
           cursor.execute("SELECT * FROM your_table")
           all_rows = cursor.fetchall()
           print(f"Total rows: {len(all_rows)}")  # Will output 205299
   finally:
       conn.close()
   

Key takeaway #

This isn't an exception or bug—it's intentional behavior for server-side cursors, which are built to handle large result sets without hogging client memory. The returned value is just PyMySQL's way of telling you it can't provide the row count at execute time.

内容的提问来源于stack exchange，提问作者Kate Hanahoe