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 = { }
 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 ( )
 if row == None:
 return None
 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"] = (1) # column type

 # get values by splitting list at commas, then applying a
 # quote stripping function to each one
 s = re.split (",", (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?)"
 print "Name: " + info["name"]
 print "Type: " + info["type"]
 print "Legal values: " + string.join (info["values"], ",")
 if info["nullable"]:
 print "Nullable"
 print "Not nullable"
 if info["default"] == None:
 print "Default value: NULL"
 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
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.)

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: