MySQLdb.cursors.Cursor.execute returns different v

Question

See these two python code snippets,

conn = MySQLdb.connect(c['host'], c['user'], c['password'], c['db'])
cur = conn.cursor()
cur.execute("select * from geo_weathers;)   ->  **1147L**

and

conn = MySQLdb.connect(c['host'], c['user'], c['password'], c['db'], cursorclass=MySQLdb.cursors.SSCursor)
cur = conn.cursor()
cur.execute("select * from geo_weathers")   ->  **18446744073709551615L**

Why the returned number of rows are different in above two cases ? And just FYI there are 1147 rows in a table.

SSCursor is used for saving result at servers side. Is it the reason ? What all rows are affected by this select query ?

Does anyone know ?

Answer 1

The standard cursor used by MySQLdb is a stored result cursor. This means that the entire result set is transferred from the server and cached in the client's memory as a result of the execute() call.

A SSCursor is a server side cursor will only return results to the client as they are requested.

The cursor execute() call will return the result of the MySql C API function mysql_affected_rows() which in turn returns the result of mysql_num_rows() for SELECT queries.

Because results are stored server side, the client does not know how many rows have been affected until it iterates over the results.

The documentation for mysql_num_rows() says this:

The use of mysql_num_rows() depends on whether you use mysql_store_result() or mysql_use_result() to return the result set. If you use mysql_store_result(), mysql_num_rows() may be called immediately. If you use mysql_use_result(), mysql_num_rows() does not return the correct value until all the rows in the result set have been retrieved.

If you want to obtain a count of the number of rows, use SELECT COUNT(*) from geo_weathers rather than rely on the result of the execute() call.