CODEWITHSUNDEEP
pythonsqliteformat
user9156598
user9156598

Reputation:

printing table in format without using a library, SQLite 3 python

I want to print data from my database in a better format, this is for a game I made. This is the code:

def read_all_tables(self):
        self.cursor.execute('SELECT Name, Gender, Age, Score, Date, Time FROM Link JOIN Scores ON  Score_ID = Scores.ID JOIN Player ON Player_ID = Player.id ORDER BY Score DESC')
        Data = self.cursor.fetchall()
        for Row in Data:
            print()
            for record in range(len(Row)):
               print(Row[record], end=" ")

The output is:

HAMMAD MALE 18 900 07/01/18 13:07:02 
HAMMAD MALE 18 850 07/01/18 13:30:11 
INDERVEER MALE 18 750 07/01/18 13:35:46 
HAMMAD MALE 18 500 07/01/18 13:08:29 
HAMMAD MALE 18 400 07/01/18 14:07:29 
PARSA MALE 18 300 07/01/18 13:36:58 
HADIA FEMALE 19 300 07/01/18 14:09:37 
MANAAL FEMALE 18 100 07/01/18 13:51:40 
MICHAL MALE 18 0 07/01/18 13:42:41 
HAMMAD MALE 18 0 07/01/18 13:44:04 
HADIA FEMALE 19 0 07/01/18 13:45:51 
MANAAL FEMALE 18 0 07/01/18 13:53:02 
JACK WEIRD 21 0 07/01/18 13:53:49 
HAMMAD MALE 18 0 07/01/18 13:54:44 
HAMMAD MALE 18 0 07/01/18 13:56:08 
MANAAL FEMALE 18 0 07/01/18 13:57:39 
PARSA MALE 18 0 07/01/18 13:58:25 
HAMMAD MALE 18 0 07/01/18 13:59:08 
HAMMAD MALE 18 0 07/01/18 14:10:37

How can I align them and have a column heading? I would like not to use any library.

Upvotes: 1

Views: 4236

Answers (3)

Bojan
Bojan

Reputation: 21

The accepted answer did not work for me, so I made my own, similar one.

  • Replace result with Data.
  • Remember to add row_factory, so when doing SELECT * tables, so you can use headers, and insert them as top row of the table. That is what first 3 rows do.

The rest of the code:

  • Create list of column lengths, initially fill them with 0 * number of first row items (which is also header).
  • For each cell in row, if width of a cell is less than maximum width of that column, set maximum to current cell width.
  • Print cell in each row, replacing None with spaces, and adding offset.
    cursor.row_factory = sqlite3.Row  
    result=cursor.execute(  'select rowlong as info,games,roms,disks,samples,roms_disks_samples as all_files           from stats' ).fetchall()
    result.insert( 0,  (result[0].keys()) )#insert column names as first row (position 0); 'cursor.row_factory = sqlite3.Row' must be used before


    int_offset = 2
    ls_int_max_column_length = [0] * len( result[0] )
    for row in result:
        for i in range(len(row)):
            int_cell_space = len(    str( row[i] )     )
            if ls_int_max_column_length[i] < int_cell_space:
                ls_int_max_column_length[i] = int_cell_space
    
    #print(ls_int_max_column_length)

    print("")
    for row in result:
        for i in range(len(row)):
            print ( str(row[i]).ljust( ls_int_max_column_length[i] + int_offset ).replace('None','    ') , end = " ")
            #print ( str(row[i]) , end = " ")
        print("")
    print("")

    cursor.row_factory = None #set it back to None

You could do it manually, but it is not recommended, what if the length changes:

    print("")
    for row in result:
        print ( str(row[0]).ljust(38), str(row[1]).ljust(25).replace('None','    '), str(row[2]).ljust(21).replace('None','    '), str(row[3]).ljust(16).replace('None','    '), str(row[4]).ljust(8).replace('None','    '), str(row[5]).ljust(9).replace('None','    '))
    print("")

Upvotes: 0

Hai Vu
Hai Vu

Reputation: 40733

Use the string formatting capability:

formatted_row = '{:<10} {:<6} {:>6} {:>6} {:<9} {:<9}'
print(formatted_row.format("Name", "Gender", "Age", "Score", "Date", "Time"))
for Row in Data:
    print(formatted_row.format(*Row))

Output:

Name       Gender    Age  Score Date      Time     
HAMMAD     MALE       18    900 07/01/18  13:07:02 
HAMMAD     MALE       18    850 07/01/18  13:30:11 
INDERVEER  MALE       18    750 07/01/18  13:35:46 
HAMMAD     MALE       18    500 07/01/18  13:08:29 
HAMMAD     MALE       18    400 07/01/18  14:07:29 
PARSA      MALE       18    300 07/01/18  13:36:58 
HADIA      FEMALE     19    300 07/01/18  14:09:37 
MANAAL     FEMALE     18    100 07/01/18  13:51:40 
...

Note

In this approach, we hard-code the width of the columns. In order to dynamically adjust the columns width, we will have to do a good deal more works. I hope this will work for you.

Update

In order to dynamically adjust the widths, we need to pass through the data twice: The first time to determine the maximum width for each column, and the second to print.

# Determine the longest width for each column
header = ("Name", "Gender", "Age", "Score", "Date", "Time")
widths = [len(cell) for cell in header]
for row in Data:
    for i, cell in enumerate(row):
        widths[i] = max(len(str(cell)), widths[i])

# Construct formatted row like before
formatted_row = ' '.join('{:%d}' % width for width in widths)

print('DEBUG: widths={!r}'.format(widths))
print('DEBUG: formatted_row={!r}'.format(formatted_row))

print(formatted_row.format(*header))
for row in Data:
    print(formatted_row.format(*row))

Output:

DEBUG: widths=[9, 6, 3, 5, 8, 8]
DEBUG: formatted_row='{:9} {:6} {:3} {:5} {:8} {:8}'
Name      Gender Age Score Date     Time    
HAMMAD    MALE    18   900 07/01/18 13:07:02
HAMMAD    MALE    18   850 07/01/18 13:30:11
INDERVEER MALE    18   750 07/01/18 13:35:46
...

Once you are happy with the result, you can delete the DEBUG lines. They are there to show how the code works.

Upvotes: 3

varro
varro

Reputation: 2482

For the heading, just select the text you want, like

select 'Name    Gender    Age  ...';

To format the data, use the printf() function (see https://sqlite.org/lang_corefunc.html#printf), like

select printf('%-20s %-6s  %2d ....', Name, Gender, Age, ...) from ...;

(Adjust as necessary.)

Upvotes: 0

Related Questions