Python MySQL 连接器 - 使用 fetchone 时发现未读结果

Python MySQL connector - unread result found when using fetchone

提问by LearningSlowly

I am inserting JSON data into a MySQL database

我正在将 JSON 数据插入 MySQL 数据库

I am parsing the JSON and then inserting it into a MySQL db using the python connector

我正在解析 JSON，然后使用 python 连接器将其插入到 MySQL 数据库中

Through trial, I can see the error is associated with this piece of code

通过试用，我可以看到错误与这段代码有关

for steps in result['routes'][0]['legs'][0]['steps']:
    query = ('SELECT leg_no FROM leg_data WHERE travel_mode = %s AND Orig_lat = %s AND Orig_lng = %s AND Dest_lat = %s AND Dest_lng = %s AND time_stamp = %s')
    if steps['travel_mode'] == "pub_tran":
        travel_mode = steps['travel_mode']
        Orig_lat = steps['var_1']['dep']['lat']
        Orig_lng = steps['var_1']['dep']['lng']
        Dest_lat = steps['var_1']['arr']['lat']
        Dest_lng = steps['var_1']['arr']['lng']
        time_stamp = leg['_sent_time_stamp'] 
    if steps['travel_mode'] =="a_pied":
        query = ('SELECT leg_no FROM leg_data WHERE travel_mode = %s AND Orig_lat = %s AND Orig_lng = %s AND Dest_lat = %s AND Dest_lng = %s AND time_stamp = %s')
        travel_mode = steps['travel_mode']
        Orig_lat = steps['var_2']['lat']
        Orig_lng = steps['var_2']['lng']
        Dest_lat = steps['var_2']['lat']
        Dest_lng = steps['var_2']['lng']
        time_stamp = leg['_sent_time_stamp']
    cursor.execute(query,(travel_mode, Orig_lat, Orig_lng, Dest_lat, Dest_lng, time_stamp))
    leg_no = cursor.fetchone()[0]
    print(leg_no)

I have inserted higher level details and am now searching the database to associate this lower level information with its parent. The only way to find this unique value is to search via the origin and destination coordinates with the time_stamp. I believe the logic is sound and by printing the leg_no immediately after this section, I can see values which appear at first inspection to be correct

我已经插入了更高级别的详细信息，现在正在搜索数据库以将此较低级别的信息与其父级相关联。找到这个唯一值的唯一方法是通过带有时间戳的起点和终点坐标进行搜索。我相信逻辑是合理的，通过在本节之后立即打印 leg_no，我可以看到在第一次检查时出现的值是正确的

However, when added to the rest of the code, it causes subsequent sections where more data is inserted using the cursor to fail with this error -

但是，当添加到代码的其余部分时，它会导致使用游标插入更多数据的后续部分因此错误而失败 -

    raise errors.InternalError("Unread result found.")
mysql.connector.errors.InternalError: Unread result found.

The issue seems similar to MySQL Unread Result with Python

这个问题似乎类似于MySQL Unread Result with Python

Is the query too complex and needs splitting or is there another issue?

查询是否过于复杂，需要拆分还是有其他问题？

If the query is indeed too complex, can anyone advise how best to split this?

如果查询确实太复杂，谁能建议如何最好地拆分它？

EDIT As per @Gord's help, Ive tried to dump any unread results

编辑根据@Gord 的帮助，我试图转储任何未读的结果

cursor.execute(query,(leg_travel_mode, leg_Orig_lat, leg_Orig_lng, leg_Dest_lat, leg_Dest_lng))
            leg_no = cursor.fetchone()[0]
            try:
                cursor.fetchall()
            except mysql.connector.errors.InterfaceError as ie:
                if ie.msg == 'No result set to fetch from.':
                    pass
                else:
                    raise
            cursor.execute(query,(leg_travel_mode, leg_Orig_lat, leg_Orig_lng, leg_Dest_lat, leg_Dest_lng, time_stamp))

But, I still get

但是，我仍然得到

