Python: Database Connection

 

Ongoing Tutorial. Previous lesson: Images in Labels

Now that we have a user form, we can connect to our database. The technique used is this:

• Set up a connection object
• Write a SQL query
• Set up a Cursor object
• Fetch rows back from the database table
• Display the results

We'll need some global variables for this project. One of them will set up a row object that we can access from everywhere in the code, and the other will hold the number of rows in the database. That way, we can prevent the program from going past the total number of rows in the table and crashing out on us.

Locate these two lines in your code:

file_name = "default.png"
path = db_config.PHOTO_DIRECTORY + file_name

On the line after these two lines, set up these two variables:

rows = None
num_of_rows = None

The keyword None does what you'd expect - stores no value in the variable.

Now set up a function at the top of your code. This one:

def load_database_results():

We want to return a Boolean value from this function that will tell us whether or not the database has loaded successfully. So just add the keyword return for now:

def load_database_results():

return

The top of your code should look like this:

When connecting to a MySql database, you need to specify the server name (host) a username and password, and the database you want to connect to. We can add these settings in our db_config file.

Open up your db_config file. It should have your PHOTO_DIRECTORY variable there. Just below that, set up the following four variables:

DB_SERVER = "localhost"
DB_USER = "root"
DB_PASS = "password"
DB = "Employees"

The words in capital letters are just variable names we made up. You can call them something else, if you want. The values going into them, however, may need to be changed. The DB_SERVER for us is localhost. This is the default when setting up MySql. The numbers (in quote marks) "127.0.0.1" will also work in place of localhost. If you've set up on a different server, you'd need to type the details here.

The default username and password for MySql is usually root for both. But on some systems, you may need to change the password to just a blank string (type two single quotes with no spaces between them). But the username and password you entered when installing MySql will need to go here. We used the very guessable root and password for our details. Needless to say, don't use these on a production system, one that's going to be accessible to the outside world! Replace root and password with your own details.

The DB variable holds the name of our database, which is Employees. Your db_config file should look something like this (but with your own username and password in place of ours):

Now go back to your load_database_results function in the db_main file. We'll add a try … except block. If we can't log on, we'll display a message box saying why. Add the try … except part first:

def load_database_results():
try:
except pymysql.InternalError as e:
messagebox.showinfo("Connection Error", e)
return

def load_database_results():

try:

except pymysql.InternalError as e:

messagebox.showinfo("Connection Error", e)

return

The exception is pymysql.InternalError as e. The InternalError is an inbuilt error that the pymysql library already has. Whatever error this is gets stored in the variable called e. This is then displayed in a message box.

As the try part, we want to try and connect to the server and database. Add the following in the try part:

con = pymysql.connect(host=db_config.DB_SERVER,
user=db_config.DB_USER,
password=db_config.DB_PASS,
database=db_config.DB)

So we're using the pymysql library, which has a method called connect. In between the round brackets of connect, we have four parameters: host, user, password, and database. The values for these parameters are coming from our db_config file. When our load_database_results function is executed, pymysql will try to connect to the database specified using the other settings we provided. If it can't connect, it will throw up an error.

Below this line, close the connection with:

con.close()

Now add a message box:

messagebox.showinfo("Connected to Database", "Connected OK")

Your code should look like this:

Let's try it out.

Scroll down to the bottom of your code and locate these two lines:

tab_parent.pack(expand=1, fill='both')
form.mainloop()

Add the following line just before them:

load_database_results()

In other words, call the load_database_results function near the end.

Run your program and see what happens.

If all went well then you should see a dialog box like this one:

If you program crashed, you might have seen an error like this in your output window:

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost'. No connection could be made because the target machine actively refused it)"

This is an OperationalError that you can catch by adding an exception part to your code:

try:

except pymysql.InternalError as e:

messagebox.showinfo("Connection Error", e)

except pymysql.OperationalError as e:

messagebox.showinfo("Connection Error", e)

You can copy and paste your first except block. Then just change InternalError to OperationalError.

Run your program again. If you are getting this type of error, a message box should now display, instead of your program crashing:

The reason for this error is because you probably don't have your server started. If you're on Windows and using WAMP Server, start it up from the programs menu. Wait for the WAMP icon to turn to green. If you're a Linux or Mac system, start your server up in a similar way. (Mac users may have a MAMP server icon in the Applications folder. Click this.)

Try again, and hopefully you'll see the Connection OK message box.

If it still fails, then check your username and password.

 

Besides the errors InternalError and OperationalError, pymysql has lots of others. Four more error codes you can use are:

DataError
IntegrityError
NotSupportedError
ProgrammingError

Here's some code that uses the errors above:

You can change your code to match, if you want, as it's good practice to cover as many errors as you can.

Now that we have a connection, we can pull the records from the database table. We'll do that in the next lesson.

Get Records from a Database Table >

< Python Course Menu