使用SELECT QUERY和WHERE CLAUSE在python中从Microsoft Access数据库中获取数据 [英] Fetching data from Microsoft Access database using SELECT QUERY WITH WHERE CLAUSE in python

问题描述

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()

我收到以下错误:

     ['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.

推荐答案

SQL注入是一个严重的问题，最终可能破坏数据库.要记住的经典方法是鲍比表.因此，正确构建查询以防止这种情况很重要.这就需要某种机制来转义"输入，以便不能将其本身解释为命令.

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 + "' "

此查询不会转义任何内容，因为您只需将cus的值放入字符串中即可. cur.execute(q,dis)然后失败，因为没有标记来解释dis的值应该去哪里.

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.

执行此操作的方法是使用占位符和绑定.在SQLite3中为?，在其他SQL版本中为%s.我不确定在这里期望哪个.从Zev Spitz的评论来看，它似乎是(请参见参数部分).

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. 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,))

这篇关于使用SELECT QUERY和WHERE CLAUSE在python中从Microsoft Access数据库中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！