| |||||||||||
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:
Runs Code:
[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:
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.
|
| ||||||||||
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho |