Geert JM Vanderkelen

Snippet: Show column information using MySQL Connector/Python

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

6 thoughts on “Snippet: Show column information using MySQL Connector/Python

  1. Craig

    Is there a way to get the column length?

    For example to get the 20 from “CREATE TABLE t1 (name VARCHAR(20), day_of_birth DATE)”

    1. Geert Vanderkelen Post author

      Craig,

      No, you can not get this information from the result of a SELECT statement. You’ll need to query the INFORMATION_SCHEMA.COLUMNS table to get what you want.

      Note that the actual length might differ. If you would do ‘SELECT CONCAT(c1, ‘more data’) AS c1 FROM t1′, then the column ‘c1′ might have more than 20 characters.

      Cheers,
      Geert

  2. Mark Janos

    Hi Geert,

    I’m executing a Stored Procedure using the cursor.callproc method, then getting the results using the cursor.stored_results method.

    When I call either cursor.description or cursor.column_names I just get None or and empty_set as a result.

    Is there any way to get the column names of the result set returned from a Stored Procedure?

    Thanks!

    Mark

Leave a Reply

Your email address will not be published. Required fields are marked *

5 + 3 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>