Can I set user-defined variable in Python MySQLdb?

Question

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?

Thanks

spacediver · Accepted Answer · 2011-07-24 14:36:32Z

10

Try to execute one query at a time:

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

answered Jul 24, 2011 at 14:36

spacediver

1,4931 gold badge11 silver badges18 bronze badges

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

1 Comment

rgenito Over a year ago

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

agf · Accepted Answer · 2011-07-24 14:45:40Z

3

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;

or

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

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

answered Jul 24, 2011 at 14:45

agf

178k45 gold badges300 silver badges241 bronze badges

1 Comment

zsljulius Over a year ago

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

Collectives™ on Stack Overflow

Can I set user-defined variable in Python MySQLdb?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related