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

2021-11-20 00:00:00 python mysql

我正在将 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)

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

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

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

推荐答案

所有需要的是 buffered 设置为 true！

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

cursor = cnx.cursor(buffered=True)

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

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.

相关文章