Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Employee Snippet: Show column information using MySQL Connector/Python
+1 Vote Up -0 Vote Down

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

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.