#This is my solution for University of Michigan's challenge on Python CRUD (Create, Read, Update, #Delete) SQL Database
import sqlite3 conn = sqlite3.connect('emaildb.sqlite') cur = conn.cursor() cur.execute('''DROP TABLE IF EXISTS Counts''') cur.execute('''CREATE TABLE Counts (org TEXT, count INTEGER)''') fname = raw_input('Enter file name: ') if ( len(fname) < 1 ) : fname = 'mbox-short.txt' fh = open(fname) for line in fh: if not line.startswith('From: ') : continue pieces = line.split() email = pieces[1] oemail = email.split('@') cemail = oemail[1] print(cemail) cur.execute('SELECT count FROM Counts WHERE org = ? ', (cemail, )) row = cur.fetchone() if row is None: cur.execute('''INSERT INTO Counts (org, count) VALUES ( ?, 1 )''', ( cemail, ) ) else : cur.execute('UPDATE Counts SET count=count+1 WHERE org = ?', (cemail, )) # This statement commits outstanding changes to disk each # time through the loop - the program can be made faster # by moving the commit so it runs only after the loop completes conn.commit() # https://www.sqlite.org/lang_select.html sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10' print "Counts:" for row in cur.execute(sqlstr) : print str(row[0]), row[1] cur.close()
1 Comment
rony
1/2/2016 09:12:16 am
life saving thanks
Reply
Leave a Reply. |
AuthorVitali Kremez Archives
January 2016
Categories |