raise errors.InternalError("Unread result found.")
mysql.connector.errors.InternalError: Unread result found.
[Finished in 3.3s with exit code 1]

scratches head

抓头

EDIT 2 - when I print the ie.msg, I get -

编辑 2 - 当我打印 ie.msg 时，我得到 -

No result set to fetch from

采纳答案by LearningSlowly

All that was required was for bufferedto be set to true!

所需要的只是buffered将其设置为 true！

cursor = cnx.cursor(buffered=True)

The reason is that without a buffered cursor, the results are "lazily" loaded, meaning that "fetchone" actually only fetches one row from the full result set of the query. When you will use the same cursor again, it will complain that you still have n-1 results (where n is the result set amount) waiting to be fetched. However, when you use a buffered cursor the connector fetches ALL rows behind the scenes and you just take one from the connector so the mysql db won't complain.

原因是没有缓冲游标，结果是“延迟”加载的，这意味着“fetchone”实际上只从查询的完整结果集中提取一行。当您再次使用同一个游标时，它会抱怨您仍然有 n-1 个结果（其中 n 是结果集数量）等待获取。但是，当您使用缓冲游标时，连接器会在幕后获取所有行，而您只需从连接器中取出一行，这样 mysql 数据库就不会抱怨。

回答by Gord Thompson

I was able to recreate your issue. MySQL Connector/Python apparently doesn't like it if you retrieve multiple rows and don't fetch them all before closing the cursor or using it to retrieve some other stuff. For example

我能够重现你的问题。MySQL 连接器/Python 显然不喜欢它，如果您检索多行并且在关闭游标或使用它来检索其他一些内容之前不全部获取它们。例如

import mysql.connector
cnxn = mysql.connector.connect(
    host='127.0.0.1',
        user='root',
        password='whatever',
        database='mydb')
crsr = cnxn.cursor()
crsr.execute("DROP TABLE IF EXISTS pytest")
crsr.execute("""
CREATE TABLE pytest (
    id INT(11) NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(20),
    PRIMARY KEY (id)
    )
""")
crsr.execute("INSERT INTO pytest (firstname) VALUES ('Gord')")
crsr.execute("INSERT INTO pytest (firstname) VALUES ('Anne')")
cnxn.commit()
crsr.execute("SELECT firstname FROM pytest")
fname = crsr.fetchone()[0]
print(fname)
crsr.execute("SELECT firstname FROM pytest")  # InternalError: Unread result found.

If you only expect (or care about) one row then you can put a LIMITon your query

如果您只期望（或关心）一行，那么您可以LIMIT在查询中添加一个

crsr.execute("SELECT firstname FROM pytest LIMIT 0, 1")
fname = crsr.fetchone()[0]
print(fname)
crsr.execute("SELECT firstname FROM pytest")  # OK now

or you can use fetchall()to get rid of any unread results after you have finished working with the rows you retrieved.

或者，您可以fetchall()在处理完检索到的行后使用删除任何未读结果。

crsr.execute("SELECT firstname FROM pytest")
fname = crsr.fetchone()[0]
print(fname)
try:
    crsr.fetchall()  # fetch (and discard) remaining rows
except mysql.connector.errors.InterfaceError as ie:
    if ie.msg == 'No result set to fetch from.':
        # no problem, we were just at the end of the result set
        pass
    else:
        raise
crsr.execute("SELECT firstname FROM pytest")  # OK now

回答by Natty

There is also a possibility that your connection to MySQL Workbench is disconnected. Establish the connection again. This solved the problem for me.

您与 MySQL Workbench 的连接也有可能断开。重新建立连接。这为我解决了这个问题。

回答by Abd_bgc

Would setting the cursor within the for loop, executing it, and then closing it again in the loop help? Like:

在 for 循环中设置游标，执行它，然后在循环中再次关闭它有帮助吗？喜欢：

for steps in result['routes'][0]['legs'][0]['steps']:
    cursor = cnx.cursor()
    ....
    leg_no = cursor.fetchone()[0]
    cursor.close()
    print(leg_no)