如何使用pymysql从多个select语句中获取结果 [英] How to get a result from multiple select statements with pymysql

问题描述

基本上，我有以下示例(假设 cur 来自有效连接):

<预><代码>>>>con = pymysql.connect(<参数在这里>)>>>cur = con.cursor()>>>sql = "SELECT @a := 0; SELECT @a := @a+2; SELECT @a;">>>res = cur.execute(sql)>>>资源1

如您所见，res 返回整数 1，这意味着 sql 运行良好.但是，最后一个选择应该返回数字 2，我需要这个数字.

如果我运行此代码(由@falsetru 建议)，我也没有得到我需要的:

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

我怎样才能找回它?是否可以不分离 SQL 语句?

解决方案

使用 Cursor.fetchone 获取一行，或者 Cursor.fetchmany/Cursor.fetchall 获取许多或所有结果行.

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

<小时>

UPDATE Cursor.execute 执行单个sql语句，所以单独执行语句(通过分割sql;)

导入pymysqlcon = pymysql.connect(user='root', 密码='root')cur = con.cursor()sql = "SELECT @a := 0; SELECT @a := @a+2; SELECT @a;"对于 sql.split(';') 中的 stmt:如果 stmt.strip():cur.execute(stmt)行 = cur.fetchone()a = 行[0]打印(一)# =>2

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?

解决方案

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

这篇关于如何使用pymysql从多个select语句中获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！