How to number from an SQL database in Python?

Question

How to get numbers 1 to 10 next to the SQL table contents from the Chinook database in a good format? I can't get the loop from 1 to 10 next to the other three elements of the database file. The output I want :

1        Chico Buarque                    Minha Historia     27     
2        Lenny Kravitz                    Greatest Hits      26     
3        Eric Clapton                         Unplugged      25     
4        Titãs                                 Acústico      22     
5        Kiss                             Greatest Kiss      20     
6        Caetano Veloso                     Prenda Minha     19     
7        Creedence Clearwater Revival    Chronicle, Vol. 2   19     
8        The Who  My Generation - The Very Best Of The Who   19     
9        Green Day              International Superhits      18     
10       Creedence Clearwater Revival    Chronicle, Vol. 1   18

My code :

import sqlite3

try: 
        conn = sqlite3.connect(r'C:\Users\Just\Downloads\chinook.db')
except Exception as e:
        print(e)

cur = conn.cursor()

cur.execute('''SELECT artists.Name, albums.Title, count (albums.AlbumId) AS AlbumAmountListened
FROM albums
INNER JOIN tracks ON albums.AlbumId = tracks.AlbumId
INNER JOIN invoice_items ON tracks.TrackId = invoice_items.TrackId
INNER JOIN artists ON albums.ArtistId = artists.ArtistId
GROUP BY albums.AlbumId
ORDER BY AlbumAmountListened DESC
LIMIT 10''')

top_10_albums = cur.fetchall()

def rank():                                                 
        for item in top_10_albums:
                name = item[0]
                artist = item[1]
                album_played = item[2]
                def num():
                        for i in range(1,11):
                                print (i)
                        return i               
                

                print (num(),'\t', name, '\t', artist, '\t', album_played, '\t')
        
print (rank())

My 1-10 number loops like this:

1
2
3
4
5
6
7
8
9
10
10   Chico Buarque   Minha Historia      27     
1
2
3
4
5
6
7
8
9
10
10   Lenny Kravitz   Greatest Hits   26

And so on. How do I correctly combine my range object?

Answer 1

Numbered Version

def rowView(strnum,row,flen_align=[(30,"l"),(30,"r"),(5,"r")]):

    i = 0
    line=""
    for k,v in row.items():

        flen , align = flen_align[i]
        strv = str(v)
        spaces = "_" * abs(flen - len(strv))

        if align == "l":
            line += strv+spaces
        if align == "r":
            line += spaces+strv
        i+=1

    return strnum+line

dlist=[
{ "name":"Chico Buarque", "title":"Minha Historia","AAL":27},
{ "name":"Lenny Kravit", "title":"Greatest Hits","AAL":26},
{ "name":"Eric Clapton", "title":"Unplugged","AAL":25},
{ "name":"Titã", "title":"Acústico","AAL":22},
{ "name":"Kis", "title":"Greatest Kiss","AAL":20},
{ "name":"Caetano Velos", "title":"Prenda Minha","AAL":19},
{ "name":"Creedence Clearwater Reviva", "title":"Chronicle,Vol.2","AAL":19},
{ "name":"TheWho My Generation", "title":"The Very Best Of The Who","AAL":19},
{ "name":"Green Da", "title":"International Superhits","AAL":18},
{ "name":"Creedence Clearwater Reviva", "title":"Chronicle,Vol.1","AAL":18}
]

for num, row in enumerate(dlist,start=1):
    strnum=str(num)
    strnum += "_" * (5-len(strnum))
    print(rowView(strnum,row))

Or using record id directly

def rowView(row,flen_align=[(5,"l"),(30,"l"),(30,"r"),(5,"r")]):

    i,line = 0,""

    for k,v in row.items():
        flen , align = flen_align[i]
        strv = str(v)
        spaces = "_" * abs(flen - len(strv))

        if align == "l":
            line += strv+spaces
        if align == "r":
            line += spaces+strv
        i+=1

    return line

dlist=[
{"id":1, "name":"Chico Buarque", "title":"Minha Historia","AAL":27},
{"id":2, "name":"Lenny Kravit", "title":"Greatest Hits","AAL":26},
{"id":3, "name":"Eric Clapton", "title":"Unplugged","AAL":25},
{"id":4, "name":"Titã", "title":"Acústico","AAL":22},
{"id":5, "name":"Kis", "title":"Greatest Kiss","AAL":20},
{"id":6, "name":"Caetano Velos", "title":"Prenda Minha","AAL":19},
{"id":7, "name":"Creedence Clearwater Reviva", "title":"Chronicle,Vol.2","AAL":19},
{"id":8, "name":"TheWho My Generation", "title":"The Very Best Of The Who","AAL":19},
{"id":9, "name":"Green Da", "title":"International Superhits","AAL":18},
{"id":10, "name":"Creedence Clearwater Reviva", "title":"Chronicle,Vol.1","AAL":18}
]

for row in dlist:
    print(rowView(row))

same output for both versions:

1____Chico Buarque_________________________________Minha Historia___27
2____Lenny Kravit___________________________________Greatest Hits___26
3____Eric Clapton_______________________________________Unplugged___25
4____Titã________________________________________________Acústico___22
5____Kis____________________________________________Greatest Kiss___20
6____Caetano Velos___________________________________Prenda Minha___19
7____Creedence Clearwater Reviva__________________Chronicle,Vol.2___19
8____TheWho My Generation________________The Very Best Of The Who___19
9____Green Da_____________________________International Superhits___18
10___Creedence Clearwater Reviva__________________Chronicle,Vol.1___18

Answer 2

You can use enumerate() to provide the numbers for you as you iterate over the rows:

top_10_albums = cur.fetchall()

for i, item in enumerate(top_10_albums, start=1):
    name = item[0]
    artist = item[1]
    album_played = item[2]
    print(f'{i}\t{name}\t{artist}\t{album_played}')

You don't even have to unpack the item into variables, just reference them directly in the fstring:

for i, item in enumerate(top_10_albums, start=1):
    print(f'{i}\t{item[0]}\t{item[1]}\t{item[2]')

But this is perhaps nicer:

for i, (name, artist, album_played) in enumerate(top_10_albums, start=1):
    print(f'{i}\t{name}\t{artist}\t{album_played}')

This uses tuple unpacking to bind the fields from the row to descriptively named variables, which makes it self documenting.

Answer 3

Just need to iterate with an index(i) within the for loop such as

top_10_albums = cur.fetchall()

i=0
for item in top_10_albums:
        name = item[0]
        artist = item[1]
        album_played = item[2]
        i += 1              
                
        print (i,'\t', name, '\t', artist, '\t', album_played, '\t')

in your case, inner loop produces 10 numbers for each step of outer loop.