Click here to Skip to main content
15,041,788 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hello,

I am using below query and query returns 100 records in MYSQL workbench but returns 0 in python cursor.

import MySQLdb as dbapi
import sys

QUERY="SET @row_number = 0; select * from (SELECT (@row_number:=@row_number + 1) AS '#input', t.* from view t LIMIT 100) as T;"
        db=dbapi.connect(Dbconnection)
        cur=db.cursor()
        cur.execute(QUERY)
        result=cur.fetchall()


In result I get 0 records, may be executing SET @rownumber first might return 0 rows. I don't have permission to create store procedure

What I have tried:

I am trying to add new column in csv with numbering but I believe it will affect performance
Posted
Updated 21-May-21 7:06am

1 solution

You are using the table alias T twice. Try
Python
QUERY="SET @row_number = 0; select * from (SELECT (@row_number:=@row_number + 1) AS '#input', t.* from view t LIMIT 100) as X;"
That was a glitch at my end.

As you have multiple statements in QUERY try
Python
cur.execute(QUERY, multi=True)
Beware: this can have different behaviour depending on which drivers you are using, so it's not the recommended approach.

If you have MySQL V8.0 or better then you could use the ROW_NUMBER [^] function to get the same result
SQL
ROW_NUMBER() OVER () AS rn, ID from view;
   
v3
Comments
RAHUL(10217975) 21-May-21 14:12pm
   
Query works fine in mysql query editor. But same query returns 0 result in python
CHill60 21-May-21 14:15pm
   
When I ran it in SQL fiddle I got 0 rows returned. When I changed it to X I got 1 row returned. I had 1 row on my rest table.
Might be a failing of sql fiddle though.
Did you actually try this fix?
RAHUL(10217975) 22-May-21 8:34am
   
Indeed!
result is 0 records
CHill60 22-May-21 10:26am
   
Well that started working for me now too - must have been a temporary glitch with the fiddle site. I'm updating my solution with an alternative

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900