Python mysql.connector InternalError: Unread result found when close cursor

Question

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?

Update:

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

Huygens

Possible duplicate of Python MySQL connector - unread result found when using fetchone – Flex

@Flex No. it isn't a duplicate. Please see my update. – Huygens

we need more code to help you – Denisse

@Denisse Please see my code. – Huygens

How is it not a duplicate then... you are missing the cursor = conn.cursor(buffered=True) .... no? – Himation

I don't want add buffered=True because, as I know, it will read all result immediately which I want to avoid. Am I right? – Huygens

yes you appear to be correct. But you want to abandon. – Himation

@Huygens - I have the same issue. I have cursor that will return 5000+ records, so I don't want to buffer the results and consume all that memory. Additionally, I only need to read an some of records until a certain condition is met. The number of records read is variable, so a limit statement can not be EASILY used. Then after the condition is met, I want to close the cursor, discarding any unread results and continue with the rest of the program. I also don't want to use for i cursor: pass or cursor.fetchall(), etc. It would be great if there was a cursor.discard_remaining_results() method. – Vigilance

Answer 1

It would appear that you need the following in order to abandon a resultset mid-stream.

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

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

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

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

The latter reference above states:

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.

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}]

Himation

But I don't want to fetch left rows from the server. Maybe fetching first 100 rows is enough. – Huygens

nothing like a mysql .... limit offset,pageSize for pagination . [LIMIT {[offset,] row_count | row_count OFFSET offset}] ... LIMIT 100 – Himation

So your answer is impossible and I have to use buffered option. In this point, this question is a duplicate actually. – Huygens

It is impossible because how? It appears to be the only way to avoid the error that you reference in the question. People write pagination routines when necessary or process the whole resultset. – Himation

Query all, fetch some rows from the server, then close cursor with left rows aren't fetched from the server. – Huygens

This answer describes why it is happening. Comments down here suggest ways to deal with it thru limits. – Himation

There are complicated conditions in chooseInstrumentsFromLeaders. It's too hard to put conditions into SQL statement with LIMIT. – Huygens

This answer actually worked for me, correct dictionary=true to dictionary=True though @Himation – Quillon

Answer 2

All you have to do is pass buffered = true in your cursor. Read more official docs

cursor = conn.cursor(buffered=True)

Leaseholder