How to get a result from multiple select statements with pymysql

Question

Basically, I have the following example (Assume cur comes from a valid connection):

>>> con =  pymysql.connect(<parameters go here>)
>>> cur = con.cursor()
>>> sql = "SELECT @a := 0; SELECT @a := @a+2; SELECT @a;"
>>> res = cur.execute(sql)
>>> res
1

As you can see, res returns the integer 1, which means the sql went well. However, the last select should return the number 2 and I need that number.

If I run this code (suggested by @falsetru), I don't get what I need either:

>>> cur.execute(sql)
1
>>> cur.fetchall()
[{u'@a := 0': 0}]

How can I retrieve it? Is it possible without separating the SQL statements?

Answer 1

Use Cursor.fetchone to get a single row, or Cursor.fetchmany/Cursor.fetchall to get many or all result rows.

row = cur.fetchone()
a = row[0]

---

UPDATE Cursor.execute executes a single sql statement, so execute statements separately (by spliting sql by ;)

import pymysql
con =  pymysql.connect(user='root', password='root')
cur = con.cursor()
sql = "SELECT @a := 0; SELECT @a := @a+2; SELECT @a;"
for stmt in sql.split(';'):
    if stmt.strip():
        cur.execute(stmt)
row = cur.fetchone()
a = row[0]
print(a)  # => 2