Python MySQL连接器-使用fetchone时发现未读结果 [英] Python MySQL connector - unread result found when using fetchone

问题描述

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

I am inserting JSON data into a MySQL database

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

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

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

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)

我插入了较高级别的详细信息，现在正在搜索数据库以将此较低级别的信息与其父级相关联.找到此唯一值的唯一方法是通过带有time_stamp的起点和终点坐标进行搜索.我相信逻辑是正确的，并且在此部分之后立即打印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

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

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.

问题似乎类似于 MySQL使用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?

编辑根据@Gord的帮助，Ive尝试转储所有未读的结果

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

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))

但是，我仍然得到

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

抓头

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

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

No result set to fetch from

推荐答案

所需的全部内容是

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

cursor = cnx.cursor(buffered=True)

原因是，没有缓冲的游标，结果将延迟"加载，这意味着"fetchone"实际上仅从查询的整个结果集中获取一行.当您再次使用同一光标时，它会抱怨您仍有n-1个结果(其中n是结果集数量)正在等待获取.但是，当您使用带缓冲的游标时，连接器会在后台获取所有行，而您只需从连接器中取出一行，这样mysql db就不会抱怨.

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.

这篇关于Python MySQL连接器-使用fetchone时发现未读结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！