在做一个python数据统计的程序,发现SQL语句有问题,最后运行结果与需求相距甚远。代码如下,个人认为是第一个备注处有问题,请大家看看,该怎么改?
import sqlite3
class hx():
def sy_1(self,pm,dh):
db=sqlite3.connect("C:\\Users\\sai\\Desktop\\kw\\kw.db")
c=db.cursor()
sy=c.execute("select hszj+yf_dd-yf_sf-(select cb from sp where pm=?) from ss where dh=?",(pm,dh))
for sy_1 in sy:
print (sy_1)
db.commit()
db.close()
def sy_2(self,qs,zz):
db=sqlite3.connect("C:\\Users\\sai\\Desktop\\kw\\kw.db")
c=db.cursor()
sy=c.execute("select sum(sy) from ss where sj>=? and sj<=?",(qs,zz))
for sy1 in sy:
print(sy1)
db.commit()
db.close()
def pm(self):
qs2=input("起始时间:")
zz2=input("终止时间:")
qs21="\"%s\""%qs2
zz21="\"%s\""%zz2
rh=[]
zs=[]
zz=[]
zg=[]
s1={'md':None,'sl':None}
db=sqlite3.connect("C:\\Users\\sai\\Desktop\\kw\\kw.db")
c=db.cursor()
pm=c.execute("select pm from ss where sj>=? and sj<=?",(qs21,zz21)) #搜索结果只有一个,应该是输入的日期格式不对。
db.commit()
for pm1 in pm:
md=c.execute("select md from sp where pm=?",(pm1))
for md1 in md:
s1['md']=md1[0] #查询输出为元组,这才是正确的调用方式。
db.commit()
sl=c.execute("select sum(sl) from ss where pm=?",(pm1))
for sl1 in sl:
s1['sl']=sl1[0]
db.commit()
if s1['md']=="自购":
zg.append(s1['sl'])
elif s1['md']=="书摊入货":
rh.append(s1['sl'])
elif s1['md']=="别人赠送处理":
zs.append(s1['sl'])
elif s1['md']=="自制":
zz.append(s1['sl'])
zg_z=sum(zg)
rh_z=sum(rh)
zs_z=sum(zs)
zz_z=sum(zz)
print ("自购:%i"%zg_z)
print ("书摊入货:%i"%rh_z)
print ("别人赠送处理:%i"%zs_z)
print ("自制:%i"%zz_z)
db.close()
a=hx()
xz=input("1,单笔收益计算\n2,时间段收益\n3,自购和入货的商品销售情况\n")
if xz=="1":
pm1=input("商品名称:")
dh1=input("单号")
a.sy_1(pm1,dh1)
elif xz=="2":
qs1=input("起始时间:")
zz1=input("终止时间:")
a.sy_2(qs1,zz1)
elif xz=="3":
a.pm()