Menu
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
695
close() Method. Use close() when you are done using a cursor. This method closes the cursor, resets all results, and ensures that the cursor object has no reference to its original connection object.
fetchall() Method. The method fetches all (or all remaining) rows of a query result set and returns a list of tuples. If no more rows are available, it returns an empty list.
(A buffered cursor fetches and buffers the rows of a result set after executing a query; see Section 10.6. 1, “cursor. MySQLCursorBuffered Class”.) This way, it is unnecessary to fetch the rows in a new variables. Instead, the cursor can be used as an iterator.
If buffered is True , the cursor fetches all rows from the server after an operation is executed. This is useful when queries return small result sets. buffered can be used alone, or in combination with the dictionary or named_tuple argument.
It would appear that you need:
cursor = conn.cursor(buffered=True,dictionary=true)
in order to abandon a resultset mid-stream.
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}]
All you have to do is pass buffered = true in your cursor. Read more official docs
cursor = conn.cursor(buffered=True)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With