def delCart(username, goodsId, goodsNum) -> bool:
conn = store.get_db_conn()
try:
cursor = conn.execute(
"SELECT goodsNum, goodsPrice from cart where username=? and goodsId=?",
(username, goodsId),
)
row = cursor.fetchone()
if row is None:
return False
newgoodsNum = row[0] - goodsNum
newtotalValue = row[1] * newgoodsNum
if newgoodsNum == 0:
cursor = conn.execute(
"DELETE from cart where username=? and goodsId=?",
(username, goodsId))
else:
conn.execute(
"UPDATE cart set goodsNum = ? where username=? and goodsId=?",
(newgoodsNum, username, goodsId),
)
conn.execute(
"UPDATE cart set totalValue = ? where username=? and goodsId=?",
(newtotalValue, username, goodsId),
)
conn.commit()
except BaseException as e:
print(e)
conn.rollback()
return False
return True
def update_token(self):
conn = store.get_db_conn()
try:
conn.execute(
"UPDATE user set token = ?, terminal = ? where username=?",
(self.token, self.terminal, self.username),
)
conn.commit()
except sqlite.Error as e:
logging.error(str(e))
conn.rollback()
def delete_user(self):
conn = store.get_db_conn()
try:
cursor = conn.execute("DELETE from user where username=?", (self.username,))
if cursor.rowcount == 1:
conn.commit()
else:
conn.rollback()
except sqlite.Error as e:
logging.error(str(e))
conn.rollback()
def cancelOrder(orderId):
conn = store.get_db_conn()
try:
cursor = conn.execute("DELETE from order where username=?",
(orderId))
if cursor.rowcount == 1:
conn.commit()
else:
conn.rollback()
except sqlite.Error as e:
logging.error(str(e))
conn.rollback()
def addGoods(goodsId, goodsName, goodsAuth, goodsPrice, goodsNum, goodsType, goodsDsr, sellerName) -> bool:
conn = store.get_db_conn()
try:
conn.execute(
"INSERT into goods(goodsId, goodsName, goodsAuth, goodsPrice, goodsNum, goodsType, goodsDsr, sellerName) VALUES (?, ?, ?, ?, ?, ?, ?, ?);",
(goodsId, goodsName, goodsAuth, goodsPrice, goodsNum, goodsType, goodsDsr, sellerName),
)
conn.commit()
except BaseException as e:
print(e)
conn.rollback()
return False
return True
def insert_user(username, password) -> bool:
conn = store.get_db_conn()
try:
conn.execute(
"INSERT into user(username, password, token, terminal) VALUES (?, ?, '', '');",
(username, password),
)
conn.commit()
except sqlite.Error as e:
print(e)
conn.rollback()
return False
return True
def check_num(goodsId, goodsNum):
try:
conn = store.get_db_conn()
cursor = conn.execute(
"SELECT goodsNum from goods where goodsId=?",
(goodsId),
)
row = cursor.fetchone()
if row is None or row[1] - goodsNum < 0:
return False
except sqlite.Error as e:
logging.error(str(e))
return False
return True
def createOrder(orderId, sellerName, buyerName, orderStatus, cartlist,
addr):
conn = store.get_db_conn()
try:
for good in cartlist:
conn.execute(
"INSERT into orders(orderId, buyerName, sellerName , orderStatus, goodsName, goodsPrice, totalValue, addr) VALUES (?, ?, ?, ?, ?, ?, ? ,?);",
(orderId, buyerName, sellerName, orderStatus, good[0],
good[1], good[2], addr),
)
conn.commit()
except sqlite.Error as e:
print(e)
conn.rollback()
return False
return True
def searchGoods(keywords, goodsType) -> (bool, list):
conn = store.get_db_conn()
try:
if keywords == "" and goodsType == "":
return False, []
elif keywords != "" and goodsType == "":
cursor = conn.execute(
"SELECT goodsId, goodsName, goodsAuth, goodsPrice, goodsNum, goodsType, goodsDsr,sellerName from goods where goodsname=?",
(keywords,),
)
contents = cursor.fetchall()
if not contents:
cursor = conn.execute(
"SELECT goodsId, goodsName, goodsAuth, goodsPrice, goodsNum, goodsType, goodsDsr,sellerName from goods where goodsDsr LIKE ?",
('%' + keywords + '%',),
)
contents = cursor.fetchall()
if not contents:
return False, []
elif keywords == "" and goodsType != "":
cursor = conn.execute(
"SELECT goodsId, goodsName, goodsAuth, goodsPrice, goodsNum, goodsType, goodsDsr,sellerName from goods where goodsType=?",
(goodsType,),
)
contents = cursor.fetchall()
if not contents:
return False, []
else:
cursor = conn.execute(
"SELECT goodsId, goodsName, goodsAuth, goodsPrice, goodsNum, goodsType, goodsDsr,sellerName from goods where goodsType=? and goodsname=?",
(goodsType, keywords),
)
contents = cursor.fetchall()
if not contents:
return False, []
goodslist = []
for row in contents:
a = [row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7]]
goodslist.append(a)
except sqlite.Error as e:
logging.error(str(e))
return False, []
return True, goodslist
def fetch_user(self, username) -> bool:
try:
conn = store.get_db_conn()
cursor = conn.execute(
"SELECT username, password, token, terminal from user where username=?",
(username,),
)
row = cursor.fetchone()
if row is None:
return False
self.username = username
self.password = row[1]
self.token = row[2]
self.terminal = row[3]
except sqlite.Error as e:
logging.error(str(e))
return False
return True
def addCart(username, goodsId, goodsName, goodsPrice, goodsNum,
totalValue) -> bool:
conn = store.get_db_conn()
try:
#添加一个判断，商品是否还有库存
if not check_num(goodsId, goodsNum):
return False
conn.execute(
"INSERT into cart(username, goodsId, goodsName, goodsPrice, goodsNum, totalValue) VALUES (?, ?, ?, ?, ?, ?);",
(username, goodsId, goodsName, goodsPrice, goodsNum,
totalValue),
)
conn.commit()
except BaseException as e:
print(e)
conn.rollback()
return False
return True
def fetch_order(username) -> (bool, list):
conn = store.get_db_conn()
try:
cursor = conn.execute(
"SELECT orderId, orderStatus, goodsName, goodsPrice, totalValue, addr from order where username=?",
(username),
)
contents = cursor.fetchall()
if contents is None:
return False, []
orderlist = []
for row in contents:
a = [row[0], row[1], row[2], row[3], row[4], row[5]]
orderlist.append(a)
except sqlite.Error as e:
logging.error(str(e))
return False
return True, orderlist
def getCart(username) -> (bool, list, float):
conn = store.get_db_conn()
try:
cursor = conn.execute(
"SELECT goodsName, goodsPrice, goodsNum, totalValue from cart where username=?",
(username),
)
contents = cursor.fetchall()
if contents is None:
return False, []
cartlist = []
sum = 0
for row in contents:
a = [row[0], row[1], row[2]]
sum += row[3]
cartlist.append(a)
except sqlite.Error as e:
logging.error(str(e))
return False
return True, cartlist, sum
def __init__(self):
self.conn = store.get_db_conn()
def __init__(self):
self.conn = store.get_db_conn()
self.cursor = self.conn.cursor()