MySQL Connector/Python 2.1.1 took a while to release and that was because we had to add some more packages which contains the optional C Extension. Note that this is still Alpha and we want you guys to report any problems and requests.

The Connector/Python C Extension was added because in certain situations, for example reading a huge result set, can take a long time with pure Python. That’s why we choose to interface with Connector/C (libmysqlclient).

Note: Pure Python is still default and it will be kept that way!

Installing Connector/Python 2.1 didn’t change much:

$ sudo python setup.py install

If you’d like the C Extension, you have to first install MySQL Connector/C or have the MySQL Server development packages available. Careful with mixing 32 and 64-bit: make sure Python matches your MySQL libraries. Connector/Python will try to detect the mismatch and notify you.

For example, on OS X with development tools installed, I would do the following:

$ virtualenv CPYENV
$ source CPYENV/bin/activate
$ tar xzf ~/Downloads/mysql-connector-c-6.1.5-osx10.7-x86_64.tar.gz
$ tar xzf ~/Downloads/mysql-connector-python-2.1.1.tar.gz
$ cd mysql-connector-2.1.1
$ python setup.py install --with-mysql-capi=../mysql-connector-c-6.1.5-osx10.7-x86_64

If all goes well, the above would have compiled and install the C Extension together with the pure Python code inside a virtual environment. Here is how you can check if the C Extension is available:

import mysql.connector
mysql.connector.HAVE_CEXT

# Output == True

If you want to see the speed improvements, you can load up the employees sample database and do the following in the Python interpreter:

import mysql.connector
cnx = mysql.connector.connect(user='root', database='employees')
cnxc = mysql.connector.connect(use_pure=False, user='root', database='employees')
cur = cnx.cursor()
q = "SELECT * FROM salaries"
s=time(); cur.execute(q); r=cur.fetchall(); print("%.2f" % (time()-s))

# Output ~= 65.57

cur = cnxc.cursor()
s=time(); cur.execute(q); r=cur.fetchall(); print("%.2f" % (time()-s))

# Output ~= 13.09

That’s 66 seconds vs. 13 seconds using the C Extension.

If that is not fast enough, and it is not, you can directly load the C Extension and use the wrapper around the MySQL C API (see manual). Here’s an example:

import _mysql_connector
cnx = _mysql_connector.MySQL()
cnx.connect(user='root', database='employees')
cnx.query("SELECT emp_no, last_name, hire_date FROM employees")
print(cnx.fetch_row())
cnx.free_result()
cnx.close()

# Output == (10001, 'Facello', datetime.date(1986, 6, 26))

It is a bit different than using mysql.connector, but notice that result coming from the C Extension is also converted to Python data types.

How fast is using _mysql_connector? Lets say we want the raw data, save the following to a Python script file and execute:

from time import time

import _mysql_connector

cnx = _mysql_connector.MySQL(raw=True)
cnx.connect(user='root', database='employees')
cnx.query("SELECT * FROM salaries")
s = time()
row = cnx.fetch_row()

while row:
  row = cnx.fetch_row()

cnx.free_result()
print("All fetched in %.2fs" % (time() - s))

cnx.close()

# Output ~= All fetched in 2.25s

If you put it all together, and this is not scientific, just on my OS X MacBook, SELECT * FORM slaries:

If you want to dump big sets of data, and you want to do it the Python way, you can use the C Extension to get it faster.

Yes, the C Extension works and compiles on Windows!