python course 4 week 2

# Using sqlite3
import sqlite3

# Create a connection
connection = sqlite3.connect('domainDB.sqlite')

cursor = connection.cursor()

cursor.execute('drop table if exists Counts')

# Create the table with columns
cursor.execute('create table Counts (domain text, count integer)')

# Use a filehandler to read the file
fileHandler = open('mbox.txt')

counts = dict()
domainList = list()

for line in fileHandler:
    if not line.startswith('From: '):
        continue
    pieces = line.split() # get the line split by space
    email = pieces[1] # get the whole email
    pieces2 = email.split('@') # split the email address using '@'
    domain = pieces2[1] # get the domain name
    domainList.append(domain) # put the domain name into the list

for domain in domainList:
    counts[domain] = counts.get(domain, 0) + 1 # count domain names
print(counts)

for domain, count in counts.items():
    # Insert results to the database
    # ? is the placeholder for parameters, it is a tuple.
    cursor.execute('insert into Counts (domain, count) values (?, ?)', (domain, count))

# Commit
connection.commit()

# Print the results from the database
sqlstr = 'select domain, count from Counts order by count desc'
for row in cursor.execute(sqlstr):
    print(str(row[0]), row[1])

# Close the connection
connection.close()