Select data from MySQL database as dictionary and iterate through it

In this tutorial we are going to look at how you can retrieve data from a MySQL database as a dictionary instead of a tuple using PyMySQL. Then using that data we will iterate through the dictionary and use logic to test for certain conditions, in this case, we will be testing if the first letter of a user’s first name is equal to a letter of the alphabet. Before we begin, I’m using Python 3.5.2 and PyMySQL 0.7.9. You will need the PyMySQL module installed in your environment and have basic SQL knowledge, if you don’t have it you can run this command on your terminal:

pip install pymysql

Once you have that done then create a new python file and name it whatever you want, I’m using databases2.py, so let’s begin. Our first set of code we need to establish a database connection, and retrieve the data for processing.  Examine the code below:

What’s going on?

1.) On line 2 we are importing the pymysql module we just installed.

2.) On line 4 we are connecting to the MySQL database to storing it into the db variable using the pymysql.connect() method.  Its arguments in order from left-to-right is your host, user, password, database.

3.) On line 5 we are calling the db.cursor() method and storing it into the cursor variable. By default the cursor() method returns a tuple so we are using mysql.cursor.DictCursor as the argument so our data will be  dictionary.

4.) On line 6 we are grabbing all the user data from the auth_user table by calling the cursor.execute() method with our SQL query string as the argument.

5.) And finally on line seven we are calling the cursor.fetchall() method so we can grab all the users in the database and storing it in the users variable. Now let’s examine the rest of the code:

What’s going on?

6.) On line 9 we set our letter variable to a letter of our choosing.

7.) On line 10 we set the count variable to zero so we can get ready to start counting.

8.) On lines 11-16; we use a for loop to iterate through the users’ dictionary, then we assign the  first name stored in user[‘first_name’] to the first_name variable for readability, then we use a conditional if statement and the .slice() method to see if the first letter of the first_name variable(first_name[0]) is equal to the letter variable(note: I’m using .upper() method to turn any lower case letters to uppercase), if that condition is met then we increment the count variable by one and print out the first name.

9.) On line 15, once the loop is done we are print out the total number of users whose first name starts with the letter we chose. 10.) And lastly we close the database connection with cursor.close().

All together now

And lastly we just run the script at the terminal and view the output:

python database2.py 

or 

./database2.py

Output:

Expanding on this concept there’s really no limit to processing this data. As an example and a bonus, modify this code so that you can store this username data to a simple text file instead of just printing it out to a text file. I hope this helps. Feel free to leave a comment below if you have any questions.

Further reading: Python 3 MySQL Database Access, String Slicing