0

I have a function getMoverList() that needs to SELECT Names from a sorted sqlite3 column:

def getMoverList():
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute('SELECT Name, CAST (Points AS int) as Points, FROM table ORDER BY Points DESC')

    moverList = []
    for Name in cur.fetchall():
        playerName = str(Name[0])
        moverList.append(compareRanks(playerName))

    return moverList
    conn.close()

The function has to get the Names of the db column, pass each Name to another function compareRanks(playerName) to get a wanted value (RankingDifference) and make another sorted list with wanted values.

The above code works but is pretty slow. I found alternative Python is slow when iterating over a large list that makes a way faster list:

def getMoverList():
    conn = get_db_connection()
    moverList = conn.execute('SELECT Name, CAST (Points AS int) as Points, FROM table ORDER BY Points DESC').fetchall()
    return moverList
    conn.close()

The problem is that moverList now doesnt contain strings with names but 'sqlite3.Row' objects. Tried things like

mover = getMoverList()
print (mover[0].Name)

but "'sqlite3.Row' object has no attribute 'Name'" How to do this?

Edit: Using sqlite3.row here:

def get_db_connection():
    conn = sqlite3.connect('db.db')
    conn.row_factory = sqlite3.Row
    return conn

Edit2: getLastRank(): Here I pass a player Name to the function and want to have a look at my table for LastRank of previous backupped db. I dont have ranks saved in the db table. Instead I initially worked with flasks loop.index to show rankings and now that I want to work with it I figured out to get a rank with ROW COUNT. If I put conn.close() before the for loop with return I get 'sqlite3.ProgrammingError: Cannot operate on a closed database.'

def getLastRank(playerName):
    conn = get_db_previous()
    player = conn.execute("SELECT ROW_NUMBER () OVER (ORDER BY Points DESC) Rank, Name, Points FROM alltime").fetchall()
    
    for item in player:
        if playerName in item:
            return item[0]
    conn.close()
fk1
  • 33
  • 5
  • 2
    Did you read the documentation? https://docs.python.org/3/library/sqlite3.html#sqlite3.Row – Shawn Oct 20 '21 at 21:08
  • @Shawn Im using sqlite3.row but not sure if I do it properly. I'll edit my first post and show you the regarded func get_db_connection() – fk1 Oct 21 '21 at 08:19
  • 1
    From the documentation Shawn linked, you can use `mover[0]['Name']` to achieve what you're after. A row apparently behaves like a `dict` with column names as keys and column data as values. – joH1 Oct 21 '21 at 08:24
  • 1
    calling `conn.close()` after `return` is not a good idea .. – balderman Oct 21 '21 at 08:24
  • @balderman Can you explain why? In general or because of speeds? I also did not like this but I had one function where it told me it can not operate on a closed database. I reviewed my testing code and found it. getLastRank(playerName) I will edit into OP. I need to restructure all of this. My first thought today was: If I am going through multiple functions best way will be to connect and close to db just once, need to rethink all my project, ty :) – fk1 Oct 22 '21 at 09:14
  • @joH1: Thank you, that got me the output I was looking for. Unfortunately I still now have to iterate the output Names into the compareRanks(playerName) func and that again felt like as slow as the original solution. Any way to avoid iterating through a list for the function? I feel like the for loop is the speed problem. – fk1 Oct 22 '21 at 09:23

0 Answers0