Use a query in sqlite value - python

Question

Does anyone know how to execute a query inside a value in python sqlite

The eroor i am getting is: sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

my code is here:

Name = input("Enter the name of the product you want to purchase: >>")
item = Name
qty = input("Enter the Quantity of the product you want to purchase: >>")

today = date.today()
cursor = db.cursor()
cursor.execute("SELECT CatID from Products where Name=?",(Name,))
result = cursor.fetchall()

 confirm = input("are you sure you want tot buy this product (y/n): >>" )

if confirm == "y":

     ### In this query where it says result i want to execute the data from the result query
cursor.execute("INSERT INTO OrderHistory(Username,Category,Date,Qty,ItemHistory) Values(?,?,?,?,?)",(Username,result,today,qty,Name))

db.commit()

print("Product purchased. Thankyou for your order")

cursor.execute("UPDATE Products SET Qty = (? -1) where Name = ?",(qty,item,))

else:

print("The program will now terminate")

`result` can be iterator`. You have to get single row from interator and later single element from row. — furas, Jan 12 '17 at 22:01
your code have empty space at left side so we have to scroll it to see all code. Change it. — furas, Jan 12 '17 at 22:02
@furas i don't have a clue how to do that can you please help — King YT, Jan 12 '17 at 22:12
if you need only one result from `SELECT` then use `row = fetchone()` and then you should have your ID as `row[0]` so `result = row[0]` or shorter `result = fetchone()[0]` — furas, Jan 12 '17 at 22:18
@furas i got it to work with by adding result = str(result) but it inserts the value in the data base like this [(7,)] do you know how to make it insert just 7 — King YT, Jan 12 '17 at 22:35
fetchall never returns single value but list of rows and every row has columns - even if you expect only single value or single row. You have to get first row from result and later first column from row `result = str(result[0][0])` — furas, Jan 12 '17 at 22:41
qty = int(input("Enter the Quantity of the product you want to purchase: >>")) cursor = db.cursor() cursor.execute("SELECT Price from Products where Name=?",(Name,)) price = cursor.fetchone() map(float(price)) newprice = price * qty — King YT, Jan 12 '17 at 23:12
TypeError: float() argument must be a string or a number, not 'tuple' — King YT, Jan 12 '17 at 23:12
how can i fix it any body? i want to multiply the qty with the price — King YT, Jan 12 '17 at 23:12
see my previous comment - `fetchone()` always return row (with columns) even if there is only one value - you have to get `price = fetchone()[0]` — furas, Jan 12 '17 at 23:16

score 0 · Accepted Answer · answered Jan 12 '17 at 22:12

0

You can also iterate over result:

for row in result:
    cursor.execute(
    "INSERT INTO OrderHistory(Username,Category,Date,Qty,ItemHistory) SELECT CatID,?,?,?,? FROM Products WHERE Name=?",(Username,row,today,qty,Name))
    db.commit()

answered Jan 12 '17 at 22:12

Bambang

391
1
10

i got it to work with by adding result = str(result) but it inserts the value in the data base like this [(7,)] do you know how to make it insert just 7 – King YT Jan 12 '17 at 22:33
refer to this: http://stackoverflow.com/questions/12867140/python-mysqldb-get-the-result-of-fetchall-in-a-list – Bambang Jan 12 '17 at 22:39
thanks anyway done by stripping the string works fine – King YT Jan 12 '17 at 22:55

Use a query in sqlite value - python

1 Answers1