Query's using name parameters

Question

Trying to learn, copying code from different examples, I write the program:

import sqlite3, datetime

test_data = [
      {"fpath": "path/to/file/one.dat","n_measure": 1, "treatment": "Control", "amplitude": 50.5,},
      {"fpath": "path/to/file/two.dat", "n_measure": 2, "treatment": "Control", "amplitude": 76.5,},
      {"fpath": "path/to/file/three.dat", "n_measure": 1, "treatment": "Experimental", "amplitude": 5.5,},
  ]

SCHEMA = """CREATE TABLE data (fpath TEXT, n_measure INTEGER, treatment TEXT, amplitude REAL)"""
Cnx = sqlite3.connect(':memory:')
Cnx.execute(SCHEMA)

for item in test_data:
      Cnx.execute(
          "INSERT INTO data (fpath, n_measure, treatment, amplitude) VALUES(:fpath, :n_measure, :treatment, :amplitude)",
          item,
      )
      Cnx.commit()  # commit after each addition

# Changing the 'row_Factory' property to the 'row' class 
Cnx.row_factory = sqlite3.Row

# Getting the data
Cursor = Cnx.cursor()
# 1
amplitude = 5.5
table = Cursor.execute("SELECT * FROM data where amplitude = :amplitude", amplitude).fetchall()
# 2
treatment = "Experimental"
table = Cursor.execute("SELECT * FROM data where treatment = :treatment", treatment).fetchall()
# 3
n_measure = 1
table = Cursor.execute("SELECT * FROM data where n_measure = :n_measure", n_measure).fetchall()

I encounter problems such as: If I run the first SELECT, I get the error:

ValueError: parameters are of unsupported type

When I execute the second, I get:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 12 supplied.

In the third, I also get:

ValueError: parameters are of unsupported type

But in the latter, if the n_measure variable changes the INTEGER value 1 by the STRING '1', the SQL runs correctly.

Then change the value of the amplitude variable of REAL value 5.5 to the STRING '5.5'; and I get the error in first SELECT:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.

I guess I'm doing incorrect use of data types; But I can not understand how I should do it. Use Python 3.9, SQLite3 and Windows 10.

Answer 1

It's because second parameter of Cursor.execute supposed to be iterable. If you wrap all examples into the tuple

amplitude = 5.5
table = Cursor.execute("SELECT * FROM data where amplitude = :amplitude", (amplitude, )).fetchall()

Everything will work. Integer is not an Iterable, that's why you get pointed ValueError. str is legit iterable, so this

s = "ab"
c.execute("SELECT * FROM data where col1 = ? and col2 = ?", s)

Evaluates to

SELECT * FROM data where col1 = 'a' and col2 = 'b'

Notice that len("5.5") == 3 and len("Experimental") == 12