- #1
- 2,111
- 2,691
I have a database that looks like this:
I want to get the "leaderboard position" of a particular member. The idea is to order the data by the
This works as I want in MySQL, returning the appropriate row number:
But, when I try to do the same via
Any idea why I am facing this?
In case anyone wants to test, this is the code for the dummy database:
I want to get the "leaderboard position" of a particular member. The idea is to order the data by the
score
column, and then retrieve the row position of that particular member id. This is my query in MySQL:
MySQL:
SET @rowNum := 0;
SELECT rowNum
FROM(
SELECT (@rowNum := @rowNum + 1) AS rowNum, member_id
FROM members
ORDER BY score DESC) tmp
WHERE member_id = 288745911563241;
This works as I want in MySQL, returning the appropriate row number:
But, when I try to do the same via
mysql.connector
in Python 3.12, I get an empty list. This is my code in Python:
Python:
import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="user_name",
password="pwd", database="db_name")
mycursor = mydb.cursor()
def main():
mycursor.execute("SET @rowNum := 0; "
"SELECT rowNum FROM( "
"SELECT (@rowNum := @rowNum + 1) AS rowNum, member_id "
"FROM members "
"ORDER BY score DESC) tmp "
"WHERE member_id = 288745911563241;")
print(mycursor.fetchall())
mycursor.close()
mydb.close()
if __name__ == '__main__':
main()
Any idea why I am facing this?
In case anyone wants to test, this is the code for the dummy database:
SQL:
CREATE DATABASE db_name;
USE db_name;
CREATE TABLE members (
member_id BIGINT PRIMARY KEY NOT NULL,
score BIGINT NOT NULL,
correct BIGINT NOT NULL,
wrong BIGINT NOT NULL,
highest_valid_count BIGINT NOT NULL
);
INSERT INTO members VALUES
(115366974582, 5, 12, 7, 20),
(588412336974, 25, 78, 53, 105),
(144865512587, 2, 98, 96, 50),
(255418963314, 31, 51, 20, 65),
(5221479632215, 12, 25, 13, 52),
(25596418873641, 23, 81, 58, 31),
(75532184413259, 41, 51, 10, 96),
(288745911563241, 9, 23, 14, 10);