1

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.

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks a lot, now my work looks much neater, and where should I start if I don't want to hard code. This is for my A-level (Year 13) coursework. So the better I code the more marks I'll get
0

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.)

Improve this answer

Comments

0

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("")
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.