Python Output Length

Question

I'm attempting to output my database table data, which works aside from long table rows. The columns need to be as large as the longest database row. I'm having trouble implementing a calculation to correctly output the table proportionally instead of a huge mess when long rows are outputted (without using a third party library e.g. Print results in MySQL format with Python). Please let me know if you need more information.

Database connection:

connection = sqlite3.connect("test_.db")
c = connection.cursor()

 c.execute("SELECT * FROM MyTable")
          results = c.fetchall()
          formatResults(results)

Table formatting:

def formatResults(x):
    try: 
          widths = []
          columns = []
          tavnit = '|'
          separator = '+' 

          for cd in c.description:
                widths.append(max(cd[2], len(cd[0])))
                columns.append(cd[0])

          for w in widths:
                tavnit += " %-"+"%ss |" % (w,)
                separator += '-'*w + '--+'

          print(separator)
          print(tavnit % tuple(columns))
          print(separator)
          for row in x:
              print(tavnit % row)
          print(separator)
          print ""
    except:
        showMainMenu()
        pass

Output problem example:

+------+------+---------+
| Date | Name | LinkOrFile |
+------+------+---------+
| 03-17-2016 | hi.com | Locky |
| 03-18-2016 | thisisitsqq.com | None    |
| 03-19-2016 | http://ohiyoungbuyff.com\69.exe?1 | None    |
| 03-20-2016 | http://thisisitsqq..com\69.exe?1 | None    |
| 03-21-2016 | %Temp%\zgHRNzy\69.exe | None    |
| 03-22-2016 |      | None    |
| 03-23-2016 | E52219D0DA33FDD856B2433D79D71AD6 | Downloader |
| 03-24-2016 | microsoft.com | None    |
| 03-25-2016 | 89.248.166.132 | None    |
| 03-26-2016 | http://89.248.166.131/55KB5js9dwPtx4= | None    |

Did you print the output of the variable widths? Does it contain the length of the longest value per column? — trans1st0r, Apr 20 '16 at 18:36
In `formatResults` after first loop, add line `print widths` — trans1st0r, Apr 20 '16 at 18:40

paceholder · Answer 1 · 2016-04-20T19:36:00.693

If your main problem is making column widths consistent across all the lines, this python package could do the job: https://pypi.python.org/pypi/tabulate

Below you find a very simple example of a possible formatting approach. The key point is to find the largest length of each column and then use format method of the string object:

#!/usr/bin/python

import random
import string
from operator import itemgetter


def randomString(minLen = 1, maxLen = 10):
    """ Random string of length between 1 and 10 """
    l = random.randint(minLen, maxLen)
    return ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(l))

COLUMNS = 4

def randomTable():
    table = []
    for i in range(10):
        table.append( [randomString() for j in range(COLUMNS)] )
    return table

def findMaxColumnLengs(table):
    """ Returns tuple of max column lengs """
    maxLens = [0] * COLUMNS
    for l in table:
        lens = [len(s) for s in l]
        maxLens = [max(maxLens[e[0]], e[1]) for e in enumerate(lens)]

    return maxLens



if __name__ == '__main__':
    ll = randomTable()

    ml = findMaxColumnLengs(ll)

    # tuple of formatting statements, see format docs
    formatStrings = ["{:<%s}" % str(m) for m in ml ]
    fmtStr = "|".join(formatStrings)

    print "=================================="
    for l in ll:
        print l
    print "=================================="
    for l in ll:
        print fmtStr.format(*l)

This prints the initial table packed in the list of lists and the formatted output.

==================================
['2U7Q', 'DZK8Z5XT', '7ZI0W', 'A9SH3V3U']
['P7SOY3RSZ1', 'X', 'Z2W', 'KF6']
['NO8IEY9A', '4FVGQHG', 'UGMJ', 'TT02X']
['9S43YM', 'JCUT0', 'W', 'KB']
['P43T', 'QG', '0VT9OZ0W', 'PF91F']
['2TEQG0H6A6', 'A4A', '4NZERXV', '6KMV22WVP0']
['JXOT', 'AK7', 'FNKUEL', 'P59DKB8']
['BTHJ', 'XVLZZ1Q3H', 'NQM16', 'IZBAF']
['G0EF21S', 'A0G', '8K9', 'RGOJJYH2P9']
['IJ', 'SRKL8TXXI', 'R', 'PSUZRR4LR']
==================================
2U7Q      |DZK8Z5XT |7ZI0W   |A9SH3V3U  
P7SOY3RSZ1|X        |Z2W     |KF6       
NO8IEY9A  |4FVGQHG  |UGMJ    |TT02X     
9S43YM    |JCUT0    |W       |KB        
P43T      |QG       |0VT9OZ0W|PF91F     
2TEQG0H6A6|A4A      |4NZERXV |6KMV22WVP0
JXOT      |AK7      |FNKUEL  |P59DKB8   
BTHJ      |XVLZZ1Q3H|NQM16   |IZBAF     
G0EF21S   |A0G      |8K9     |RGOJJYH2P9
IJ        |SRKL8TXXI|R       |PSUZRR4LR

he doesnt want to use a package, as his question mentions. – trans1st0r Apr 20 '16 at 18:35 — trans1st0r, Apr 20 '16 at 18:35
Yes, I want to do this without a package. – user5792975 Apr 20 '16 at 18:36 — user5792975, Apr 20 '16 at 18:36
Sorry, I have overlooked that. I've added an example – paceholder Apr 20 '16 at 19:39 — paceholder, Apr 20 '16 at 19:39

trans1st0r · Answer 2 · 2016-04-20T19:50:30.877

0

The code that you used is for MySQL. The critical part is the line widths.append(max(cd[2], len(cd[0]))) where cd[2] gives the length of the longest data in that column. This works for MySQLdb.

However, you are using sqlite3, for which the value cd[2] is set to None: https://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.description

Thus, you will need to replace the following logic:

 for cd in c.description:
                widths.append(max(cd[2], len(cd[0])))
                columns.append(cd[0])

with your own. The rest of the code should be fine as long as widths is computed correctly.

The easiest way to get the widths variable correctly, would be to traverse through each row of the result and find out the max width of each column, then append it to widths. This is just some pseudo code:

for cd in c.description:
   columns.append(cd[0])  # Get column headers

widths = [0] * len(c.description)   # Initialize to number of columns.
for row in x:
  for i in range(len(row)):  # This assumes that row is an iterable, like list
    v = row[i]  # Take value of ith column
    widths[i] = max(len(v), widths[i]) # Compare length of current value with value already stored

At the end of this, widths should contain the maximum length of each column.

edited Apr 20 '16 at 19:50

answered Apr 20 '16 at 19:01

trans1st0r

2,023
2
17
23

Ok, how would I replace that logic that you mentioned? Could you provide a sample please? – user5792975 Apr 20 '16 at 19:09
I'm not sure how I'd go about this. – user5792975 Apr 20 '16 at 19:20
This works but it does not return single-value queries such as Count(*). Any ideas on that one? – user5792975 Apr 20 '16 at 19:48
May have to do with the way `widths` is initialized. See edit. – trans1st0r Apr 20 '16 at 19:50
The Count(*) functions and large query results do not show up. – user5792975 Apr 20 '16 at 20:30

Python Output Length

2 Answers2