UC2s9VRU

1import sqlite3
2import re
3conn = sqlite3.connect('orgs1.sqlite')  # if the database not there, then it will create one
4
5cur = conn.cursor()
6
7cur.execute('''
8 DROP TABLE IF EXISTS Counts ''')  # if exists is important here
9
10cur.execute('''
11CREATE TABLE Counts (orgs TEXT, count INTEGER)''')
12
13fname = raw_input('Enter File Name')
14if (len(fname) < 1): fname = 'mbox.txt'
15fh = open(fname)
16
17for line in fh:
18    if not line.startswith('From: '): continue
19    places = line.split()
20    email = places[1]
21    org = re.search('@[w.]+', email)
22    org = org.group()
23    #print(org)
24    cur.execute('SELECT count FROM Counts WHERE orgs = ?',
25                (org, ))  # Tuple is important, or else it wil think it as a expression
26    # string contactionation wil lead to sql injections, so we do parameter execution
27    try:
28        count = cur.fetchone()[0]  # fetch one will get the list, form that list we take only the first one
29        cur.execute('UPDATE Counts SET count=count+1 WHERE orgs=?', (org,))
30    except:
31        cur.execute(''' INSERT INTO Counts(orgs,count)
32                VALUES (? ,1)''', (org, ))
33        conn.commit()
34
35sqlstr = 'SELECT orgs, count FROM Counts order by count desc limit 10'
36
37for row in cur.execute(sqlstr):
38    print str(row[0]), str(row[1])
39
40cur.close()