Spewing SQL query resultset to HTML table...

Tue Oct 9 17:16:37 EDT 2001

Newbie's first post... New Project... should I use Python or PHP? 

I need to take ANY valid SQL query and display the resultset in HTML. 
KEY QUESTION: How do I capture the column aliases and display them as
headers in an HTML table? I have over 100 SQL queries and I just want to use
one simple routine to display results. I don't want reams of hand code just
for output. I've already done this with PHP but I can't find a way to do it
in Python. I'm assuming it's because of my Python newbie status and not
because PHP is more capable. 
BY WAY OF EXAMPLE... here's how this was accomplished in PHP:
--------------------------------
<?php // proofofconcept.php
$db_connection=OCILogon(blah, blah, blah...); 
$SQLtext='select file_name "File Name", bytes "Bytes" from dba_data_files';
$stmt=ociparse($db_connection,$SQLtext); // Parse the SQL... 
OCIExecute($stmt,OCI_DEFAULT); // Exec & save results in associative
array... 
$ncols=OCINumCols($stmt); // Get # of columns in the select statement...
$nrows=OCIFetchStatement($stmt,&$results); //Get # rows and $results
array...   
// PRINT THE COLUMN HEADERS... KEY FEATURE!
print "<HTML><BODY><TABLE BORDER=1><TR>\n";
while (list($key,$val)=each(&$results)){print "<TH ALIGN=CENTER>$key</TH>";}

print "</TR>\n";
// Now print the resultset...
for ( $i = 0; $i < $nrows; $i++ ) { 
    reset(&$results); 
    print "<TR>\n"; 
    while ( $column = each(&$results) ) { 
        $data=$column['value'];
        $datum=$data[$i];
        if (is_numeric($datum)) {//format based on string or numeric
value...
           settype($datum,"double");
           $datum=number_format($datum);
           print   "<TD ALIGN=RIGHT>$datum</TD>\n"; 
           }// end if
           else print "<TD ALIGN=LEFT >$datum</TD>\n";
    }// end while 
    print "</TR>\n";
}// end for 
print "</HTML></BODY></TABLE>\n";
?>
--------------------------------

Not too bad for just a few lines of code. In my first pass at this project I
was able to learn PHP and achieve spectacular results in 2 weeks. As a proof
of concept that Python can do this just as easily I want to replicate what
I've already done in PHP. Then I can make an informed decision about PHP vs
Python. I'm an Oracle DBA about to develop a large database admin toolset.
I've had significant OOP experience in a "prior life" and now I'm getting
back into the development mode and need to match the language and tools with
the requirements. 

Formal Spec: 
With ANY valid SQL query, render output to an HTML table. Labels for the
HTML column headers should come from the SQL statement. Must be able to
format differently for strings and numbers with strings being left aligned
and numbers formatted with commas and right aligned. Must be able to handle
result sets with any varying number of columns and up to 10,000 rows. This
is for an Oracle database administration app so the feature set for the
database API should be richly Oracle-specific (e.g. a complete
implementation of Oracle's OCI.) Other RDBMS connectivity not needed! 

AtDhVaAnNkCsE,
Steve Orr,
Veteran Oracle DBA and Python Newbie