MySQL Connector/Python on GitHub

Last week we released Connector/Python v2.0 (alpha); today we publish the source on GitHub. Yes, we are using Git internally and are now able to push it out on each release. Previous versions are still available through LaunchPad.

Here is the full process to get Connector/Python installed in a virtual environment. You’ll need Git installed of course.

shell> git clone cpy
shell> virtualenv ENVCPY
shell> source ENVCPY/bin/activate
(ENVCPY)shell> cd cpy
(ENVCPY)shell> python install
(ENVCPY)shell> python
>>> import mysql.connector
>>> mysql.connector.__version__

Please report issues and feature request through the MySQL Bugs System.

Snippet: Show column information using MySQL Connector/Python


You have a query executed by MySQL Connector/Python and would like to show column information nicely on the console.


Every cursor object has a description property. This can be used to show information about the columns in a result set.

columns = []
maxnamesize = 0
for coldesc in cur.description:
    coldesc = list(coldesc)
    coldesc[2:6] = []
    namesize = len(coldesc[0])
    if namesize > maxnamesize:
        maxnamesize = namesize

fmt = "{{nr:3}} {{name:{0}}} {{type:12}} {{null}}".format(
colnr = 1
for column in columns:
    (colname, fieldtype, nullok, colflags) = column
        null='NOT NULL' if nullok else 'NULL',
    colnr += 1


There are two parts in the code snippet. The for-loop goes over the columns and saves the information we need. We also figure out the longest column name. (Yes, we could put the column name at the end but then it would not be much fun.)

On line 4 we use the cursor description property. It returns a list of tuples which contains information about the column in the result set. The information is defined by PEP-249, though only the first 2 items, name and column type, are mandatory. The last field is kind of a MySQL extension returning the flags of the column. On line 20 we fetch the name of the column data type using the FieldType class.

If you’d like to get information about the column flags, import and use FieldFlag:


Below is example of output, for a table created using CREATE TABLE t1 (name VARCHAR(20), day_of_birth DATE).

  1 name          VAR_STRING   NOT NULL
  2 day_of_birth  DATE         NOT NULL

Installing gevent inside a virtual environment on OSX

Installing Python’s gevent package can be a bit challanging. This blog
post explains how to install it on OSX v10.8 or later without using something like ‘MacPorts‘.

You will need to get XCode from Apple’s App Store and make sure to install the
Command Line Tools‘. You do this under Preferences>Downloads (Using XCode 5 you don’t have to do that anymore, apparently).

Installing virtualenv can be done using easy_install (or pip if available):

shell> easy_install virtualenv

Here’s how you can use virtualenv:

shell> mkdir MyExample
shell> cd MyExample
shell> virtualenv ENV
shell> source ENV/bin/activate

The virtualenv package comes with pip, so we’ll use this instead of easy_install.

We need to install the C-library libevent. Download the latest version and do the following while in the virtual environment:

(ENV)shell> curl -L -O
(ENV)shell> tar xzf libevent-2.0.21-stable.tar.gz
(ENV)shell> cd libevent-2.0.21-stable
(ENV)shell> ./configure --prefix="$VIRTUAL_ENV"
(ENV)shell> make && make install
(ENV)shell> cd $VIRTUAL_ENV/..

If all went well, install the Pyton packages greenlet and gevent. Note that the order is important.

(ENV)shell> pip install greenlet
(ENV)shell> pip install gevent --global-option "-L$VIRTUAL_ENV/lib" \
    --global-option "-I$VIRTUAL_ENV/include"

The order is important because gevent depends on greenlet. If you install gevent first, the extra global options will not work and you’ll get an error.

Try to import gevent and if all went well, no error should raise:

shell> python
python> import gevent

Using OSX ‘Mavericks’ v10.9-beta you need XCode v5 Developer Preview to be able to compile libevent.

Using Connector/Python with SQLAlchemy

SQLAchemy has support for MySQL Connector/Python for a while now. Here is a little HOWTO showing how install both, and setup a database engine.

There are multiple ways of installing both projects, but here is the simplest using pip, whatever platform you use:

shell> pip install SQLAlchemy
shell> pip install mysql-connector-python 

Start your SQLAlchemy engines using a URL pointing to Connector/Python. Note the connect_args argument which passes extra connection arguments to Connector/Python. In the following example we set the MySQL session variable time_zone to UTC:

from sqlalchemy import create_engine

DB_URI = "mysql+mysqlconnector://{user}:{password}@{host}:{port}/{db}"

engine = create_engine(DB_URI.format(
  user ='sakila',
  password = 'yoursecret',
  host = '',
  db = 'test'),
  connect_args = {'time_zone': '+00:00'}

That’s it. Now just continue with SQLAlchemy as usual.

Fetching rows as dictionaries with MySQL Connector/Python (revised)

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.

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()

The output of the above script would be (formatted):

 {u'c1': 1,
  u'c2': 10},
 {u'c1': 2,
  u'c2': 20}

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.