Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.9k views
in Technique[技术] by (71.8m points)

python - Reducing item in SQLite DB by a certain percentage

I'm trying to take a user input to reduce the price of an item in my database by the user input as a percentage.

currently I'm getting the error:

    currentPriceRowFetch = currentPriceSelect.fetchone()
AttributeError: 'str' object has no attribute 'fetchone'

However I would not have expected currentPriceSelect to be read as a string as it should be an SQL statement. Not sure where I've gone wrong here.

  elif userinput == "6":
                    print(pd.read_sql_query("SELECT * FROM Products", conn))
                    itemToReduce = input("Enter the id of the item to put on sale: ")
                    salePercentage = input("Enter the percentage to reduce the item by: ")
    
                    itemSale = '''UPDATE Products
                                SET ProductPrice = ?
                                WHERE ProductID = ?
                            '''
                    currentPriceSelect = "SELECT * FROM Products WHERE ProductID = ?"
                    conn.execute(currentPriceSelect, itemToReduce)
                    currentPriceRowFetch = currentPriceSelect.fetchone()
                    currentPrice = currentPriceRowFetch[3]
                    salePercentage = salePercentage / 100
                    newCost = currentPrice - (currentPrice * salePercentage)
                    conn.execute(itemSale, (newCost, itemToReduce))
                    conn.commit()
                    print("Old price was " + currentPrice)
                    print("New price is " + newCost)

I have changed the currentPriceSelect to this:

currentPriceSelect = conn.execute("SELECT * FROM Products WHERE ProductID = ?")

Now I am getting a new error:

 currentPriceSelect = conn.execute("SELECT * FROM Products WHERE ProductID = ?")
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.

Updated code:

 elif userinput == "6":
                print(pd.read_sql_query("SELECT * FROM Products", conn))
                itemToReduce = input("Enter the id of the item to put on sale: ")
                salePercentage = input("Enter the percentage to reduce the item by: ")

                itemSale = '''UPDATE Products
                            SET ProductPrice = ?
                            WHERE ProductID = ?
                        '''
                currentPriceSelect = conn.execute("SELECT * FROM Products WHERE ProductID = ?"), itemToReduce
                currentPriceRowFetch = currentPriceSelect.fetchone()
                currentPrice = currentPriceRowFetch[3]
                salePercentage = salePercentage / 100
                newCost = currentPrice - (currentPrice * salePercentage)
                conn.execute(itemSale, (newCost, itemToReduce))
                conn.commit()
                print("Old price was " + currentPrice)
                print("New price is " + newCost)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
等待大神答复

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...