Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
Python, Lua and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Java, C, C++, Ruby, MySQL and Linux / Tomcat systems
Formatting SQL output

Posted by cloughie (cloughie), 29 January 2008
I'm using the standard odbc calls to retrieve rows from a table, but the formatting of those supplied rows is very awkward.

What I would like to display is a set of column names along with data underneath them.

I've just knocked up a really ungainly example, just to show how far I am from a generic solution !

Surely there is an easier, more generic way of displaying (formatted) table data ?

# Here's the basic function to get the data for supplied #sql
def getdata(s,sql):
   cur = s.cursor()
       rows = cur.fetchall()
   except: rows = ''
   return rows

# Here's the function that will be invoked from a menu
def display_archive_set(input_connection):

   s = input_connection
   arch_set ="ARS"

   # getdata is a call to odbc for the sql returned from a dictionary of sql statements in general_sql()
   result = getdata(s,general_sql().get(arch_set))
   if result == '':
       returning('No data found',1.5)

# print out one line of column headings        print 'SET'+'\t'+'DB_NM'+'\t'+'YRS_ONLINE'+'\t'+'PAR_TAB'+(21-len('PAR_TAB'))*' '+'PI_NM'+(19-len('PI_NM'))*' '+'BUS_KEY'+(19-len('BUS_KEY'))*' '+'EXTR_COL'
       print 100 *'*'
       for row_entry in result:
#move the data into the appropriate column #name          
           ARCH_SET = row_entry[0].strip()
           DB_NM = row_entry[1].strip()
           YRS_ONLINE = str(row_entry[2])
           PAR_TAB = row_entry[3].strip()
           PI_NM = row_entry[4].strip()
           BUS_KEY = row_entry[5].strip()
           EXTR_COL = row_entry[6].strip()

# print the actual data to the screen
           print ARCH_SET+'\t'+DB_NM+'\t'+YRS_ONLINE+'\t'+PAR_TAB+(21-len(PAR_TAB))*' '+PI_NM+(19-len(PI_NM))*' '+BUS_KEY+(19-len(BUS_KEY))*' '+EXTR_COL

   raw_input ('Press Enter to continue.')

Posted by admin (Graham Ellis), 30 January 2008
How about this:

result = [["hello","Australia",16,17,"A test"],\
       ["Good Day","Guildford",5,5000,"Comment"]]

formatter = ("%-12s","%-15s","%4d","%6d"," - %s")

for rows in result:
  row = ""
  for col in range(len(formatter)):
     row += formatter[col] % rows[col]
  print row


earth-wind-and-fire:~ grahamellis$ python pyf
hello       Australia        16    17 - A test
Good Day    Guildford         5  5000 - Comment
earth-wind-and-fire:~ grahamellis$

[Display may not look great due to tabbing on the forum, but try it out - it works with a fixed width font!)

Posted by cloughie (cloughie), 30 January 2008
Well, I think you'd agree, it's a little contrived but it's MUCH,MUCH better than my (horrible) solution.

Armed with your generic approach, I've now recoded it, thus :

def print_set(in_set,formatter):
  for rows in in_set:
      row = ""
      for col in range(len(formatter)):
          row += formatter[col] % str(rows[col]).strip()
      print row

def display_archive_set(input_connection):

   s = input_connection
   arch_set ="ARS"
   col_names = [["ARCH_SET","DB_NM","YRS_OLNE","PAR_TAB","PI_NM","BUS_KEY","EXTR_COL"],

   # get the data from the database
   result = getdata(s,general_sql().get(arch_set))

   formatter = ("%-9s","%-11s","%-9s","%-21s","%-19s","%-20s","%-0s")

   # call the generic data set print function
   raw_input ('Press Enter to continue.')

As long as the windows sufficiently wide to display the data, it works a treat !

Thanks again!

Posted by admin (Graham Ellis), 30 January 2008
on 01/30/08 at 11:57:50, cloughie wrote:
Well, I think you'd agree, it's a little contrived ....

Posting at that time of the morning ... even a contrived answer stretched me ... glad it was of use

This page is a thread posted to the opentalk forum at and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2018: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01225 708225 • FAX: 01225 793803 • EMAIL: • WEB: • SKYPE: wellho