Fetching data from Microsoft Access database using SELECT QUERY WITH WHERE CLAUSE in python

Question

from tkinter import *

lg = Tk()
lg.state('zoomed')

def view():
   cus = accno.get()
   dis = [cus]
   print(dis)
   import pypyodbc
   con=pypyodbc.win_connect_mdb("D:\\customer_details.mdb")
   cur = con.cursor()

   q = "select * from cus_details where cus_id = '" + cus + "' "
   cur.execute(q,dis)
   result=cur.fetchall()
   Label(lg,text="",font = "Calibri 12 bold",width=2).grid(row=1,column=1)
   Label(lg,text="",font = "Calibri 12",width=2).grid(row=2,column=1)
   Label(lg,text="",font = "Calibri 12",width=2).grid(row=3,column=1)
   Label(lg,text="",font = "Calibri 12",width=2).grid(row=4,column=1)
   Label(lg,text="",font = "Calibri 12",width=2).grid(row=5,column=1)
   Label(lg,text="",font = "Calibri 12",width=2).grid(row=6,column=1)
   Label(lg,text="",font = "Calibri 12",width=2).grid(row=7,column=1)
   Label(lg,text="",font = "Calibri 12",width=10).grid(row=8,column=0)
   Label(lg,text="",font = "Calibri 12",width=10).grid(row=9,column=1)
   Label(lg,text="",font = "Calibri 12",width=10).grid(row=9,column=2)

   Label(lg,text="Customer ID",font = "Calibri 12",width=5).grid(row=9,column=3)
   Label(lg,text="First Name",font = "Calibri 12",width=20).grid(row=9,column=4)
   Label(lg,text="Last Name",font = "Calibri 12",width=15).grid(row=9,column=5)
   Label(lg,text="Address",font = "Calibri 12",width=10).grid(row=9,column=6)
   Label(lg,text="ID Proof",font = "Calibri 12",width=15).grid(row=9,column=7)
   Label(lg,text="A/c No",font = "Calibri 12",width=15).grid(row=9,column=8)
   Label(lg,text="A/c Type",font = "Calibri 12",width=15).grid(row=9,column=9)
   Label(lg,text="Initial Deposit",font = "Calibri                          `                                                     `                    `12",width=15).grid(row=9,column=10)
   r=10
   for row in result:
        Label(lg,text="",font = "Calibri 12",width=10).grid(row=r,column=0)
        Label(lg,text="",font = "Calibri 12",width=10).grid(row=r,column=2)

        Label(lg,text=row[0],font = "Calibri 12",width=5).grid(row=r,column=3)
        Label(lg,text=row[1],font = "Calibri 12",width=10).grid(row=r,column=4)
        Label(lg,text=row[2],font = "Calibri 12",width=20).grid(row=r,column=5)
        Label(lg,text=row[3],font = "Calibri 12",width=10).grid(row=r,column=6)
        Label(lg,text=row[4],font = "Calibri 12",width=10).grid(row=r,column=7)
        Label(lg,text=row[5],font = "Calibri 12",width=10).grid(row=r,column=8)
        Label(lg,text=row[6],font = "Calibri 12",width=10).grid(row=r,column=9)
        Label(lg,text=row[7],font = "Calibri 12",width=10).grid(row=r,column=10)
        r=r+1
   con.close()


     tit = Label(lg,text="BANK MANAGEMENT SYSTEM",font = "Batang 29                  ``   bold",fg = "blue")

` `  tit1 = Label(lg,text="Account Detail",font = "Calibri 15 bold")

``   la1 = Label(lg,text="Account No",font = "Calibri 12")
``         accno = Entry(lg,width=35)

``   but = Button(lg,text="Delete",bg = "green",width=11,height=1,fg =
 ``    "white",font = "Calibri 10 bold")
