How to compare two possibly different sqlite3 lists with comprehnsive list plus add new values?-CodePudding

I have a player ladder and I want to compare rankings and add info wether they moved up or down since last gained points. I select current and previous ladder list from sqlite3. Now I want to compare Rankings from each player and write the result into a new moverList.

#Get Ranks and Names from current Ranking table as a list
def getCurrentRankNew():
    conn = get_db_connection()
    currentList = conn.execute("SELECT ROW_NUMBER () OVER (ORDER BY Points DESC) Rank, Name FROM table").fetchall()
    conn.close()
    return currentList

#Get Ranks and Names from last Ranking table as a list
def getLastRankNew():
    conn = get_db_previous()
    lastList = conn.execute("SELECT ROW_NUMBER () OVER (ORDER BY Points DESC) Rank, Name FROM table").fetchall()
    conn.close()
    return lastList

The following is pseudeocode that does not work since I dont know how to do it. Goal is to make a comprehensive list, each value is a ladder moving indicator that is a result of a comparision of current and last table rankings for each Name.

Note that I try: except: the if-condition but when it fails that means on current list a new player entry exists that doesnt in the last list. In that case sqlite3 throws an error so except can be just mov = 'new'

def compareRanksNew():
    currentRank = getCurrentRankNew()
    lastRank =  getLastRankNew()
    try:
        if CurrentRank < LastRank:
            mov = 'up'
        elif CurrentRank > LastRank:
            mov = 'down'
        else:
            mov = '-'
    except:
        mov = 'new'
    movList = [mov for i in currentRank if i in lastRank]
    return movList

I solved this problem before using multiple lists and for loops but this is very slow like ~3 seconds for just ~350 table rows. I have read it is much faster to use comprehensive lists but I fail to get it working for days.

This is my old solution:

#Make a mov list for all players 
def getMoverList():
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute('SELECT Name FROM table ORDER BY Points DESC')

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

    return moverList

#Get Rank from a playName from current Ranking table
def getCurrentRank(playerName):
    conn = get_db_connection()
    player = conn.execute("SELECT ROW_NUMBER () OVER (ORDER BY Points DESC) Rank, Name FROM table")

    for item in player:
        if playerName in item:
            return item[0]

#Get Rank from a playName from previous Ranking table
def getLastRank(playerName):
    conn = get_db_previous()
    player = conn.execute("SELECT ROW_NUMBER () OVER (ORDER BY Points DESC) Rank, Name FROM table").fetchall()
    conn.close()
    for item in player:
        if playerName in item:
            return item[0]

#compare ranks and return moving value (mov)
def compareRanks(playerName):
    CurrentRank = getCurrentRank(playerName)
    LastRank =  getLastRank(playerName)

    try:
        if CurrentRank < LastRank:
            mov = 'up'
        elif CurrentRank > LastRank:
            mov = 'down'
        else:
            mov = '-'
    except:
        mov = 'new'

    return mov

getMoverList()
    
conn.close()

CodePudding user response：

One possible solution is to load both databases in one SQLite connection, the you can join the two databases in different ways and let the database engine itself perform the work.

First off, create a small test database, this is roughly like the layout of your database, just here for completeness:

# Create a couple of databases with some test data
for fn in ["db_cur.db", "db_prev.db"]:
    db = sqlite3.connect(fn)
    db.execute("CREATE TABLE players(Name, Points);")
    for i in range(350):
        if random.random() <= 0.01:
            # 1 percent chance of a random score, near the "real" score
            db.execute("INSERT INTO players(Name, Points) VALUES(?,?);", (f"Player {i}", i   random.randint(1, 10)))
        else:
            # Otherwise, just use the score based off the player name
            db.execute("INSERT INTO players(Name, Points) VALUES(?,?);", (f"Player {i}", i))
    db.commit()
    db.close()

Then, with these two databases created, you can read from both of them in one connection by attaching them:

# Now load both databases so we can join them:
db = sqlite3.connect(":memory:")
db.execute("ATTACH 'db_cur.db' as cur;")
db.execute("ATTACH 'db_prev.db' as prev;")
sql = """
SELECT 
    prev_ranks.Rank,
    prev_ranks.Points,
    cur_ranks.Rank,
    cur_ranks.Points,
    prev_ranks.Name
FROM
    (SELECT 
        ROW_NUMBER () OVER (ORDER BY Points DESC) AS Rank, 
        Name, 
        Points 
    FROM 
        prev.players) AS prev_ranks
INNER JOIN 
    (SELECT 
        ROW_NUMBER () OVER (ORDER BY Points DESC) AS Rank, 
        Name, 
        Points 
    FROM 
        cur.players) AS cur_ranks
ON 
    prev_ranks.Name = cur_ranks.Name;
"""
for prev_rank, prev_points, cur_rank, cur_points, name in db.execute(sql):
    if prev_rank < cur_rank:
        print(f"{name} moved down from {prev_rank} to {cur_rank}")
    elif prev_rank > cur_rank:
        print(f"{name} moved up from {prev_rank} to {cur_rank}")