## Worked Example: Counting Email in a Database
# import library
import sqlite3

# connection to the database which checks access to the file
connection = sqlite3.connect('DatabaseMail.sqlite')
# cursor like handle: open it and then you send SQL commands the cursor
# and then you get your responses through that same cursor.
cursor = connection.cursor()

# drop table if it already exists, so start fresh
cursor.execute('DROP TABLE IF EXISTS Counts')

# create new table called Counts with:
# columns: email (type text), count (type integer)
# (sometimes application can have read me that says, go run these commands to set the database up)
cursor.execute('''
CREATE TABLE Counts (email TEXT, count INTEGER)''')

# open and read file mbox-short.txt or mbox-short.txt
file_name = input('Enter file name: ')
if (len(file_name) < 1): file_name = 'mbox-short.txt'
file_handle = open(file_name)

for line in file_handle:

    # skip if line does not start with "From: "
    if not line.startswith('From: '): continue

    # if line starts with "From: "
    # split line by whitespace
    pieces = line.split()
    # get email address (second part of line)
    email = pieces[1]

    # select all rows from table Counts where email=?
    # dangerous to put user-entered data, into SQL (bc of SQL injection)
    # single ? is placeholder, will be replaced by singleton tuple containing email
    # like the opening of a file but what we're opening is a record set
    cursor.execute('SELECT count FROM Counts WHERE email = ? ', (email,))

    # Grab the first one and give it back in row
    # row is the information that we get from the database
    row = cursor.fetchone()
    # if row with that email does not exist, email is seen for first time
    if row is None:
        # insert new row with count 1
        cursor.execute('''INSERT INTO Counts (email, count)
                VALUES (?, 1)''', (email,))
    else:
        # if row with that email already exists, email has been seen before
        # update to increment count of that row (single atomic operation)
        cursor.execute('UPDATE Counts SET count = count + 1 WHERE email = ?', (email,))

    # commit every time through the loop
    connection.commit()

# https://www.sqlite.org/lang_select.html
# select email and count columns from table Counts
# top 10 in descending order
sqlstr = 'SELECT email, count FROM Counts ORDER BY count DESC LIMIT 10'

# ask for rows one at a time, print out
# row[0] is email, row[1] is count
for row in cursor.execute(sqlstr):
    print(str(row[0]), row[1])

# close connection to the database
cursor.close()

# open DatabaseMail.sqlite in DB Browser for SQLite