``   but1 = Button(lg,text="Cancel",bg = "green",width=11,height=1,fg = 
``        "white",font = "Calibri 10 bold")
``    but2 = Button(lg,text="Verify",bg = "green",width=11,height=1,fg = 
``     "white",font = "Calibri 10 bold",command = view) 
``     tit.place(x=600,y=10)

      tit1.place(x=600,y=70)

      la1.place(x=400,y=150)

      accno.place(x=650,y=150)

      but2.place(x=870,y=145)

      lg.mainloop()

I get the following error:

     ['1']
     Exception in Tkinter callback
     Traceback (most recent call last):
  File "C:\Python34\lib\tkinter\__init__.py", line 1533, in __call__
    return self.func(*args)
  File "C:\Python34\pypyodbc-1.3.3\customer_details.py", line 15, in view
    cur.execute(q,dis)
  File "C:\Python34\pypyodbc-1.3.3\pypyodbc.py", line 1470, in execute
    self._BindParams(param_types)
  File "C:\Python34\pypyodbc-1.3.3\pypyodbc.py", line 1263, in
    _BindParams
        raise ProgrammingError('HY000',error_desc)
       pypyodbc.ProgrammingError: ('HY000', 'The SQL contains 0 parameter markers,   ``       but 1 parameters were supplied')

I am facing a problem in fetching and displaying the data in a grid.

This is not the way to build queries. In your current code I guess cur.execute(q) would work instead of cur.execute(q,dis) but you should look at how to build queries correctly i.e. not concatenating cus into the query but providing a parameter marker e.g. %s — roganjosh
– roganjosh, Commented Oct 11, 2016 at 9:31
Basically it's looking for a parameter marker in the query string to know where it should insert dis... but there isn't one because you placed the value of cus into the string directly. 'The SQL contains 0 parameter markers, `` but 1 parameters were supplied' — roganjosh
– roganjosh, Commented Oct 11, 2016 at 9:33
improved readability of error message, removed capitalization, fixed some typos — Maximilian Peters
– Maximilian Peters, Commented Oct 11, 2016 at 13:54
It appears that ODBC doesn't support parameter placeholders with %name syntax, but only ? syntax. — user20416
– user20416, Commented Oct 18, 2016 at 20:51

roganjosh · Accepted Answer · 2016-10-18 21:28:06Z

2

SQL injection is a serious issue and can ultimately destroy your database. The classic to remember is Bobby Tables. For this reason, it's important to build your queries properly to prevent this; that requires some mechanism to "escape" an input so that it cannot be interpreted as a command in itself.

q = "select * from cus_details where cus_id = '" + cus + "' "

This query does not escape anything, since you simply throw the value of cus into your string. cur.execute(q,dis) then fails because there's no marker to explain where the value of dis is supposed to go.

The way to do this is the use placeholders and bindings. In SQLite3 these are ? and in other versions of SQL they are %s. I'm not sure which is expected here. EDIT: From Zev Spitz comment, it seems that it's ? for placeholder in this particular case (see Parameters section).

Therefore, your query would look something like the following:

q = "SELECT * FROM cus_details WHERE cus_id = ?"
cur.execute(q, (cus,))

# Or

q = "SELECT * FROM cus_details WHERE cus_id = %s"
cur.execute(q, (cus,))

edited Oct 18, 2016 at 21:28

answered Oct 11, 2016 at 10:15

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

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

3 Comments

user20416 Over a year ago

From here it would seem that ODBC supports only the ? syntax.

roganjosh Over a year ago

@ZevSpitz Thanks, I should really have dug into documentation myself for that. I've edited the answer to include your link, but left both code options in place as it satisfies a broader range and OP never responded.

user20416 Over a year ago

This link I referenced above is from ADO.NET, which has classes that can connect to ODBC data sources, and thus presumably uses the same syntax. But I think a more authoritative link would be from the pyodbc page (Parameters section).

Collectives™ on Stack Overflow

Fetching data from Microsoft Access database using SELECT QUERY WITH WHERE CLAUSE in python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related