Grade Assignment Coursera / University of Michigan / py4e / Using Databases with Python / week2

z_graded_emaildb_1.py

import sqlite3
import re

db_path = '../databases/'
db = db_path + 'z_graded_emaildb_1.sqlite'
file_path = '../../text_files/'
file = file_path + 'mbox.txt'

count = {}
columns = {'org': 'Text,', 'count': 'INTEGER'}

con = sqlite3.connect(db)
cur = con.cursor()


def create_table(table: str, col: dict) -> None:
    query = 'DROP TABLE IF EXISTS ' + table + ';'
    query += 'CREATE TABLE ' + table + '('

    for key, value in col.items():
        query += key + ' ' + value
    query += ')'

    try:
        cur.executescript(query)
    except:
        print('Could not create table')
        quit()

    con.commit()


def add_data(org: str) -> None:
    try:
        cur.execute('INSERT INTO Counts (org, count) VALUES(?, ?)', (org, 1))
    except:
        print('Couldn\'t insert ', org, 'into table.')


def update_data() -> None:
    for key, value in count.items():
        try:
            cur.execute('UPDATE Counts SET count = ? WHERE org = ?', (value, key))
        except:
            print('Couldn\'t update value', value, 'for', key)


def email() -> None:
    try:
        fh = open(file)
    except:
        print('Couldn\'t open file:', file)
        quit()

    for line in fh:
        if line.startswith('From '):
            address = re.findall('\S+@+\S+', line)
        else:
            continue
        org = (address[0].split('@')[1])

        cur.execute('SELECT count FROM Counts WHERE org=\'' + org + '\' LIMIT 1')
        row = cur.fetchall()
        if len(row) == 0:
            add_data(org)
            count[org] = 1
        else:
            count[org] += 1

    update_data()
    con.commit()


create_table('Counts', columns)
email()
cur.close()