I cannot enter user-defined values in Sqlite

Question

I have a problem where I can not add anything in the table and always appears the error:

sqlite3.Interfaceerror: Error Binding Parameter 0 - probably Unsupported type.

The code I’m trying to add values and this:

  p_name=input("Digite o nome do produto: "),
  p_desc=input("Digite a descrição: "),
  p_costp=input("Digite o preço de custo: "),
  p_sellp=input("Digite o preço de venda: ")
  c.execute("INSERT INTO products (name,description,costp,sellp) VALUES
(?,?,?,?)",(p_name,p_desc,p_costp,p_sellp))

And my Table:

c.execute("CREATE TABLE IF NOT EXISTS products(
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name TEXT, 
    description TEXT, 
    costp REAL, 
    sellp REAL)")

Insert with predefined values works well, but values received from the user I can’t and always appears this error.

Answer 1

First, use REAL to store monetary values is a mistake.

If you want to insist, you need to convert the data received by input() in a floating point type to match the type REAL, thus:

c.execute("INSERT INTO products (name, description, costp, sellp)
              VALUES (?, ?, ?, ?)", (p_name, p_desc, float(p_costp), float(p_sellp)))

I put in the Github for future reference.

This is a simplistic way, but it works if the value is typed correctly. Ideally the conversion would be done before and would treat exception if it does not work. But as it seems to be only a test, this is not so important.

Answer 2

a way to be passing the values to your very useful sql that always works, is using the passage of parameters by tuples.

c.execute("INSERT INTO products (name,description,costp,sellp) VALUES ('%s','%s',%f,%f)" %(p_name,p_desc,p_costp,p_sellp))

preferably, don’t forget the simple quotation marks for type values TEXT