CODEWITHSUNDEEP
pythonmysql
samayo
samayo

Reputation: 16495

Mysql query not working with python script

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.

Upvotes: 2

Views: 1042

Answers (1)

mhawke
mhawke

Reputation: 87064

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''')

Upvotes: 4

Related Questions