統(tǒng)計所有郵件的發(fā)件人所屬機構的出現次數，按照郵件域名計算：
- 提取數據部分是常規(guī)的文件處理方法
file.split() - 不同點在于把數據插入數據庫宦言，需要調用
cursor()相關指令扇单，注意插入的數據用占位符?替代。 -
commit()放在循壞外奠旺，提高速度蜘澜。
import sqlite3
conn = sqlite3.connect('testdb.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Counts')
cur.execute('''
CREATE TABLE Counts (org TEXT, count INTEGER)''')
fname = input('Enter file name: ')
if (len(fname) < 1): fname = 'mbox-short.txt'
# fname = 'mbox.txt'
fh = open(fname)
for line in fh:
if not line.startswith('From: '): continue
pieces = line.split()
email_full = pieces[1]
org = email_full.split('@')[1] #organization name
cur.execute('SELECT count FROM Counts WHERE org = ? ', (org,))
row = cur.fetchone()
# counting times of the org ocurrence
if row is None:
cur.execute('''INSERT INTO Counts (org, count)
VALUES (?, 1)''', (org,))
else:
cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',
(org,))
conn.commit()
# have a check about the results
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'
for row in cur.execute(sqlstr):
print(str(row[0]), row[1])
cur.close()
2018.4.25
