How do I apply selected data entry to my to sql update function?

Question

I am able to access the database and populate the listbox but unable to conduct a search based on text in the Entry String.

def find_roster(num_input):
global cursor
cursor.execute("""SELECT num, firstname, surname, duty FROM active WHERE num='%s'""" %(num_input))
rows = cursor.fetchall()
   dbi.close()
    for results in rows:
  rosterList.insert("end", results)
return rows

numLabel=Label(root, text="Employee #")
numLabel.grid(row=0,column=0)

findButt=Button(root, text="Find", width=12, command=find_roster)
findButt.grid(row=1, column=5)


num_input=StringVar()
num_input=Entry(root,textvariable=num_input)
num_input.grid(row=0,column=1)

I have selected the specific syntax

Here is the error: TypeError: find_roster() missing 1 required positional argument: 'num_input'

I appreciate any direction.

Where do you call `find_roster()`? Provide enough code for us to the problem. And consider using parameterization not string interpolation especially if *num_input* is user entered text. — Parfait, Dec 26 '17 at 20:45

Parfait · Answer 1 · 2017-12-26T22:05:38.263

1

Simply use lambda for the Tkinter command callback and pass in num_input value as parameter. Also, adjust your database connection and cursor objects to work with your process and use SQL parameterization:

dbi = pymysql.connect(...)  # OUTSIDE OF FUNCTION

def find_roster(n):    
   cursor = dbi.cursor()
   cursor.execute("SELECT num, firstname, surname, duty FROM active WHERE num = %s", (n,))

   rows = cursor.fetchall()    
   for results in rows:
       rosterList.insert("end", results)

   cursor.close()    
   return rosterList

#... other code

findButt=Button(root, text="Find", width=12, command=lambda: find_roster(num_input))

dbi.close()                 # OUTSIDE OF FUNCTION

edited Dec 26 '17 at 22:05

answered Dec 26 '17 at 21:27

Parfait

104,375
17
94
125

Getting an Error: line 1071, in _execute_command raise err.InterfaceError("(0, '')") pymysql.err.InterfaceError: (0, '') – Raymond Dec 26 '17 at 21:52
You attempt to return the cursor fetch but close the connection object! See [here](https://stackoverflow.com/q/6650940/1422451). I update for adjusted flow with parameterization. You may need to pass *rosterList* into function as well. – Parfait Dec 26 '17 at 22:07
cursor.execute("""SELECT num, firstname, surname, assign FROM active_roster WHERE num = %s"""% (n,)) rows = cursor.fetchall() for results in rows: rosterList.insert("end", results) cursor.close() print(rows) return rows I HAD TO MAKE IT 'RETURN ROWS' TO START WORKING HOWEVER THE 'N' IS EMPTY I AM GETTING - Truncated incorrect DOUBLE value AND ALL OF THE NUM COLUMN – Raymond Dec 26 '17 at 22:50
doesnt like fetchone() either – Raymond Dec 26 '17 at 22:54
When I print (n) it is a string (ie .12323212323232) I need this to be text input. Also as per 'cursor fetch' it appear to attempting query correctly less the actual 'n' value – Raymond Dec 27 '17 at 01:55
def find_roster(n=""): global cursor cursor.execute("""SELECT num, firstname, surname, assign FROM active WHERE num='%s'"""%(n)) ALMOST THERE BUT 'N" A STRING AND SHOULDNT BE - NEED HELP – Raymond Dec 27 '17 at 02:27
You reverted back to string interpolation which is highly ill-advised especially with user input (i.e., [bobby tables](http://bobby-tables.com/)). Read on [sql parameterization](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html). My solution here does not concatenate values to strings but uses the second parameter in execute: `cursor.execute(querystring, params)`. It is confusing but both use same placeholders `%s`. Run it the way it is coded in this proposed answer. Adjust *rows* as needed. – Parfait Dec 27 '17 at 03:49

score 0 · Answer 2 · answered Jan 20 '18 at 16:09

0

I solved this myself (late update). This issue was simply resolved by adding

def find_roster(n):
   n=num_input.get()

Had nothing to do with DB query just simple syntax omission..

answered Jan 20 '18 at 16:09

Raymond

40
8

How do I apply selected data entry to my to sql update function?

2 Answers2