Python - Searching for a Record
We'll briefly cover searching for a record in your database. We'll have a look at dropdown lists, as well. We want something like this:
So you can enter a family name (we've missed the label off for convenience sake), select a Job Title, and then click the search button. If you wanted to, you could add some text boxes and display the results of the search in them. But we'll just print the results out.
Create a new tab object called tab3, highlighted in bold below:
tab_parent = ttk.Notebook(form)
tab1 = ttk.Frame(tab_parent)
tab2 = ttk.Frame(tab_parent)
tab3 = ttk.Frame(tab_parent)
Add it to the tab parent:
tab_parent.add(tab1, text="All Records")
tab_parent.add(tab2, text="Add New Record")
tab_parent.add(tab3, text="Search")
Add a String Var and Entry text widget where you can search for a family name:
search_text = tk.StringVar()
search_family = tk.Entry(tab3, textvariable=search_text)
You can add the new widget code near the bottom, just after your grid lines for tab two but before the load_database_results line.
You need some contents to go in your dropdown list. This can come from a Python sequence:
contents = {'Graphic Artist', 'IT Manager', 'Programmer', 'Systems Analyst', 'Support'}
Now set up two String Variables:
search_text_var = tk.StringVar()
options_var = tk.StringVar()
The first one will be used to for the Entry box while the second one is used to set the default value for the dropdown. We can also use the second one to get which item was selected in the Options Menu (OptionMenu is the name of a dropdown list in Tkinter).
To set some text to use as the default in the Option Menu, add this line:
options_var.set("Select Job Title")
An Option Menu is typically set up with three things: where you want the Option menu (on a tab or form, for example), a string variable, and the contents for the menu. Add this line:
dropdown = tk.OptionMenu(tab3, options_var, *contents)
We want our Option Menu on tab3. The string variable associated with it is called options_var. The actual list of items is contained in our contents variable. Note the asterisk before contents. This means it is a sequence of items.
For the search button, add this line (we'll add the command parameter shortly):
buttonSearch = tk.Button(tab3, text="Search")
To place your new widgets in a grid, add these lines:
search_family.grid(row=0, column=0, padx=15, pady=15)
dropdown.grid(row=0, column=1, padx=15, pady=15)
buttonSearch.grid(row=0, column=2, padx=15, pady=15)
Run your program and you'll see a form with a search area like ours.
We now need a function to deal with the actual searching.
SQL Search Query
The technique here is to build a SQL query using the text in the Entry box and the item chosen from the dropdown list. That SQL Query will then be executed on the database table.
Set up the following function in your code:
def search_records():
Now set up a connection, like we did before: (It would have been better to put the connection code in a function of its own. That way, we could just return con, and cut down on code repetition.)
Once we have a connection object, we can build a query string. This is where the real power lies. By using SELECT, WHERE, and AND we can construct some SQL that will search for the records we want. Add this line to your code:
sql_query = "SELECT * FROM tbl_Employees WHERE Job_Title=%s AND Family_Name=%s"
We want to SELECT all the records from the table called tbl_Employees. But only where the Job_Title and Family_Name match our input. The input will replace the two %s placeholders.
Because we've set up string variables, it's easy to get at the values in our Entry box and Option Menu. Just use get:
vals = (options_var.get(), search_text_var.get())
This will get the text from the Entry box and the chosen item from the dropdown. They are both stored in the variable that we've called vals.
Next, like you did before, set up a cursor object and execute:
cursor = con.cursor()
cursor.execute(sql_query, vals)
We can fetch all the records that the table has found. We can get a row count, as well:
my_rows = cursor.fetchall()
total_rows = cursor.rowcount
Now close the cursor object and the connection:
cursor.close()
con.close()
At this stage, you can use an if statement to check the total_rows variable. If it's 1 row then you can display it wherever you want, in Entry boxes and a Label, as we did before. If total_rows is 0 or more than 1 then you can display a message box informing the user.
To test it out, though, add two print statement:
print(my_rows)
print("TOTAL ROWS: ", total_rows)
Locate your buttonSearch and add a command to call your new function:
buttonSearch = tk.Button(tab3, text="Search", command=search_records)
Your function might look like this, though:
Your main code might look like this:
Try it our. Run your program and search for a record in your database. Then watch what gets printed out.
Deleting Records
You have to be careful when deleting records as there is no undo - the records will be gone for good. One technique to use is to do a search, like we've just done. If you only find 1 row, then ask if this is really what the user wanted. If so, reconnect to the database and do a delete, using the SQL from below. If more than 1 row is found, then the user can cancel. After all, you may have more than one programmer on your staff called Khan.
To delete a record, the SQL is in this format:
DELETE FROM table_name WHERE field_name = some_value
The words in capitals are the keywords: DELETE FROM, WHERE. You can also add an AND part:
DELETE FROM table_name WHERE field_name=some_value AND field_name2=some_value
Replace the value above with placeholders, though, to prevent SQL injection attacks:
DELETE FROM table_name WHERE field_name=%s AND field_name2=%s
AN ID field can come in handy here: (We had one for our database but haven't used it yet.)
DELETE FROM table_name WHERE ID=%s AND Family_Name=%s
Updating records
You may want to update a record in your database table. For example, change a first name where you have got the spelling wrong. In which case, use UPDATE in your SQL:
UPDATE table_name SET First_Name = %s WHERE ID=%s
Again, you can add AND clauses in your SQL. But notice the keywords are now UPDATE, SET and WHERE. The SET keyword is used for the table field you want to change.
We'll leave databases there, though, as you may have your fill of them by now!