Snippet: Show column information using MySQL Connector/Python

Problem

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

Solution

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] = []
    columns.append(coldesc)
    namesize = len(coldesc[0])
    if namesize > maxnamesize:
        maxnamesize = namesize

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

Discussion

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:

FieldFlag.get_info(colflags)

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

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 https://github.com/downloads/libevent/libevent/libevent-2.0.21-stable.tar.gz
(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 = '127.0.0.1',
  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()
pprint(rows)
cur.close()
cnx.close()

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.

Debugging MySQL Cluster installed using RPMs using gdb

This post explains how to debug MySQL Cluster 7.1, installed using the RPM packages, using gdb on a Linux box (Red Hat, Oracle Enterprise Linux, CentOS, ..).

When a data node crashes lots of information goes into the error log, trace files and out log. However, it makes sometimes sense when you can repeat the crash, to run the data node in debug mode, or using gdb.

First, using RPMs and a Linux distribution, make sure you have the ‘debuginfo’ package installed. For example, for Red Hat or Oracle Enterprise Linux on a 64-bit machine, this package would be called: MySQL-Cluster-gpl-debuginfo-7.1.15-1.rhel5.x86_64.rpm .

Create a file with the following commands, we will name it ‘ndbd.gdb’:

set pagination off
set logging overwrite on
set logging file ndbd_gdb_backtrace.txt
set logging on
run --foreground -c <YourMGM:1186> --ndb-nodeid=<YourID>
thread apply all bt
set logging off

Note line 5: pass the options to ‘run’ which you usually pass when starting ndbd, but leave the –foreground option.
Note line 3: you can save of course the logging file wherever you want to.

Then all you need to do is run gdb with the commands file and the ndbd binary you just created:

shell> gdb /usr/sbin/ndbd -x ndbd.gdb

A full backtrace of threads will be available in the logging file when ndbd crashes. This you can then upload when reporting bugs.

There are probably more options and things you can do, please post them in the comments!