Can I set a user variable in Python MySQLdb?

Question

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

However, this query does not work if I sent it via 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 

He prints an empty tuple.

How can i solve this?

thanks

Answer 1

Try to do this as two queries.

If you want this to be a single query, examples of comments on the MySQL User Variables documentation look like this:

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

or

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

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

Answer 2

Try one query at a time:

 cursor.execute("SET @X:=0;"); cursor.execute("SELECT @X: =@X +1 FROM some_table");