Mysql query not working with python script

Question

I know, 'not working' is pretty vague, but that is what's happening with this script.

I have a database with table _added_points that looks like this.

 id g_id g_name by_score by_rank by_percentage
------ ---- --------------------------- -------- ------- -------------
 1 2332 john-doedd 307 408 (NULL)
 2 3724 maxx 844 15 (NULL)
 3 5208 orion-straxee-asset 663 122 (NULL)
 4 8333 Proton 777 49 (NULL)
 5 9029 contax-dgaaevel 155 542 (NULL)
 6 6789 clanstrom-e 803 32 (NULL)
 7 1250 rexx-bundle 666 119 (NULL)
 8 0236 cluster-performance-toolkit 154 543 (NULL)
 9 0334 gotas 493 263 (NULL)
 10 3470 pleximoir 611 163 (NULL)

Using the below, mysql code, I am trying to give a percentage to each user buy calculating the by_score field.

SET @total= (SELECT COUNT(*) FROM _added_points);
SELECT s1.g_name, s1.g_id, FLOOR(nge / @total * 100) + 1 AS percent 
FROM _added_points s1
JOIN (SELECT s1.id, COUNT(s2.id) AS nge
 FROM _added_points s1
 JOIN _added_points s2
 ON s1.by_score <= s2.by_score
 GROUP BY s1.id) AS s2
ON s1.id = s2.id
ORDER BY by_score DESC

The problem with the above script is, that it works when I run it using a a query builder of Mysql Workbench or SQLyog .. but when I run it with a python script, the I get nothing. Something like this:

[root@localhost crawler]# python _added_points.py
()

I have more than double checked if there was an issue with my python code, but there is nothing. Even the try: except block can't catch any exception.

Is there a code that works on mysql clients, but not when run as on programs like python, or php? It does not seem logical, but that is exactly what is happening.

• Selcuk: You should also post your Python code.
• samayo: @Selcuk I knew someone would say that, there is nothing except a mysql query. that works with other query, but NOT with this one.
• Mihai: Can python mysql library handle 2 queries at the same time?
• samayo: @Mihai That would be the only case which would make sense.

Answer 1

Try executing two queries, the first to set the user-defined variable and the second query which references the variable:

cursor.execute('SET @total= (SELECT COUNT(*) FROM _added_points)')
cursor.execute('''SELECT s1.g_name, s1.g_id, FLOOR(nge / @total * 100) + 1 AS percent 
FROM _added_points s1
JOIN (SELECT s1.id, COUNT(s2.id) AS nge
 FROM _added_points s1
 JOIN _added_points s2
 ON s1.by_score <= s2.by_score
 GROUP BY s1.id) AS s2
ON s1.id = s2.id
ORDER BY by_score DESC''')

• samayo: after hours of trying everything, this only worked for me. Even though it looks like a big hack, I have no choice but to accept it. Thanks!!
• mhawke: I think that it has to be done this way in order for the user-defined variable to be present in the session. It's not such a big hack though, not all DB-API interfaces support execution of multiple SQL queries - sqlite3 for example.