python mysql query not displaying correct result

Question

I have a code here and it's supposed to show all results where the selection from combobox like id = the value in the entry box. But when the code is executed I get a empty set "[]", it skips to the if statement which gives me a messagebox, despite the fact that the database is populated and it's displaying properly with the other function that I have defined before. Is there a mistake in my code or am I doing anything wrong here?

Thanks in advance :)

Take a look at the code :

def sp_patient():
        #Creating window
        sp_pat = Toplevel(update)
        sp_pat.title('Choose Patient')

        def search():
            #Assigning variable to .get()
            a = drops.get()

            if a == 'id' or a == 'emirate_id' or a == 'email_adress' or a == 'gender' or a == 'DOB' or a == 'blood_grp' or a == 'COVID_test':

                #Establishing connection
                con = mysql.connect(host='', user='',
                                    password='', database='')
                # Making SQL command
                sql_command = "SELECT * FROM patient_infos where %s = %s ;"
                values = a , e_1.get()
                c = con.cursor()
                c.execute(sql_command, values)

                # Executing and saving SQL command
                records = c.fetchall()
                print(records)
                if records == []:
                    messagebox.showinfo('Does not exist!','Sorry such patient does not exist')
                else:
                    #Creating window
                    result_win = Toplevel(sp_pat)
                    result_win.title('Search result')
                    index=0
                    for index,x in enumerate(records):
                        num=0
                        for y in x:
                            lookup_label = Label(result_win,text=y)
                            lookup_label.grid(row=index+1,column=num)
                            num += 1
                    #Closing connection
                    con.close()

                    #Creating column header and exit button
                    l_1 = Label(result_win,text='ID',font=font_text)
                    l_2 = Label(result_win,text='Full Name',font=font_text)
                    l_3 = Label(result_win,text='Phone no.',font=font_text)
                    l_4 = Label(result_win,text='Emirates ID',font=font_text)
                    l_5 = Label(result_win,text='Email addr.',font=font_text)
                    l_6 = Label(result_win,text='Gender',font=font_text)
                    l_7 = Label(result_win,text='DOB',font=font_text)
                    l_8 = Label(result_win,text='Nationality',font=font_text)
                    l_9 = Label(result_win,text='Blood group',font=font_text)
                    l_10 = Label(result_win,text='COVID test',font=font_text)
                    l_11 = Label(result_win,text='Emergency no.',font=font_text)
                    btn_ext = Button(result_win,text='Exit',font=font_text,command=result_win.destroy,borderwidth=2,fg='#eb4d4b')

                    #Placing it in screen
                    l_1.grid(row=0,column=0,padx=20)
                    l_2.grid(row=0,column=1,padx=20)
                    l_3.grid(row=0,column=2,padx=20)
                    l_4.grid(row=0,column=3,padx=20)
                    l_5.grid(row=0,column=4,padx=20)
                    l_6.grid(row=0,column=5,padx=20)
                    l_7.grid(row=0,column=6,padx=20)
                    l_8.grid(row=0,column=7,padx=20)
                    l_9.grid(row=0,column=8,padx=20)
                    l_10.grid(row=0,column=9,padx=20)
                    l_11.grid(row=0,column=10,padx=20)
                    btn_ext.grid(row=index+2,columnspan=11,ipadx=240,sticky=E+W)

mechanical_meat · Accepted Answer · 2020-05-18 16:33:42Z

1

You cannot pass column names to the query like that.
You'll have to do it in two steps:

do the string substitution,
pass just the value to the query

                # Making SQL command
                sql_command = "SELECT * FROM patient_infos where {} = %s;"
                c = con.cursor()
                sql_command = sql_command.format(a)
                c.execute(sql_command, (e_1.get(),))

edited May 18, 2020 at 16:33

answered May 18, 2020 at 16:16

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

3 Comments

Delrius Euphoria Over a year ago

Im getting this error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '} = '3'' at line 1

Delrius Euphoria Over a year ago

but this is my line of code now ,

sql_command = "SELECT * FROM patient_infos where {} = %s;"                 c = con.cursor()                 sql_command.format(a)                 c.execute(sql_command, (e_1.get(),))

mechanical_meat Over a year ago

@CoolCloud: my mistake. I updated my code sample. Please try that out.

Collectives™ on Stack Overflow

python mysql query not displaying correct result

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related