Getting ENUM and SET Column Information

9.7.1 Problem

You want to know what the legal members of an ENUM or SET column are.

9.7.2 Solution

Use SHOW COLUMNS to get the column definition and extract the member list from it.

9.7.3 Discussion

It's often useful to know the list of legal values for an ENUM or SET column. Suppose you want to present a web form containing a pop-up menu that has options corresponding to each legal value of an ENUM column, such as the sizes in which a garment can be ordered, or the available shipping methods for delivering a package. You could hardwire the choices into the script that generates the form, but if you alter the column later (for example, to add a new enumeration value), you introduce a discrepancy between the column and the script that uses it. If instead you look up the legal values using the table metadata, the script always produces a pop-up that contains the proper set of values. A similar approach can be used with SET columns.

To find out what values an ENUM or SET column can have, issue a SHOW COLUMNS statement for the column and look at the Type value in the result. For example, the colors column of the item table has a Type value that looks like this:

set('chartreuse','mauve','lime green','puce')

ENUM columns are similar, except that they say enum rather than set. For either column type, the allowable values can be extracted by stripping off the initial word and the parentheses, splitting at the commas, and removing the surrounding quotes from the individual values. Let's write a function get_enumorset_info( ) to break out these values from the column type definition.[3] While we're at it, we can have the function return the column's type, its default value, and whether or not values can be NULL. Then the function can be used by scripts that may need more than just the list of values. Here is a version in Python. It takes arguments representing a database connection, a table name, and a column name, and returns a dictionary with entries corresponding to the various aspects of the column definition:

[3] Feel free to come up with a less horrible function name.

def get_enumorset_info (conn, tbl_name, col_name):
 # create dictionary to hold column information
 info = { }
 try:
 cursor = conn.cursor ( )
 # escape SQL pattern characters in column name to match it literally
 col_name = re.sub (r'([%_])', r'\1', col_name)
 # this is *not* a use of placeholders
 cursor.execute ("SHOW COLUMNS FROM %s LIKE '%s'" 
 % (tbl_name, col_name))
 row = cursor.fetchone ( )
 cursor.close
 if row == None:
 return None
 except:
 return None

 info["name"] = row[0]
 # get column type string; make sure it begins with ENUM or SET
 s = row[1]
 match = re.match ("(enum|set)((.*))$", s)
 if not match:
 return None
 info["type"] = match.group (1) # column type

 # get values by splitting list at commas, then applying a
 # quote stripping function to each one
 s = re.split (",", match.group (2))
 f = lambda x: re.sub ("^'(.*)'$", "\1", x)
 info["values"] = map (f, s)

 # determine whether or not column can contain NULL values
 info["nullable"] = (row[2] == "YES")

 # get default value (None represents NULL)
 info["default"] = row[4]
 return info

The following example shows one way to access and display each element of the dictionary value returned by get_enumorset_info( ):

info = get_enumorset_info (conn, tbl_name, col_name)
print "Information for " + tbl_name + "." + col_name + ":"
if info == None:
 print "No information available (not an ENUM or SET column?)"
else:
 print "Name: " + info["name"]
 print "Type: " + info["type"]
 print "Legal values: " + string.join (info["values"], ",")
 if info["nullable"]:
 print "Nullable"
 else:
 print "Not nullable"
 if info["default"] == None:
 print "Default value: NULL"
 else:
 print "Default value: " + info["default"]

That code produces the following output for the item table colors column:

Information for item.colors:
Type: set
Legal values: chartreuse,mauve,lime green,puce
Nullable
Default value: puce

Equivalent functions for other APIs are similar. They'll come in handy in the context of generating list elements in web forms. (See Recipe 18.3 and Recipe 18.4.)