Python mysql.connector InternalError:关闭游标时发现未读结果 [英] Python mysql.connector InternalError: Unread result found when close cursor

问题描述

我想从游标中读取部分结果，然后将其关闭而不读取所有结果. cursor.close()引发InternalError: Unread result found.是否可以关闭游标而不遍历所有结果或使用 buffer选项?

I want to read part of result from cursor and then close it without reading all result. cursor.close() raises InternalError: Unread result found. Is it possible to close cursor without iterating through all result or using buffer option?

更新:

我的查询获得约3000条记录，我的目标是首先获得一些符合某些条件的记录.经过部分结果的迭代之后，我得到了我想要的.然后我只想放弃未读的结果.我不使用缓冲区选项，据我所知，该选项将立即读取所有结果.这个问题不是 Python MySQL连接器的重复-在以下情况下发现未读结果使用fetchone

My query get about 3000 records, I aim to getting first several records which fit some conditions. After iterating through part of result, I get what I want. Then I want to just abandon unread result. I don't use buffer option which, as I know, will read all result immediately. This question is Not duplicate of Python MySQL connector - unread result found when using fetchone

def chooseInstrumentsFromOrigin(self, time):
    sql = """select symbol, name, total_ratio, outstanding_ratio from market_values
            where time = %s order by {captype} asc""".format(captype=self.strategy_data['captype'])

    args = [time]

    conn = mysql.connector.connect(**mysql_config)
    cursor = conn.cursor(dictionary=True)
    cursor.execute(sql, args)

    # This function will return half way.
    symbols = self.chooseInstrumentsFromLeaders(time, cursor)

    # I don't want this line!
    for i in cursor: pass

    cursor.close()
    conn.close()

    return symbols

推荐答案

您似乎需要:

cursor = conn.cursor(buffered=True,dictionary=true)

以便放弃中间结果集.

in order to abandon a resultset mid-stream.

完全公开，我是mysql开发人员，而不是python开发人员.

Full disclosure, I am a mysql dev, not a python dev.

请参见Python手册页 MySQLConnection .cursor()方法和光标.MySQLCursorBuffered类.

See the Python Manual Page MySQLConnection.cursor() Method and cursor.MySQLCursorBuffered Class.

立即读取所有行，为true.非常适合中小型结果集.

All rows are read immediately, true. Fantasic for small to mid-sized resultsets.

后面的参考文献指出:

对于使用缓冲游标执行的查询，请使用行提取方法 例如fetchone()从缓冲行集中返回行.为了 非缓冲游标，直到 调用行获取方法.在这种情况下，您必须确保获取 在执行任何其他语句之前，结果集的所有行 相同的连接或InternalError(找到未读结果) 将会引发异常.

For queries executed using a buffered cursor, row-fetching methods such as fetchone() return rows from the set of buffered rows. For nonbuffered cursors, rows are not fetched from the server until a row-fetching method is called. In this case, you must be sure to fetch all rows of the result set before executing any other statements on the same connection, or an InternalError (Unread result found) exception will be raised.

作为旁注，您可以使用分页来修改策略. MySQL LIMIT clause支持使用offset，pageSize设置:

As a side note, you can modify your strategy by using pagination. The MySQL LIMIT clause supports this with the offset,pageSize settings:

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

这篇关于Python mysql.connector InternalError:关闭游标时发现未读结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！