MySQL Connector/Python 2.0 and greater now support cursors returning dictionary or named tuples. Please check the manual for more information. This blog is, however, still relevant.
It is possible with MySQL Connector/Python to define your own cursor classes. A very good use case is to return rows as dictionary instead of tuples. This post shows how to do this using MySQL Connector/Python v1.0 and is an update for an older blog entry.
In the example below we are subclassing the
MySQLCursor class to create a new class called
MySQLCursorDict. We change the
_row_to_python() method to return a dictionary instead of a tuple. The keys of the dictionary will be (unicode) column names.
```python from pprint import pprint import mysql.connector
class MySQLCursorDict(mysql.connector.cursor.MySQLCursor): def _row_to_python(self, rowdata, desc=None): row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc) if row: return dict(zip(self.column_names, row)) return None
cnx = mysql.connector.connect(user=’root’, database=’test’) cur = cnx.cursor(cursor_class=MySQLCursorDict) cur.execute(“SELECT c1, c2 FROM t1”) rows = cur.fetchall() pprint(rows) cur.close() cnx.close() ```
The output of the above script would be (formatted):
Depending on your needs, you can subclass from any class found in the
mysql.connector.cursor module, but note that you will need to change some other methods to make it work.
If you like to leave a comment, but don't like to use Disqus, please send an email instead. Comments by email might end up as a FAQ here under.