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
Comments
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)”
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
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