Python MySQL connector-使用fetchon时找到未读结果

Question

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

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

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

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，我可以看到第一次检查时出现的值是正确的

但是，当添加到代码的其余部分时，它会导致使用光标插入更多数据的后续部分失败，并出现以下错误-

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

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

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

如果查询确实太复杂，有人能建议如何最好地分割它吗？

根据@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))

但是，我还是

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

抓伤头部

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

No result set to fetch from

Answer 1

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

Answer 2

我能重现你的问题。MySQL Connector/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.

如果您只希望（或关心）一行，那么您可以在查询上放置LIMIT

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

或者在处理完检索到的行之后，可以使用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

Answer 3

所需要的只是^{} to be set to true!

cursor = cnx.cursor(buffered=True)