In order to pull records from a database table, you need something called a SQL string. SQL stands for Search Query Language and is pronounced SeeKwel. It's a natural language and uses words like SELECT, FROM and WHERE. It's usual to type the keywords in uppercase, but it's not required.
To pull all the records from a table the asterisk (*) symbol is used. The table in our database is called tbl_employees. The SQL String to pull all the records from this table is:
SELECT * FROM tbl_employees
You can, however, specify just the columns you need. For example, suppose we just wanted the first name and family name. Our SQL string would be this:
SELECT First_Name, Family_Name FROM tbl_employees
Try out this SQL String in phpMyAdmin. Start up phpMyAdmin like you did before. Enter your username and password. Now click on your Employees database on the left-hand side. Click on the SQL tab at the top. Copy and paste the above SQL in:
Click the GO button in the bottom right. You should see some results appear.
Click back on the SQL tab.
If you want to further restrict the rows that you get back, you can add a WHERE part to your SQL. For example, suppose we wanted only the programmers returned. Our SQL would be this:
SELECT * FROM tbl_employees WHERE Job_Title = 'Programmer'
We're selecting all the records from tbl_employees WHERE the Job_Title column equals Programmer. Note where all the quote marks are above. Give it a try in phpMyAdmin and see what happens. You should find that just the two records are returned.
In future, if you're trying to construct some SQL string in Python and it's not working, fire up phpMyAdmin and enter your SQL there. You'll be able to check if the problem is with your SQL query.
You can whittle down your results even further with the use of AND in your SQL query. Suppose you want to search for programmers called David. You'd do it like this:
SELECT * FROM tbl_employees WHERE Job_Title = 'Programmer' AND First_Name = 'David'
Now, we have a WHERE part and an AND part. The thing we're searching for goes between single quote marks.
For our purposes, though, we'll select all the records from the table. Add this line to your load_database_results function:
sql = "SELECT * FROM tbl_employees"
Add the line just after your connection line and before con.close().
The next thing we need is something called a cursor. This is an object that contacts the database table on your behalf and executes SQL queries. It can perform table operations for you. Like fetching rows, getting a row count, and a whole lot more besides.
Add this line just below your SQL one:
cursor = con.cursor()
This sets up a cursor object that we've called cursor. (Again, this is just a variable name. Feel free to call it something else, if you like.)
Now that we have a cursor object, we can execute some SQL. Here's the line to add:
We use a method of the cursor object called execute. Between its round brackets, you need the SQL query that you want to execute. For us, this is held in the variable called sql.
We'd like al the results back from the query. So add this line next:
rows = cursor.fetchall()
This does exactly what you think it does - fetches all the results from the query. We're placing them in our rows variable. But we want to make it global, which is why we set up the variable near the top of the code, with a value of None. To make a variable global, add this line to the top of your load_database_results function. Add it as the first line, before the try statement:
We also need to make our num_of_rows variable global. So add this just below the rows global variable:
We'll need this variable when we come to scroll forward through the table rows.
Back to the try statement. Just below your fetchall command, add this:
num_of_rows = cursor.rowcount
How many rows the table holds will be placed in our num_of_rows variable.
We can now close the cursor object. Add this just before your con.close line:
One more thing we need is a Boolean variable to state that the database has loaded successfully. Inside of your try except block, set up a variable called has_loaded_successfully. Set it to True:
has_loaded_successfully = True
(It might flag up an error, this new variable. It will go away when we've added the rest of the code.)
Now, instead of saying return at the end of the function, return the has_loaded_successfully variable:
For the except parts of the try except block, we can create another function to help us cut down on code repetition. We want to return a value of False for the has_loaded_successfully variable. We also want to display an error message. Instead of saying this for every except part:
has_loaded_successfully = False
messagebox.showinfo("Internal Error", e)
We can just have one line:
has_loaded_successfully = database_error()
Add this function to your code:
For every except part of your code in the load_database_results function, replace it with the line above:
has_loaded_successfully = database_error()
Your two functions should look like this:
So the new function just return as value of False, after displaying an error message. This False value is then returned to the calling line, highlighted above.
Notice that we've deleted the line that displays a message box when the connection to the database was OK. You can delete this line from your own code, too.
We now need to call the load_database_results function from our main code. If it's True that the database connection was successful then we can go ahead and display the first record from the database table. If it's False, then we don't need to do anything as an error message will already have been shown.
Jump down to the end of your code and locate the line that calls the load_database_results function. Change it to this:
success = load_database_results()
The function is now returning a value, either True or False. If success is True then we can load the first record from the database into our text boxes and a picture into our label.
In the next lesson, we'll finally load those database results into our form.