Using variable combining LIKE and %s% in SQLite and Python

Question

Using:

Python3
SQLite
TKinter

I am currently trying to create a function to search for a keyword in a database, but as soon as I try to combine it with TKinter, it seems to fail.

Here are the relevant lines: (I tried it in a lot of different ways, those 3 lines below seem to work with variables, but not with the input from TKinter, so I thought they might actually work, if I edit them a little.

The problem I got is, that I'm not experienced in TKinter and SQLite yet and worked with those 2 for about 3 days yet.

def searcher(column):

    #Getting input from user (TKinter)
    keyword = tk.Entry(self)
    keyword.pack()

    #Assigning the input to a variable
    kword = keyword.get()

    c.execute("SELECT * FROM my_lib WHERE {kappa} LIKE {%goal%}".format(kappa=column, goal=kword))
    #c.execute("SELECT * FROM my_lib WHERE "+column+"=?", (kword,))
    #c.execute("SELECT * FROM my_lib WHERE {} LIKE '%kword%'".format(column))

I want to check if any of the data CONTAINS the keyword, so basically:

k_word in column_data

and not

column_data == k_word

My question is:

Is there a way to take the user input (by TKinter) and search in the database (SQLite) and check, if any data in the database contains the keyword.

score 1 · Answer 1 · edited May 23 '17 at 12:24

The SQLite docs explain that you can use ? as a placeholder in the query string, which allows you so substitute in a tuple of values. They also advise against ever assembling a full query using variables with Python's string operations (explained below):

c.execute("SELECT * FROM my_lib WHERE ? LIKE ?", (column, '%'+kword+'%'))

You can see above that I concatenated the % with kword, which will get substituted into the second ?. This also is secure, meaning it will protect against SQL Injection attacks if needed.

Docs: https://docs.python.org/2/library/sqlite3.html

Related posts: Escaping chars in Python and sqlite

Thank you so much! This helped a lot. – Fur-gan Jan 21 '17 at 15:38 — Fur-gan, Jan 21 '17 at 15:38

score 0 · Answer 2 · answered Dec 17 '21 at 19:31

0

Try:

kword = "%"+kword+"%"
c.execute("SELECT * FROM my_lib WHERE kappa LIKE '%s'" % kword)

answered Dec 17 '21 at 19:31

Pablo Caldeira

1

Fur-gan · Answer 3 · 2017-01-21T22:58:21.200

-1

After trying over and over again, I got the actual solution. It seems like I can't just add the '%' to the variable like a string, but rather:

c.execute("SELECT * FROM my_lib WHERE {} LIKE '%{}%'".format(column, kword))

edited Jan 21 '17 at 22:58

answered Jan 21 '17 at 22:50

Fur-gan

107
1
3
10

You shoudn't use fstring when querying the DB because it can be used to inject code into the query using SQL comments. – guilfer Jan 23 '22 at 18:34

Using variable combining LIKE and %s% in SQLite and Python

3 Answers3

Linked

Related