Last week, my colleague Massimo and I discussed how to handle big result sets coming from MySQL in Python. The problem is that MySQL doesn’t support server-side cursors, so you need to select everything and then read it. You can do it either buffered or not. MySQL Connector/Python defaults to non-buffered, meaning that you need to fetch all rows after issuing a SELECT statement. You can also turn on the buffering, mimicking what MySQL for Python (MySQLdb) does.
For big result sets, it’s better to limit your search. You can do this using an integer primary key or some temporal field for example. Or you can use the LIMIT keyword. The latter solution is what is used in the MySQLCursorServerSide cursor-class. Using the SELECT it creates a temporary table from which the fetch-methods will get the information. It is something people have probably implemented in their applications, but I hope this new class will make it easier since it’s done transparently.
The code is not pushed yet, but expect it to be available in next release. Here is an example how you could use it. This code selects cities staring with Z, loops over the result getting the country (yes, this is a simple join made difficult):
cnx = db.connect(user='root', db='world')
cur = cnx.cursor()
curCity = cnx.cursor(db.cursor.MySQLCursorServerSide)
curCity.execute(
"SELECT ID,Name,CountryCode FROM City "
"WHERE NAME LIKE 'Z%' ORDER BY ID")
query = "SELECT Code, Name FROM Country WHERE CODE = %s"
for city in curCity:
cur.execute(query, (city[2],))
country = cur.fetchone()
print("{0} ({1})".format(city[1], country[1]))
cur.close()
cnx.close()
I guess the main advantage is that you can use two or more cursor objects with the same connection without the need of buffering everything in Python. On the MySQL side, the temporary table could go to disk when to big. It’s maybe slower, but keeping big result sets in memory ain’t good either.
Comments are welcome!
Comments
The main thing that confused me was that I thought MySQLdb’s SScursor was a “server-side” cursor, but it isn’t - it’s simply a non-buffered cursor such that the default cursor in MySQLdb is the Mysql Connect cursor with buffering turned on while the default Mysql Connect cursor is the MySQLdb SScursor equivalent.
This MySQLCursorServerSide class - if it were implemented - would allow for new execute commands to be called from the same connection before the MySQLCursorServerSide cursor was finished iterating through the rows which neither the default Mysql Connect cursor nor SScursor allow.
Is this correct?
Thanks!
Out of curiosity (if you can comment on roadmaps), are there any plans to bring this class or a Dictionary cursor class to mysql.connector? I recognize a Dictionary cursor class is not really a necessity (and mysql.connector has a return column name(s) function), but on occasion I’ve found it to be a nicety.