Buffering results with MySQL Connector/Python

MySQL Connector/Python doesn’t buffer results by default. This means you have to fetch the rows when you issued a SELECT. This post describes how you can change this behavior.

Why buffering result sets?

Buffering or storing the result set on the client side is handy when you, for example, would like to use multiple cursors per connection and you’de like to traverse each one interleaved.

Keep in mind that with bigger result sets, the client side will use more memory. You just need to find out for yourself what’s best. When you know result sets are mostly small, you might opt to buffer.

MySQLdb by default buffers results and you need to use a different cursor to disable it. oursql does not buffer by default. This is good to know when you start using a different MySQL DB API for Python.

Use case: 1 connection, fetch from 2 cursors

You want to fetch data from two tables and process the data using one connection. If you do it without buffering, you would run into the following:

>>> cnx = mysql.connector.connect(database='test')
>>> cur1 = cnx.cursor()
>>> cur2 = cnx.cursor()
>>> cur1.execute("SELECT c1 FROM t1")
-1
>>> cur2.execute("SELECT c1 FROM t1")
..
mysql.connector.errors.

InternalError: Unread result found.

MySQL Connector/Python offers two ways to turn buffering on or off. Either per connection or per cursor using the buffered argument set to True

Set buffering per connection

If you want all the cursors for a particular connection to be buffered, you can turn it on when connecting to MySQL setting the buffered-argument to True.

>>> import mysql.connector
>>> cnx = mysql.connector.connect(database='test',buffered=True)
>>> cur1 = cnx.cursor()
>>> cur1.__class__
<class 'mysql.connector.cursor.MySQLCursorBuffered'>
>>> cur2 = cnx.cursor()
>>> cur1.execute("SELECT c1 FROM t1")
3
>>> cur2.execute("SELECT c1 FROM t1")
3
>>> cur1.fetchone()
(u'Geert',)
>>> cur2.fetchone()

Set buffering per cursor

You can request a buffering cursor buffering from the connection object. Do this use the buffered-argument set to True:

>>> import mysql.connector
>>> cnx = mysql.connector.connect(database='test')
>>> cur1 = cnx.cursor(buffered=True)
>>> cur1.__class__
<class 'mysql.connector.cursor.MySQLCursorBuffered'>

Note: the above should work with MySQL Connector/Python v0.2 or greater