Spewing SQL query resultset to HTML table...

Wed Oct 10 08:07:29 EDT 2001

"Orr, Steve" <sorr at rightnow.com> wrote ...
> 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
>
The following code takes a database cursor on which you have executed a
query, with its result set optional in case you have already called
fetchall(), and prints the results as neatly as it can manage to. I have
tried it with Oracle via ODBC, and don't know of any reason why it shouldn't
work with (e.g.) DCOracle2. Hope this helps you avoid PHP!

regards
 Steve

def cpp(cursor, t = None):
    print
    d = cursor.description
    if not d:
        print "#### NO RESULTS ###\n"
        return
    names = []
    lengths = []
    rules = []
    for dd in d:
        l = dd[1]
        if not l:
            l = 12
        l = max(l, len(dd[0]))
        names.append(dd[0])
        lengths.append("%%%ss" % l)
        rules.append("-"*l)
    format = " ".join(lengths)
    print format % tuple(names)
    print format % tuple(rules)
    if not t:
        t = cursor.fetchall()
    for row in t:
        print format % row
    print
--
http://www.holdenweb.com/