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

Phone System reconfiguration 11.1.2018 to 26.1.2018 - we are on email but incoming landline not available. Temp number 0797 4 925928
 
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., 2018: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho