想用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()