Using python's MySQLdb library to execute a mysql query that needs a couple of database variables using the SET
command to be defined before the query will work properly and cannot apparently provide multiple SQL statements into a single python execute command using semicolons for separating each query. Python or MySQLdb wants a single SQL query so need an answer on how to best sub-query I guess the SET
commands into what will already be a much larger query. Below is the most simplified version of the query and made possible thanks to others at stack overflow already...
SET @rownum := 0;
SET @ip := null;
SELECT *
FROM (
SELECT IF(
@ip=ip,@rownum:=@rownum+1,@rownum:=0) AS rownum,
@ip:=ip AS ip,
oid
FROM test
ORDER BY ip, oid
) AS t
ORDER BY FLOOR(rownum/10),
ip,
oid;
The final query will actually include some another sub-query or two so trying to figure out the appropriate what to set the variables inside as cleanly as possible. In research, indications have pointed out against defining and using a database variable inside the same query is not suggested as the order of processing may not be what is expected. So what is the answer on how best to achieve this quandary?