Python sqlite user input search

Question

I'm trying to get the IDs of the films from DB where category equals users input category, but for some reason I'm not able to do it. My code:

import sqlite3

#Connectin to DB
conn = sqlite3.connect('sakila.db')
c = conn.cursor()

#Checking if the connection to the DB is successful
if (conn):
    print("Connection successful")
else:
    print ("Connection unsuccessful")

kategorija=input("Enter the category: ")
c.execute("SELECT FID FROM film_list WHERE category=%s", (kategorija,))
film = c.fetchall()
#Removing duplicates
final_film = list(dict.fromkeys(film))
print(final_film)

The error i get: line 14, in c.execute("SELECT FID FROM film_list WHERE category=%s", (kategorija,)) sqlite3.OperationalError: near "%": syntax error

The parameter placeholder used by sqlite is ?, not %s. Your query should be SELECT FID FROM film_list WHERE category=? — snakecharmerb
– snakecharmerb, Commented Nov 28, 2021 at 10:07

javelin_hurler · Accepted Answer · 2021-11-28 10:23:38Z

2

As you can get here the way to bind python variables to sqlite queries is ?

priority = input("Enter priority : ")
cur = conn.cursor()
cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))
rows = cur.fetchall()

If you want to remove duplicates you could consider changing your query to

SELECT DISTINCT ...

This will change the behavior of sql to only select distinct values (i think this is obvious).

answered Nov 28, 2021 at 10:23

javelin_hurler

711 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitreeey Over a year ago

This is really useful, thank you!

Collectives™ on Stack Overflow

Python sqlite user input search

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related