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
 
20.9.2014 - We have just updated our course layouts and descriptions and added our 2015 schedule.

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()
   try:
       cur.execute(sql)
       rows = cur.fetchall()
       s.commit
   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)
   else:

# 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:
           clear_windows_screen()
#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:

Code:
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


Runs

Code:
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))

   try:
       complete_set=col_names+result
   except:
       complete_set=col_names
   
   formatter = ("%-9s","%-11s","%-9s","%-21s","%-19s","%-20s","%-0s")

   # call the generic data set print function
   print_set(complete_set,formatter)
   
   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 www.opentalk.org.uk 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., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho