7bufnTJ2

1import sqlite3
2
3conn = sqlite3.connect('emaildb1.sqlite')
4cur = conn.cursor()
5
6cur.execute('DROP TABLE IF EXISTS Counts')
7
8cur.execute('''
9CREATE TABLE Counts (org TEXT, count INTEGER)''')
10
11fname = 'G://emaildb/mbox.txt'
12if (len(fname) < 1): fname = 'mbox-short.txt'
13fh = open(fname)
14for line in fh:
15    if not line.startswith('From: '): continue
16    pieces = line.split()
17    email = pieces[1]
18    parts = email.split('@')
19    org = parts[1]
20    cur.execute('SELECT count FROM Counts WHERE org = ? ', (org,))
21    row = cur.fetchone()
22    if row is None:
23        cur.execute('''INSERT INTO Counts (org, count)
24                VALUES (?, 1)''', (org,))
25    else:
26        cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',
27                    (org,))
28    conn.commit()
29
30# https://www.sqlite.org/lang_select.html
31sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'
32
33for row in cur.execute(sqlstr):
34    print(str(row[0]), row[1])
35
36cur.close()