Python MySQL Connector在游标循环内执行第二条sql语句? [英] Python MySQL Connector executing second sql statement within cursor loop?

问题描述

以下逻辑适用于mysqldb模块(请参见 python mysqldb多个游标以获取相关信息一个连接)，但是在cursor2.execute(sql)上，mysql.connector出现以下错误

The following logic works with the mysqldb module (see python mysqldb multiple cursors for one connection), but I am getting the following error with mysql.connector on cursor2.execute(sql)

找到未读结果."

我意识到我可以使用联接来组合这2个简单的sql语句，并且避免使用第二个游标，但是我的实际示例更加复杂，并且需要第二个sql语句.

I realize that I can use a join to combine these 2 simple sql statements and avoid the need for a second cursor, but my real world example is more complex and requires a second sql statement.

假设我需要执行2条单独的sql语句(1条用于循环，1条在循环内)，那么如何使用mysql.connector模块来完成此操作?

Assuming I need to execute 2 separate sql statements (1 for the loop and 1 inside the loop), how should this be done with the mysql.connector module?

import datetime
import mysql.connector

db = mysql.connector.connect(user='alan', password='please', host='machine1', database='mydb')

cursor1 = db.cursor()
cursor2 = db.cursor()

sql = """
SELECT userid, 
       username,
       date
  FROM user
 WHERE date BETWEEN %s AND %s
"""

start_date = datetime.date(1999, 1, 1)
end_date   = datetime.date(2014, 12, 31)

cursor1.execute(sql, (start_date, end_date))

for (userid, username, date) in cursor1:

    sql = """
        select count(*)
        from request
        where assigned = '%s'
    """ % (userid)

    cursor2.execute(sql)
    requestcount = cursor2.fetchone()[0]

    print userid, requestcount

cursor2.close()
cursor1.close()
db.close()

此mysqldb版本工作正常:

This mysqldb version works just fine:

import datetime
import MySQLdb 

db = MySQLdb.connect(user='alan', passwd='please', host='machine1', db='mydb')

cursor1 = db.cursor()
cursor2 = db.cursor()

sql = """
SELECT userid, 
       username,
       date
  FROM user
 WHERE date BETWEEN %s AND %s
"""

start_date = datetime.date(1999, 1, 1)
end_date   = datetime.date(2014, 12, 31)

cursor1.execute(sql, (start_date, end_date))

for (userid, username, date) in cursor1:

    sql = """
        select count(*)
        from request
        where assigned = '%s'
    """ % (userid)

    cursor2.execute(sql)
    requestcount = cursor2.fetchone()[0]

    print userid, requestcount

cursor2.close()
cursor1.close()
db.close()

推荐答案

默认情况下，MySQL Connector/Python是非缓冲的.这意味着不会自动获取数据，您需要消费" 所有行. (它与MySQLdb一起使用，因为默认情况下该驱动程序正在缓冲.)

MySQL Connector/Python is, by default, non-buffering. This means the data is not fetched automatically and you need to 'consume' all rows. (It works with MySQLdb because that driver is buffering by default.)

使用Connector/Python时，必须将设置为True的buffered-argument用作用作迭代器的游标.在OP的问题中，这将是cursor1:

Using Connector/Python you have to use the buffered-argument set to True for cursor you use as iterator. In the OP's question, this would be cursor1:

cursor1 = db.cursor(buffered=True)
cursor2 = db.cursor()

您还可以使用buffered=True作为连接参数，以使所有光标缓冲由此连接缓冲实例化.

You can also use buffered=True as connection argument to make all cursor buffering instantiated by this connection buffering.

这篇关于Python MySQL Connector在游标循环内执行第二条sql语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！