Why my placeholder won't work in python for sqlite3 query

59 Views Asked by At

I am writing a series of very similar sqlite3 query in python:

def spark_plug():
    f = 3 
    s = f - 1
    cursor.execute("SELECT spark_plug FROM periodic_service ORDER BY n DESC LIMIT ?", (s,))
    result = cursor.fetchall();

def air_filter():
    f = 2 
    s = f - 1
    cursor.execute("SELECT air_filter FROM periodic_service ORDER BY n DESC LIMIT ?", (s,))
    result = cursor.fetchall();

this code look similar so i want to use placeholder in the query, however the placeholder doesn't work

i tried:

def periodic(): 
    data=[('spark_plug', 3)]
    cursor.execute("SELECT ? FROM periodic_service ORDER BY n DESC LIMIT ?", data)

but it yield sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

if i change to:

cursor.execute("SELECT ? FROM periodic_service ORDER BY n DESC LIMIT ?", ('spark_plug',3))

it do not retrieve data from db, but become 'spark_plug' 3 times :[('spark_plug',), ('spark_plug',), ('spark_plug',)]

even i tried reduce 2 placeholder to 1:

cursor.execute("SELECT ? FROM periodic_service ORDER BY n DESC LIMIT 3", ('spark_plug'))
or
cursor.execute("SELECT ? FROM periodic_service ORDER BY n DESC LIMIT 3", 'spark_plug')

error still occur: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 10 supplied.

now i changed the placeholder to another parameter, it seem work for me:

    cursor.execute("SELECT spark_plug FROM periodic_service ORDER BY n DESC LIMIT ?", '3' )

however, when the number become grater than 10, it yield error:

cursor.execute("SELECT spark_plug FROM periodic_service ORDER BY n DESC LIMIT ?", '10' )

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

Why my placeholder WONT WORK??

0

There are 0 best solutions below