Problem
You have a page with widgets and data and you absolutely despise having to recreate the entire page contents every time the UI is engaged. You hate this because some of your server side data retrieval processes are complicated and time consuming and it's just dumb to constantly re-execute the same procedures every time the user does something trivial. Your page is data driven and you want a richer UI than is possible with straight HTML. While you are retrieving data asynchronously you want to inform the user so they don't compound the stupidity by clicking the reload button. You want to get smart with AJAX.
Example: You have two SELECT inputs and the contents of the second input is dynamic and should change when an option in the first input is selected. AKA, The Double Drop Down Dilemma. Or Double Combo Confusion. Furthermore, you would like to display the results of a database query in an HTML table based on the SELECT OPTION inputs.
Solution
Recipe provided by D. B. Dweeb alias Steve Orr.
This recipe contains the following ingredients:
- Quixote (Duh! I'm using Quixote with mod_python.)
JavaScript. Why? Because it's the only way to dynamically manipulate "The DOM".
MochiKit, a lightweight Javascript library which "makes Javascript suck less."
JSON. I like it because it's less verbose than XML.
cx_Oracle for database access. (Need demo for MySQL and PostgreSQL)
These ingredients are mixed in the following files:
http.conf
__init__.py
mochi.py
mochi.css
mochi.js
http.conf
1 <LocationMatch "^/mochi(/|$)">
2 SetHandler python-program
3 PythonHandler quixote.server.mod_python_handler
4 PythonOption quixote-publisher-factory mochi.create_publisher
5 PythonInterpreter mochi
6 PythonDebug On
7 </LocationMatch>
8
__init__.py
1 import mochi
2 def create_publisher():
3 return mochi.create_publisher()
4
mochi.py
1 from quixote.directory import Directory
2 from quixote.util import StaticDirectory
3 from quixote.publish import Publisher
4 from quixote.errors import TraversalError
5 import cx_Oracle
6
7 def create_publisher():
8 return Publisher(MyRoot())
9
10 TEMPLATE = """\
11 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN">
12 <html><head><title>AJAX with Quixote and MochiKit</title>
13 <link href="./mylib/mochi.css" rel="stylesheet" type="text/css" />
14 <script type="text/javascript" src="/lib/MochiKit/MochiKit.js"></script>
15 <script type="text/javascript" src="./mylib/mochi.js"></script>
16 </head>
17 <body>
18 <div id="userlabel">User Name: </div>
19 <div id="userselect"></div>
20 <div id="tablelabel">Table Name:</div>
21 <div id="tblselect"></div>
22 <BR /><BR />
23 <div id="getdata">Please wait. Getting data...</div>
24 <div id="dbtablecolumns"></div>
25 </body></html>"""
26
27 class MyRoot(Directory):
28 mylib = StaticDirectory('/hydra/cgi-bin/mochi2/mylib')
29 _q_exports = ['', 'mylib', 'getTableColumns', 'getUsers', 'getUserTables']
30 def __init__(self):
31 self.conn = cx_Oracle.connect('username/pw@tnslabel')
32 self.curs = self.conn.cursor()
33 def _q_index(self):
34 return TEMPLATE
35 def _q_traverse(self, path): # override to process 2 args
36 component = path[0]
37 path = path[1:]
38 name = self._q_translate(component)
39 if name is not None:
40 obj = getattr(self, name)
41 else:
42 obj = self._q_lookup(component, *path)
43 path = None
44 if obj is None:
45 raise TraversalError(private_msg=('directory %r has no component '
46 '%r' % (self, component)))
47 if path: return obj._q_traverse(path)
48 elif callable(obj): return obj()
49 else: return obj
50 obj = self._q_lookup(*path)
51 path = None
52 def _q_lookup(self, user, table=None):
53 if user and table: return self.getFirst100(ownerArg=user, tblArg=table)
54 elif user: return self.getUserTables(ownerArg=user)
55 def renderJSON(self, cursor, headers=True): # return JSON format...
56 if headers: return dict(headers=[x[0] for x in cursor.description],
57 data=[list(x) for x in cursor.fetchall()])
58 else: return dict(data=[list(x) for x in cursor.fetchall()])
59 def getUsers(self):
60 sql = """select username "value", username "text"
61 from all_users ORDER BY username"""
62 self.curs.execute(sql)
63 return self.renderJSON(self.curs, headers=False)
64 def getUserTables(self, ownerArg):
65 sql = """select table_name "value", table_name "text"
66 from all_tables WHERE owner = UPPER(:ownr)
67 ORDER BY table_name"""
68 self.curs.execute(sql, ownr=ownerArg)
69 return self.renderJSON(self.curs, headers=False)
70 def getFirst100(self, ownerArg, tblArg):
71 sql = """SELECT * FROM %s.%s WHERE rownum < 101""" % (ownerArg, tblArg)
72 self.curs.execute(sql)
73 return self.renderJSON(self.curs, headers=True)
74
mochi.css
1 table.datagrid {
2 border-spacing: 0px 0px;
3 border-style: outset;
4 border-width: 0px 0px 0px 1px;
5 border-color: gray gray gray gray;
6 border-collapse: separate;
7 }
8
9 table.datagrid thead th a {
10 color: white;
11 text-decoration: none;
12 font-size: 12px;
13 background-repeat: no-repeat;
14 background-position: center right;
15 padding-right: 15px;
16 }
17
18 table.datagrid thead th.over {
19 background-color: #dde;
20 cursor: pointer;
21 }
22
23 table.datagrid thead th {
24 text-align: left;
25 background-color: #AAE;
26 font-size: 14px;
27 color: black;
28 font-weight: bold;
29 border-width: 0px 1px 0px 0px;
30 padding: 0px 4px 0px 4px;
31 border-style: inset inset inset inset;
32 border-color: gray white gray gray;
33 }
34
35 table.datagrid tbody td {
36 text-align: left;
37 font-size: 11px;
38 border-width: 0px 1px 1px 0px;
39 padding: 0px 2px 0px 2px;
40 border-style: inset inset inset inset;
41 border-color: gray gray gray gray;
42 background-color: white;
43 ;
44 }
45 table.datagrid tbody tr.alternate td {
46 text-align: left;
47 font-size: 11px;
48 border-width: 0px 1px 1px 0px;
49 padding: 0px 2px 0px 2px;
50 border-style: inset inset inset inset;
51 border-color: white white white white;
52 background-color: #f7f7f7;
53 }
54
mochi.js
1 hdr_display = function (row) {
2 return TR({'class': 'datagrid'}, map(partial(TH, null), row));
3 };
4 opt_display = function (row) {
5 return OPTION({"value":row[0]}, row[1]);
6 };
7 function showTableData(result) {
8 var styleList = ['alternate','datagrid'];
9 var iterStyle = cycle(styleList);
10 var tblRow = function(row) {
11 return TR({'class': iterStyle.next()}, map(partial(TD, null), row));
12 };
13 var html_table=TABLE({'class': 'datagrid', "id": "dbtablecolumns"},
14 THEAD({'class': 'datagrid'},
15 hdr_display(result["headers"])),
16 TBODY(null, map(tblRow, result["data"])));
17 swapDOM("dbtablecolumns", html_table);
18 showElement("dbtablecolumns");
19 hideElement("getdata");
20 };
21 function showSelectData(result) {
22 hideElement("tablelabel");
23 var mydata = concat([[-1,'-choose user-']], result["data"]);
24 var html_select=SELECT({"onchange": "popTables(this.value);",
25 "id": "userselect"},
26 map(opt_display, mydata));
27 swapDOM("userselect", html_select);
28 };
29 function showSelectData2(result) {
30 var mydata = concat([[-1,'--']], result["data"]);
31 var html_select=SELECT({"onchange": "showColumns(this.value)",
32 "id": "tblselect"},
33 map(opt_display, mydata));
34 swapDOM("tblselect", html_select);
35 };
36 function showError(e) {
37 logError(map(function (kv) {
38 return ' ' + kv.join(": ");
39 }, sorted(items(e))).join(""));
40 logger.debuggingBookmarklet();
41 };
42 function popTables(ownerName) {
43 showElement("tablelabel");
44 hideElement("dbtablecolumns");
45 var url = "./" + ownerName;
46 var d = loadJSONDoc(url);
47 d.addCallback(showSelectData2);
48 d.addErrback(showError);
49 };
50 function showColumns(tableName) {
51 hideElement("dbtablecolumns");
52 showElement("getdata");
53 var uname = getElement("userselect").value;
54 var url = './' + uname + '/' + tableName;
55 var d = loadJSONDoc(url);
56 d.addCallback(showTableData);
57 d.addErrback(showError);
58 };
59 addLoadEvent(function() {
60 hideElement("getdata");
61 var d = loadJSONDoc("./getUsers");
62 d.addCallback(showSelectData);
63 d.addErrback(showError);
64 });
65
Discussion
Other than the fact that the above code is self-documenting and intuitively obvious, are there any questions? 
Download MochiKit and make the JavaScript accessible. Then create your Python, CSS, and JavaScript source.
After you get a working demo of the above code and you can see fully populated objects on the page, do a "view source" from the browser to see the HTML. The only thing visible from here will be the div tags as appeared in the initial TEMPLATE text. To see the data you need to get into the DOM. To see the contents of the page use the "DOM Inspector" with Firefox/Mozilla. Anything that's in the DOM can be manipulated with JavaScript. This is where MochiKit comes in.
I like MochiKit because it's written by Pythonistas who think Pythonically. I also like it because it uses the concept of "deferreds" as in Twisted. Finally, I like it because it's humble claim to fame is to make JavaScript "suck less." Writing supportable and well engineered JavaScript is one of the key challenges with AJAX and a good set of JavaScript libraries is needed. To do AJAX well you have to do JavaScript well. Since I've just started learning JavaScript I'm making a New Year's resolution to imbibe more of this koolaid and enhance my web UI's.
Here's the sequence of events on this page: The addLoadEvent function asynchronously populates the first SELECT input and creates it with an onchange event which then triggers the asynchronous population of the second SELECT input via the popTables function. This second SELECT input also has an onchange event which triggers the asynchronous population of the HTML table via the showTableData function. Notice also that some of the text and HTML objects appear and disappear according to UI events and completion of the retrieval of server data. For busy pages this helps to make the application appear more responsive.
The data from cx_Oracle is returned as a list of tuples and this needs to be transformed into a list of lists for the JSON format. If you use MySQLdb you'll need to transform its tuple of tuples result set into a list of lists. Here's a project: Create a Service Oriented Architecture where all your data is processed by a single URL and make all your database calls to it. Give each query a name, pass any necessary parameters, and return the results in JSON or XML.
BUG: Please note that the above craps out on Oracle tables containing columns with date datatypes. This is a database specific issue so I won't bother with a fix here.