SSCursor in MySQL

There are four kinds of cursors available in MySQL client side programming. In this article, I am going to introduce the difference between Cursor the normal one and SSCursor the server side one and how to use SSCursor.
First, let's see the definitions of those four cursors in MySQLdb source code.

class Cursor(CursorStoreResultMixIn, CursorTupleRowsMixIn,
             BaseCursor):

    """This is the standard Cursor class that returns rows as tuples
    and stores the result set in the client."""


class DictCursor(CursorStoreResultMixIn, CursorDictRowsMixIn,
                 BaseCursor):

     """This is a Cursor class that returns rows as dictionaries and
    stores the result set in the client."""


class SSCursor(CursorUseResultMixIn, CursorTupleRowsMixIn,
               BaseCursor):

    """This is a Cursor class that returns rows as tuples and stores
    the result set in the server."""


class SSDictCursor(CursorUseResultMixIn, CursorDictRowsMixIn,
                   BaseCursor):

    """This is a Cursor class that returns rows as dictionaries and
    stores the result set in the server."""

In fact, there are two orthogonal features of cursors in MySQLdb. CursorDictRowsMixIn/CursorTupleRowsMixIn controls result type and CursorStoreResultMixIn/CursorUseResultMixIn which we will focus on in this article controls the result will be store in server or client.
With the definitions, we know that SSCursor stores result in the server rather than in the client.

server side VS client side

Two simple flows show how they works.

• client side

  # mysql_store_result
  while(True):
    result = produce_part_result() # find part of results that can be filled into a network package
    fill_network_package(result)
    send_network_package

• server side

  # mysql_use_result
  while(True):
    result = produce_part_result() # find part of results that can be filled into a network package

    wait_until_the_client_invoke_`mysql_fetch_row()`

    fill_network_package(result)
    send_network_package

In fact, the MySQL server will not store results in server even if a SSCursor is used, it will produce the part of results before fill them into the network packages. So don't worry the memory use of the server when use SSCursor.

how to use SSCursor

  cur = conn.cursor(MySQLdb.cursors.SSCursor)

Here is a trap that commit should be invoked after all of the results have been sent to client. Otherwise, 2014, "Commands out of sync; you can't run this command now" error will be raised.

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order. This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

commit is also a query, or we can say request, so the program should read all of the results then invoke commit.

advantages and disadvantages of using SSCursor

• Less memory use in the client.
• Get the first row more quickly.
• The whole results sending will be slower.