Why is there a problem with sorting?

Question

Python

My code for displaying the top players of my bot looks like this:

@dp.message_handler(commands=['top'])
async def cmd_top(message: types.Message):
    tops = cur.execute("SELECT balance FROM users").fetchall()
    tops.sort()
    top1 = tops[-1][0]
    top2 = tops[-2][0]
    top3 = tops[-3][0]
    top4 = tops[-4][0]
    top5 = tops[-5][0]
    allt = [top1, top2, top3, top4, top5]
    alltops = ""
    num = 0
    for x in allt:
        fname = cur.execute("SELECT fname FROM users WHERE balance=?", (x,)).fetchall()
        num += 1
        alltops += f"{num} • {fname} - <b><i>{x}</i></b> монеток.\n"
    await message.reply(f"Топ 5 богачей бота.\n\n{alltops}")

After entering the command, an error pops up in the console:

tops.sort()
TypeError: '<' not supported between instances of 'NoneType' and 'int'

Please help me solve the problem

I am writing on the Aiogram library

Answer 1

The code is fabulous. Two queries instead of one, selecting all rows for the sake of sorting on the client, NULL where it shouldn't be, and so on.
All this is perfectly done at the request level to the database!

SELECT fname, balance FROM users WHERE balance IS NOT NULL ORDER BY balance DESC LIMIT 5

It will select the name and balance of users whose balance is not NULL (the SQL NULL turns into Python's None, which you encountered when sorting). This, by the way, is your fault, you should have specified NOT NULL and the default value for the balance field when creating the table.
The result is sorted by the balance field in descending order, and no more than the first 5 rows are selected (maybe less).
Each row of the result will be a tuple, where the zero element is the player's name and the first element is the balance.

top5 = cur.execute('SELECT fname, balance FROM users WHERE balance IS NOT NULL ORDER BY balance DESC LIMIT 5').fetchall()
table = []
for num, (fname, x) in enumerate(top5, 1):
    table.append(f'{num} • {fname} - <b><i>{x}</i></b> монеток.\n' )
await message.reply('Топ 5 богачей бота.\n\n'+''.join(table))

Learn SQL, you will greatly simplify your life when compiling queries.

Answer 2

Apparently, the database returns empty values ​​among the values, which causes the error. Here is her model:

tops = [(1,), (2,), (3,), (4,), (None,), (7,)]
tops.sort()

Check what select returns. Perhaps you need to make sure there are no None before sorting, or loop through and convert all None to a null int value.

spoiler

Также конструкция со множеством присваиваний выглядит ужасно. Вот вариант:

tops = [(1,), (2,), (3,), (4,), (None,), (7,)]

tops = [(0, ) if item[0] == None else item for item in tops]
allt = [item[0] for item in sorted(tops, reverse=True)][:5]
print(allt)