如何修复错误：sqlite3.Connection'对象没有属性“fetchone”；在sqlite查询中

Question

我对这段代码有点头疼。当我在输入提示（SQL2）中写“A”时，它似乎工作正常，但是当我写“D”时，（SQL1）我收到错误：

sqlite3.Connection'对象没有属性'fetchone

我对回答任务也不是很有信心，因为 SQL1 给我的输出为零。 所以我将发布任务文本和我的代码。抱歉，文字墙。

任务：

检索产品名称列表、订购这些产品的客户的姓名、订单号、订单日期和总金额以及产品的供应商名称

仅当供应商的公司名称以“E”开头时才必须包含结果。结果必须根据供应商的公司名称进行排序。供应商的公司名称必须按字母顺序 (AZ) 排序。

一旦获得检索到的信息，您必须显示适当的消息和结果集。如果用户想查看产品有货时的结果，则仅显示（打印）查询返回的行数

如果用户想查看产品停产时的结果，则仅显示（打印）该行数查询返回的行数。如果用户输入了错误的选项那么应该使用下面的代码来显示错误的输入消息，包括错误的输入。下面提供了执行此操作的代码 print

("Invalid input{selection}.".format(selection = DiscOpt))

代码：

       import sqlite3
       conn = sqlite3.connect('Northwind2020.db')
       cursor = conn.cursor()
       DiscOpt = input("Would you like to see customers who ordered available or discontinued
       products.")
       print("Type 'A' for available products. Type 'D' for discontinued products.")

       SQL1 = '''SELECT P.ProductName, C.FirstName, C.LastName, O.OrderNumber, O.OrderDate, 
                 O.TotalAmount, S.CompanyName
                 FROM Customer AS C
                 INNER JOIN [Order] AS O
                 ON C.Id = O.CustomerId
                 INNER JOIN OrderItem AS OI
                 ON O.Id = OI.OrderId
                 INNER JOIN Product AS P
                 ON P.Id = OI.ProductId
                 INNER JOIN Supplier AS S
                 ON S.Id = P.SupplierId
                 WHERE S.CompanyName LIKE 'E%'
                 AND P.IsDiscontinued = 1
                 ORDER BY S.CompanyName ASC;'''

      SQL2 = '''SELECT P.ProductName, C.FirstName, C.LastName, O.OrderNumber, O.OrderDate, 
                O.TotalAmount, S.CompanyName
                FROM Customer AS C
                INNER JOIN [Order] AS O
                ON C.Id = O.CustomerId
                INNER JOIN OrderItem AS OI
                ON O.Id = OI.OrderId
                INNER JOIN Product AS P
                ON P.Id = OI.ProductId
                INNER JOIN Supplier AS S
                ON S.Id = P.SupplierId
                WHERE S.CompanyName LIKE 'E%'
                AND P.IsDiscontinued = 0
                ORDER BY S.CompanyName ASC;'''

     while True:
         if DiscOpt == 'A':
             for row in cursor.execute(SQL2).fetchall():
                 print(row)
     elif DiscOpt == 'D':
             cursor = conn.cursor()
             for row in cursor.execute(SQL1).fethcall():
                 print(row)
     else:
          "Invalid input{selection}.".format(selection = DiscOpt) 

`

Answer 1

这是一个愚蠢的拼写错误，导致我的代码无法正常运行，实际上它甚至拼写在标题中（fethcone 而不是 fetchone）。我只是厌倦了看到它。但我得到了一些帮助，以不那么密集和重复的方式写它。所以我将其发布在下面：

    import sqlite3
    conn = sqlite3.connect('Northwind2020.db')
    cursor = conn.cursor()
    DiscOpt = input("Would you like to see customers who ordered available or 
                     discontinued products.")
    print("Type 'A' for available products. Type 'D' for discontinued 
          products.")

    SQL = '''SELECT P.ProductName, C.FirstName, C.LastName, O.OrderNumber, 
                     O.OrderDate, O.TotalAmount, S.CompanyName
                     FROM Customer AS C
                     INNER JOIN [Order] AS O ON C.Id = O.CustomerId
                     INNER JOIN OrderItem AS OI ON O.Id = OI.OrderId
                     INNER JOIN Product AS P ON P.Id = OI.ProductId
                     INNER JOIN Supplier AS S ON S.Id = P.SupplierId
                     WHERE S.CompanyName LIKE 'E%'
                        AND P.IsDiscontinued = ?
                     ORDER BY S.CompanyName ASC;'''

    while True:
        if DiscOpt == 'A':
            for row in cursor.execute(SQL, (0,)).fetchall():
                print(row)
        elif DiscOpt == 'D':
             for row in cursor.execute(SQL, (1,)).fetchall():
                print(row)
        else:
             "Invalid input{selection}.".format(selection = DiscOpt)