Python: Read database to display table with equal columns

I have a program when it currently reads from a database, which can be found here. I have users choose a specific record they want to display so the SQL command will execute that record. Now I have a table that currently displays those records, however, the columns are not how I wanted to look because I am dealing with long strings of data.

I am not using any extended libraries. My table display is sort of equal for some tables except tables like Suppliers, Orders, Categories. The width for each column is way too long. It takes too much space.

I think my code currently takes the longest string from the whole table and set the width of that for each column. What I want it to do instead is to take the longest string for each column and NOT the whole table, so the width of each column is set by string length (with like a little extra space of course) of that column.

Here is my code so far:

import sqlite3

def read_display(record):
  database = 'data.db'
  connection = sqlite3.connect(database)
  c = connection.cursor()
  # selects a table to display
  sql = "SELECT * FROM {0}".format(record)
  cursor.execute(sql)
  conn.commit()
  results = cursor.fetchall()

  header = [i[0] for i in c.description]
  data = [header] + list(tuple(results))
  width = max((len(str(x)) for d in data for x in d))

  for i, d in enumerate(data):
      line = ' | '.join(str(x).ljust(width) for x in d)
      print(line)
      if i == 0:
         print('-' * len(line))

I tried to mess with the width variable, but I can’t seem to figure it out. I tried looking at other StackOverflow q’s but they are dealing with little data, so it is easier to set the width. This data contains huge strings.

Thank you so much!

Answer

Comment: what does _ mean in #2?

Read What is the purpose of the single underscore “_” variable in Python?
Here, “3. As a general purpose “throwaway” variable name”, we don’t need the range(... value.

---

Question: display database records as table with equal columns

1. Get the columns names and prepend it to the results.

       header = tuple(i[0] for i in cur.description)
       data = [header] + results
   

2. Init config as a list with {'width':0}.
   Create as many dict items as columns in the database table.

       config = [{'width':0} for _ in range(len(data[0]))]
   

3. Loop the whole data, which is list of tuple.
   Find the max len and store it in the columns config dict

       for rec in data:
           for c, value in enumerate(rec):
               config[c]['width'] = max(config[c]['width'], len(str(value)))
   

4. Prepare format list, using the width from config

       format_ = []
       for f in config:
           format_.append('{:<' + str(f['width']) + '}')
   

5. Convert the list format_ to str using a '|' as delimiter.

       format_ = ' | '.join(format_)
   

6. Loop the data, list of tuple, print using the format_ string.

       for rec in data:
           print(format_.format(*rec))
   

Tested with Python: 3.5