@@ -0,0 +1,2706 @@
+%60%60%60%0AYou do not need to export or convert the database - simply upload the .sqlite file that your program creates. See the example code for the use of the connect() statement.%0A%0ACounting Organizations%0AThis application will read the mailbox data (mbox.txt) count up the number email messages per organization (i.e. domain name of the email address) using a database with the following schema to maintain the counts.%0A%0ACREATE TABLE Counts (org TEXT, count INTEGER)%0AWhen you have run the program on mbox.txt upload the resulting database file above for grading.%0AIf you run the program multiple times in testing or with dfferent files, make sure to empty out the data before each run.%0A%0AYou can use this code as a starting point for your application: http://www.pythonlearn.com/code/emaildb.py.%0A%0AThe data file for this application is the same as in previous assignments: http://www.pythonlearn.com/code/mbox.txt.%0A%0ABecause the sample code is using an UPDATE statement and committing the results to the database as each record is read in the loop, it might take as long as a few minutes to process all the data. The commit insists on completely writing all the data to disk every time it is called.%0A%0AThe program can be speeded up greatly by moving the commit operation outside of the loop. In any database program, there is a balance between the number of operations you execute between commits and the importance of not losing the results of operations that have not yet been committed.%0A%60%60%60%0Aimport sqlite3%0A%0Aconn = sqlite3.connect('emaildb.sqlite')%0Acur = conn.cursor()%0A%0Acur.execute('''%0ADROP TABLE IF EXISTS Counts''')%0A%0Acur.execute('''%0ACREATE TABLE Counts (org TEXT, count INTEGER)''')%0A%0Afname = raw_input('Enter file name: ')%0Aif ( len(fname) %3C 1 ) : fname = 'mbox-short.txt'%0Afh = open(fname)%0Afor line in fh:%0A    if not line.startswith('From: ') : continue%0A    pieces = line.split()%0A    email = pieces%5B1%5D%0A    print email%0A    email_split = email.split(%22@%22)%0A    org = email_split%5B1%5D%0A    cur.execute('SELECT count FROM Counts WHERE org = ? ', (org, ))%0A    row = cur.fetchone()%0A    if row is None:%0A        cur.execute('''INSERT INTO Counts (org, count)%0A                VALUES ( ?, 1 )''', ( org, ) )%0A    else :%0A        cur.execute('UPDATE Counts SET count=count+1 WHERE org = ?',%0A            (org, ))%0A    # This statement commits outstanding changes to disk each%0A    # time through the loop - the program can be made faster%0A    # by moving the commit so it runs only after the loop completes%0A    conn.commit()%0A%0A# https://www.sqlite.org/lang_select.html%0Asqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'%0A%0Aprint%0Aprint %22Counts:%22%0Afor row in cur.execute(sqlstr) :%0A    print str(row%5B0%5D), row%5B1%5D%0A%0Acur.close()%0A

Create emaildb.py