Take the 2-minute tour ×

So My problem is this, I have a query that uses Mysql User-defined variable like: @x:=0 SELECT @X:=@X+1 from some_table and this code returns a column from 1-1000.

However, this query doesn't work if I sent it through mySQLdb in Python.

connection =MySQLdb.Connect(host='xxx',user='xxx',passwd='xxx',db = 'xxx')
cursor = connection.cursor
cursor.execute("""SET @X:=0;SELECT @X:=@X+1 FROM some_table""")
rows = cursor.fetchall()
print rows

It prints a empty tuple.

How can I solve this?


share|improve this question

2 Answers 2

up vote 1 down vote accepted

Try it as two queries.

If you want it to be one query, the examples in the comments to the MySQL User Variables documentation look like this:

SELECT @rownum:=@rownum+1 rownum, t.* FROM (SELECT @rownum:=1) r, mytable t;


SELECT if(@a, @a:=@a+1, @a:=1) as rownum

See http://dev.mysql.com/doc/refman/5.1/en/user-variables.html

share|improve this answer
Hi,Thanks for your help. It works now. But my real query is kinda complicated than this dummy example. It runs rather fast in mysql, like 2 sec, but when I sent this query through python, it is much much slower, like more than minutes. Do you have any insight on this issue? Thanks –  zsljulius Jul 24 '11 at 15:56

Try to execute one query at a time:

cursor.execute("SET @X:=0;");
cursor.execute("SELECT @X:=@X+1 FROM some_table");
share|improve this answer
Thank you, It works great now! –  zsljulius Jul 24 '11 at 15:57
@zsljulius you are welcome. don't hetistate to upvote my answer :) –  spacediver Jul 27 '11 at 20:08
this worked perfect for me as well :D @zsljulius I edited your answer to add some extra info that would've helped me even quicker :) –  rgenito Apr 23 '13 at 20:12

Your Answer


