用Python编辑SQLite的时候，怎样在遍历数据的时候，对数据进行计数，并将计数个数更新在表中？

Question

想用Python控制SQLite，遍历数据的时候，对相同的数据进行计数并放在SQL的表中。但是我用以下代码运行的时候，相同的数据并没有叠加计数，而是每个数据都在表里单独一行，计数列都是1。请指教

import sqlite3
import urllib.request


url='http://www.pythonlearn.com/code/mbox.txt'
data=urllib.request.urlopen(url).read().decode()

#print (type(data))

conn=sqlite3.connect('email.sqlite')
cur=conn.cursor()

cur.execute('''DROP TABLE COUNTS''')
cur.execute('''CREATE TABLE COUNTS (org TEXT, count INTEGER)''')
for line in data.split('\n'):
    #print (line)
    
    
    if  not line.startswith('From'):
        continue
    
    words=line.split()
    #print (words)
    email=words[1]
       # print (email)
    word=email.split('@')
    s=word[0]
    #print (s)
    cur.execute('SELECT count FROM COUNTS WHERE org = ?',(s,))
    try:
        count=cur.fetchone()[0]
        cur.execute('UPDATE count=count+1 WHERE org = ?',(s,))
    except:
        cur.execute('''INSERT INTO COUNTS (org,count) VALUES (?,1)''',(s,))
    conn.commit()
    
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()

Answer 1

# coding=utf-8

import requests
import collections

url = 'http://www.pythonlearn.com/code/mbox.txt'
data = requests.get(url).text

emails = [_.split()[1] for _ in data.split('\n') if _.startswith('From')]
print collections.Counter(emails).most_common(10)

#如果你只想取人名，这里再split一下
emails = [_.split()[1].split('@')[0] for _ in data.split('\n') if _.startswith('From')]
print collections.Counter(emails).most_common(10)