Counting Items Inside a SQLite table Based on 2 Arguments

July 20, 2022

I currently have a table like the below example…

id	rack	rack_loc	ent_date	product	serial	state	status
1	RACK 7	A	05/05/2022	ENT5000X	8675762	READY	READY

I want to return the COUNT of products with status, READY.

I currently have a viable set from the user with the product called prod (which will contain the same text as the product column), I then have the below code which results in an error.

cursor.execute('''SELECT COUNT(*) FROM test_data WHERE product = prod AND status = 'READY';''')

The error I get is no such column: prod

What I would expect was a returned value of 1, or more if table was full.

Any help on this would be great

Thanks

>Solution :

Just use placeholders (?) and query parameters for the dynamic data.

prod = "ENT5000X"  # or whatever
cursor.execute(
    "SELECT COUNT(*) FROM test_data WHERE product = ? AND status = ?",
    [prod, "READY"],
)
count = cursor.fetchone()[0]  # `count` is 0 or more