如何从Python的SQL数据库编号?

Question

如何以良好的格式从Chinook数据库中获得SQL表内容旁边的数字1到10 ?我无法在数据库文件的其他三个元素旁边获得从1到10的循环。我想要的输出:

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

我的代码:

import sqlite3
try: 
conn = sqlite3.connect(r'C:UsersJustDownloadschinook.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())

我的1-10数字循环是这样的:

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

以此类推。我如何正确地组合我的范围对象?

Answer 1

您可以使用enumerate()在遍历行时为您提供数字:

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}')

您甚至不必将项解包为变量，只需在fstring中直接引用它们:

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

但是这个可能更好:

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

它使用元组解包将行中的字段绑定到描述性命名的变量，这使得它可以自我记录。

Answer 2

只需要在for循环中迭代索引(i)，例如

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

在你的例子中，内循环为外循环的每一步产生10个数字。

Answer 3

编号版本

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

或者直接使用record id

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

两个版本的输出相同:

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