Insert new Record

In the previous lesson, you set up a line that called a function with the name of insert_into_database. We'll now write that function. This will remove the red underline in PyCharm.

Set up the new function near the top of you code:

def insert_into_database(first _field, family_field, job_field, photo_field):

This function has three things passed over to it: the first name data, the family name data, and the name of a photo. We'll use these varaibles later.

To do the actual inserting, we need to open up a connection to the database again. We'll also need a SQL string, like last time, and a cursor object. We'll wrap the whole thing up in a try … except block, which will check for errors.

The connection to the database is the same as before:

con = pymysql.connect(host=db_config.DB_SERVER,

user=db_config.DB_USER,

password=db_config.DB_PASS,

database=db_config.DB)

Add the connection code above and your function will look like this:

Adding database connection code to our Python function

INSERT INTO

We now need some SQL. When we pulled the records from the database table, we used SELECT * FROM tbl_employees. If you want to insert a new record, the format is this:

INSERT INTO tbl_name (col_names_here) VALUES (values_here)

The keywords are INSERT INTO and VALUES. After INSERT INTO, you need your table name. In between round brackets, you need the name of the columns from your table. Our columns are called First_Name, Family_Name, Job_Title, and Photo. For the VALUES, you need one value for every column that you've previously typed. We can use placeholders again. This guards against SQL Injection attacks. Add the following line:

sql = "INSERT INTO tbl_employees (First_Name, Family_Name, Job_Title, Photo) VALUES (%s, %s, %s, %s)"

The %s means you want to insert a value later. (%s is used for all placeholder, even if your field is a number.)

Our values are the ones we passed over to the function, between the round brackets. Add this line, then:

vals = (first_field, family_field, job_field, photo_field)

Make sure your values are between round brackets. You could hard-code them, like this:

vals = ("John", "Doe", "Support", "10.png")

But ours are in variables so we don't need any quote marks.

Next, set up a cursor object. You did this before:

cursor = con.cursor()

We can then execute our SQL and values:

cursor.execute(sql, vals)

The SQL comes first. After a comma, you need your values.

You need to use the commit command to commit the SQL and values to the table:

con.commit()

Now close the cursor and the connection:

cursor.close()
con.close()

You won't see anything, if the commit is successful, so add a message box:

messagebox.showinfo("Database", "Record Added to Database")

Once you've added the code, it should look like this:

SQL code to insert a record into a database using Python

To wrap it all up in a try … catch block, highlight the code:

Some highlighted Python code

From the menu at the top of PyCharm, select Code > Surround With (or press CTRL + ALT + T). From the menu that appear, select try / except.

For the except part, you can copy and paste the exception block you set up in your load_database_results function. But delete the has_loaded_successfully = part and just keep the database_error(e) part. Ours were these (you may not have as many):

except pymysql.ProgrammingError as e:

database_error(e)

except pymysql.DataError as e:

database_error(e)

except pymysql.IntegrityError as e:

database_error(e)

except pymysql.NotSupportedError as e:

database_error(e)

except pymysql.OperationalError as e:

database_error(e)

except pymysql.InternalError as e:

database_error(e)

Delete the pass line and your code will look something like this:

Database error checking code in Python and pyMySql

You're almost ready to try it out. First, locate your commit button:

buttonCommit = tk.Button(tab2, text="Add Record to Database")

Now add a command parameter and call your add_new_record function:

buttonCommit = tk.Button(tab2, text="Add Record to Database", command=add_new_record)

We need to take care of one more thing - reloading the database when the All Records tab is clicked.

Locate your on_tab_selected function. It looks like this, at the moment:

Tab selected code in Python

We don't need the print statements. Instead, we'll make use of those flags we set up: blank_textboxes_tab_two and image_selected. These are both Boolean values. We can reload the database if blank_textboxes_tab_two is False and is image_selected True.

For the All Records if statement, then, add this in place of the print line:

if (blank_textboxes_tab_two is False) and (image_selected is True):

load_database_results()

For the Add New Record if statement, we need to set the two booleans backs to True and False. Add these lines in place of the print line:

blank_textboxes_tab_two = True
image_selected = False

You'll probably get a red underline somewhere. To get rid of it, we need to access the global variables. As the first two lines of the on_tab_selected function, add these lines:

global blank_textboxes_tab_two
global image_selected

Your function should look like this:

Boolean error checking in Python

The function makes use of two Boolean flags. If blank_textboxes_tab_two is False and is image_selected True, then we reload the database.

One last thing to do before trying it out. Your photos directory should contain 10 photos plus a default one. Move half of these out to a different folder (leave the default). When adding a new photo, select one from this new folder that you created. It will be copied over to the photos directory.

Add this new record, when testing your program out (and make sure you've started your server):

First Name: Peter
Family Name: Lee
Job Title: Graphic Artist

Select an appropriate photo to go with your new record.

Click back on the All Records tab when you've added a new record. You should see something like this, once you scroll to the end:

A new record added to the database using a Tkinter form

In the next lesson, you'll learn how to search for a record in your database.

Search for a Record >