python course 4 week 2

How to CRUD with Python3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

# 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()