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 I knew someone would say that, there is nothing except a mysql query. that works with other query, but NOT with this one. — samayo
– samayo, Commented Mar 21, 2016 at 0:18

mhawke · Accepted Answer · 2016-03-21 01:40:23Z

4

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

answered Mar 21, 2016 at 1:40

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

samayo Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

Mysql query not working with python script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related