python - 在Python中从两个MySQL表中检索数据？

Question

我正在使用Python，并且尝试从同一数据库(name = begin)中的两个mySQL表(name = deal_info和profile)检索数据。这两个表未链接或没有相同的主键，但我收到错误

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

下面是代码

import mysql.connector
from decimal import *

cnx = mysql.connector.connect(user='root', password = 'xxx', database='begin')
cursor = cnx.cursor()

query_deal_info = ("SELECT mrp, qty_remain, deal_price, qty, asp FROM deal_info WHERE deal_id = %s")
deal_id = int(input("Enter the deal id:"))
cursor.execute(query_deal_info,(deal_id,))
query_profile = ("SELECT user_id, abp1, abp2, abp3, abpf FROM profile WHERE user_id = %s")
user_id = int(input("Enter the user id:"))
cursor.execute(query_profile,(user_id,))

for (mrp, qty_remain, deal_price, qty, asp) in cursor:
 print("The MRP for the deal is {}".format(mrp))
 print ("The deal price is {}, asp is {} and qty is {}".format(deal_price, asp, qty))

for (user_id, abp1) in cursor:
  print("The ABP1, 2, 3 and f for the deal is {}, {}".format(user_id, abp1))

cursor.close()
cnx.close()

Answer 1

根据 MySQL.connector docs ，您需要在执行任何其他语句之前获取结果:

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.

因此，只需在 cursor.execute() 之后运行循环打印行即可:

cnx = mysql.connector.connect(user='root', password = 'xxx', database='begin')
cursor = cnx.cursor()

# DEAL INFO
query_deal_info = "SELECT mrp, qty_remain, deal_price, qty, asp \
                    FROM deal_info WHERE deal_id = %s"
deal_id = int(input("Enter the deal id:"))
cursor.execute(query_deal_info,(deal_id,))

for (mrp, qty_remain, deal_price, qty, asp) in cursor:
    print("The MRP for the deal is {}".format(mrp))
    print ("The deal price is {}, asp is {} and qty is {}".format(deal_price, asp, qty))

# PROFILE
query_profile = "SELECT user_id, abp1, abp2, abp3, abpf \
                  FROM profile WHERE user_id = %s"
user_id = int(input("Enter the user id:"))
cursor.execute(query_profile,(user_id,))

for (user_id, abp1) in cursor:
    print("The ABP1, 2, 3 and f for the deal is {}, {}".format(user_id, abp1))

cursor.close()    
cnx.close()