SQL Like statement with variable in python

Question

Here is part of the code:

import sqlite3
from PIL import Image,ImageTk
import cv2
import numpy as np
from tkinter import Tk, Label,Button,Toplevel,Entry

def display():
    m,n=0,0
    c=str(en.get())
    print(c)
    t=Toplevel()
    d="%"+c+"%"
    cursor = conn.execute("""SELECT * from (select * from
        user_1 union all SELECT *
        from user_3 union all SELECT *
        from user_4 union all SELECT *
        from user_5 union all SELECT *
        from user_2 ) where id like %s""",(d,))
    for row in cursor: 
        print("ID = ", row[0])
        print("IMG = ",row[1])

    t.mainloop()
    print("Operation done successfully");
    conn.close()

w=Tk()
w.title("gui")
en=Entry(w)
en.grid(row=0)
conn = sqlite3.connect('datastorage.db')
print("Opened database successfully");
a=Button(w,text="display",command=display).grid()

The sql LIKE statement is generating a syntax error as follows:

Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\ABC\AppData\Local\Programs\Python\Python37\lib\tkinter\__init__.py", line 1705, in __call__
    return self.func(*args)
  File "C:\Users\ABC\Desktop\Python tut\trial.py", line 18, in display
    from user_2 ) where id like %s""",(d,))
sqlite3.OperationalError: near "%": syntax error

c is the string input by the user, it's value is stored in d as I want all strings containing the string c. I know there are many similar questions but none seem to solve my problem. Can anyone please help me out?

@jarlh I have a database from which I am extracting images in the above code. That database has those 5 tables — Athena
– Athena, Commented Apr 7, 2020 at 13:26

Gabio · Accepted Answer · 2020-04-07 13:17:03Z

2

try the following:

c = str(en.get())
sql = f"""SELECT * from (select * from
        user_1 union all SELECT *
        from user_3 union all SELECT *
        from user_4 union all SELECT *
        from user_5 union all SELECT *
        from user_2 ) where id like '%{c}%'"""
cursor = conn.execute(sql)

answered Apr 7, 2020 at 13:17

Gabio

9,5643 gold badges17 silver badges38 bronze badges

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

1 Comment

Athena Over a year ago

I'll do that as soon as it lets me, there is a timer

Collectives™ on Stack Overflow

SQL Like statement with variable in python

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