LJ 34: Writing CGI Scripts in Python

#!/usr/local/bin/python
#
#       CGIGres95
#

#       This CGI script implements a general
#       query on any single PostGres95
#       table/view from an HTML form.

# Michel Vanaken (Michel.Vanaken@ping.be) - 03
# August 1996

from pg95ext import *
from pgtools import *
import cgi


def print_header( TableName ) :
    ############
	print "Content-type: text/html"
	print
	print "<HTML>"
	print "<Head><Title>Query Result"
	print "</Title></Head>"
	print "<Body><H1><em>", TableName ,"
	print "</em> Query Result</H1><hr>"


def display_query( cnx, TableName, qfields, form ) :
    #############
        print "<P><H3>Query criteria :</H3>"
        print "<UL>"
        if len( qfields ) = 0 :
             print "<LI>Everything"
        else :
             for h in qfields :
                  print "<LI>", h, " : ", cgi.escape( form[ h ] )
            print "</UL>"
                

def build_query( cnx, TableName, qfields, form ) :
    ###########
   q = "Select * from " + TableName
   if len( qfields ) > 0 :
        q = q + " where "
        order = None
        for h in qfields :
             if order is not None :
                  q = q + " and "
             else :
                  order = " order by " + h

             val = form[ h ]
             if h[ -3: ] == "num" :
	          q = q + h + " = " + val
             else :
                  # Faster than a wildcard search when indexed
?
                  q = q + h + " >= '" + val + "' and " + h +" <
'" + val[ :-1 ] + chr( ord( val[ -1 ] ) + 1 ) + "'"\
           q = q + order
      return q


def print_results( titles, res ) :
    #############
        print "<Hr>"
        print "<H3>Query result :</H3>"

        print "<Table border>"

        for t in titles :
             print "<th>", cgi.escape( t ), "</th>"

        for l in res :
             print "<tr>"
             for e in l :
                   print "<td>", cgi.escape( e ), "</td>"
              print "</tr>"

        print "</Table>"

        print "</body></HTML>"



def Main() :
    ####
     form = cgi.SvFormContentDict()

     # Work on which table ?
     if form.has_key( 'TableName' ) :
          TableName = form[ 'TableName' ]
     else :
          print "<html><body>"
          print "Query which table ?"
          print "</body></html>"
          exit

     # Build a list of used fields in the query
     fields = form.keys()
     fields.remove( 'TableName' )
     if form.has_key( 'Submit' ) :
          fields.remove( 'Submit' )
     elif form.has_key( 'submit' ) :
          fields.remove( 'submit' )
     elif form.has_key( 'SUBMIT' ) :
          fields.remove( 'SUBMIT' )


     print_header( TableName )

     # Debug
     #print fields
     #print form.values()
     #cgi.print_form( form )

     # For user information
     cnx = connect()
     display_query( cnx, TableName, fields, form )

     qs = build_query( cnx, TableName, fields, form )

     # For debugging purposes
     print "<P>", qs

     res = cnx.query( qs )
     titles = cnx.listfields()
     print_results( titles, res )


#### Start here
Main()