SQL中WHERE子句不能包含聚合函数,否则会报错。
聚合函数包括:
count() 所有记录数
count(*)所有非null记录数
avg() 某一列平均值
min() 某一列最小值
max() 某一列最大值
sum() 某一列总和
等,需要通过Group by having等。
import cPickle
import sqlite3
DATABASE = r'/home/build.db'
#cxn=sqlite3.connect('sqlite_test.db')
cxn=sqlite3.connect(DATABASE)//连接指定路径的db,如不存在则创建一个新的,如果存在,则连接
cur=cxn.cursor()//执行的游标
cur.execute("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='usersSort1'")
//查看是否存在名为usersSort1的table,
for user in cur.fetchall():
j=user[0]
得到游标元素,如不存在名为userSort1的table,则为0,存在则创建table
if j==0:
cur.execute('create table if not exists usersSort1(project_key_id int,project_key NTEXT,‘。。。。’')
//省略了一些定义的内容
for a in test:
for x in test[a]:
if x['project_key'] in project_list_value_next:
if l==False:
m=project_list_value_next.index(x['project_key'],0)
i=m+1
l=True
else:
try:
m=project_list_value_next.index(x['project_key'],m+1)
i=m+1
except Exception,e:
print Exception,":",e
project_list_value_next.append(x['project_key'])
else:
project_list_value_next=list()
project_list_value_next.append(x['project_key'])
i=0
l=False
cur.execute('insert into usersSort1(project_key_id,project_key) values(?,?)',
(i,x['project_key']))
cur.execute('select max(project_timestamp) from usersSort1 ')
for user in cur.fetchall():
k=user[0]
cur.close()
cxn.commit()
cxn.close()