e7AQ52eh

1import sqlite3
2import re
3
4db_path = '../databases/'
5db = db_path + 'z_graded_emaildb_1.sqlite'
6file_path = '../../text_files/'
7file = file_path + 'mbox.txt'
8
9count = {}
10columns = {'org': 'Text,', 'count': 'INTEGER'}
11
12con = sqlite3.connect(db)
13cur = con.cursor()
14
15
16def create_table(table: str, col: dict) -> None:
17    query = 'DROP TABLE IF EXISTS ' + table + ';'
18    query += 'CREATE TABLE ' + table + '('
19
20    for key, value in col.items():
21        query += key + ' ' + value
22    query += ')'
23
24    try:
25        cur.executescript(query)
26    except:
27        print('Could not create table')
28        quit()
29
30    con.commit()
31
32
33def add_data(org: str) -> None:
34    try:
35        cur.execute('INSERT INTO Counts (org, count) VALUES(?, ?)', (org, 1))
36    except:
37        print('Couldn\'t insert ', org, 'into table.')
38
39
40def update_data() -> None:
41    for key, value in count.items():
42        try:
43            cur.execute('UPDATE Counts SET count = ? WHERE org = ?', (value, key))
44        except:
45            print('Couldn\'t update value', value, 'for', key)
46
47
48def email() -> None:
49    try:
50        fh = open(file)
51    except:
52        print('Couldn\'t open file:', file)
53        quit()
54
55    for line in fh:
56        if line.startswith('From '):
57            address = re.findall('\S+@+\S+', line)
58        else:
59            continue
60        org = (address[0].split('@')[1])
61
62        cur.execute('SELECT count FROM Counts WHERE org=\'' + org + '\' LIMIT 1')
63        row = cur.fetchall()
64        if len(row) == 0:
65            add_data(org)
66            count[org] = 1
67        else:
68            count[org] += 1
69
70    update_data()
71    con.commit()
72
73
74create_table('Counts', columns)
75email()
76cur.close()