This post will examine how to parse email data with Python.
You will go step by step through the program emaildb.py.
This program will read through mbox-short.txt file, count the occurrences of each email, and put that data in a database.
In the first line of code you will import a library you need to talk to the database.
The second line of code establishes a connection to the database file emaildb.sqlite.
The third line of code creates a cursor object that allows you to make commands to the database.
Next, you will call the execute method on the cursor. This program will create a new table called “Counts” every time it runs. So, first it will drop the table if it exists, and then it will create a new table.
Notice how you are using SQL commands in Python to talk to the database.
The next several lines of code are similar to what you learned in the Python dictionary post. Here, you are parsing through the file and pulling out the email address.
The next line of code is different. This line uses a technique called parameter substitution.
In this line, you are selecting a row from the table that match the email. The question mark after email serves as a placeholder for a value that will be substituted in. You are substituting in the current value of the email variable.
Next, you will use a “try” statement. You want to use “try” because your program will blow up if no rows were found that matched the email from the previous line. If the email was found, then you advance the count of that email.
In the “except” statement, you are saving the program from blowing up from the email not being found in the table. Rather, you insert this new email into the table, and start the count at 1.
Finally, the conn.commit() statement is very important, because it writes your new changes to the database.
The next thing you will do is run select statement to list the top 10 emails in descending order.
Before closing out the program, you will loop through the rows and print the columns for each row. It is a good idea to convert your column fields to str type before printing.