def add_funds(self, user_id, password, add_value) -> (int, str): try: usr = self.session.query(Users).filter_by(user_id=user_id).first() #usr = self.session.execute("SELECT password from usr where user_id= '%s'"%(user_id,)).fetchone() #row = cursor.fetchone() if usr is None: return error.error_authorization_fail() if usr.password != password: return error.error_authorization_fail() cursor = self.session.query(Users).filter(Users.user_id == user_id) rowcount = cursor.update( {Users.balance: Users.balance + add_value}) # cursor = self.session.execute( # "UPDATE usr SET balance = balance + %d WHERE user_id = '%s'"% # (add_value, user_id)) #if cursor.rowcount == 0: if rowcount == 0: return error.error_non_exist_user_id(user_id) self.session.commit() self.session.close() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def check_token(self, user_id: str, token: str) -> (int, str): #cursor = self.session.execute("SELECT token from user where user_id=?", (user_id,)) #row=self.session.execute("SELECT token from usr where user_id='%s'" % (user_id)).fetchone() #row = cursor.fetchone() row=self.session.query(Users).filter(Users.user_id==user_id).first() if row is None: return error.error_authorization_fail() # db_token = row[0] db_token = row.token if not self.__check_token(user_id, db_token, token): return error.error_authorization_fail() return 200, "ok"
def check_password(self, user_id: str, password: str) -> (int, str): # row=self.session.execute("SELECT password from usr where user_id=:user_id", # {"user_id": user_id}).fetchone() #row= self.session.execute("SELECT password from user where user_id=?", (user_id,)).fetchone() #row = cursor.fetchone() row=self.session.query(Users).filter(Users.user_id==user_id).first() if row is None: return error.error_authorization_fail() #if password !=row[0] if password != row.password: return error.error_authorization_fail() return 200, "ok"
def change_password(self, user_id: str, old_password: str, new_password: str) -> bool: try: code, message = self.check_password(user_id, old_password) if code != 200: return code, message terminal = "terminal_{}".format(str(time.time())) token = jwt_encode(user_id, terminal) # cursor = self.conn.execute( # "UPDATE user set password = ?, token= ? , terminal = ? where user_id = ?", # (new_password, token, terminal, user_id), ) # cursor=self.session.execute( # "UPDATE usr set password = '******',token='%s',terminal='%s' where user_id = '%s'"%(new_password, token, terminal,user_id), ) cursor= self.session.query(Users).filter(Users.user_id == user_id).first() # if cursor.rowcount == 0: if cursor is None: return error.error_authorization_fail() cursor.password = new_password cursor.token = token cursor.terminal = terminal self.session.commit() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok" # 搜索是针对全部用户都拥有的功能 # 关键字搜索,参数化搜索方式 # 全站店铺搜索与当前店铺搜索 # 全文索引优化查找 # 范围:题目标签内容(book_intro)
def logout(self, user_id: str, token: str) -> bool: try: code, message = self.check_token(user_id, token) if code != 200: return code, message terminal = "terminal_{}".format(str(time.time())) new_token = jwt_encode(user_id, terminal) # cursor = self.conn.execute( # "UPDATE user SET token = ?, terminal = ? WHERE user_id=?", # (dummy_token, terminal, user_id), ) # cursor = self.session.execute( # "UPDATE usr set token= '%s' , terminal = '%s' where user_id = '%s'"% (new_token, terminal, user_id) ) cursor=self.session.query(Users).filter(Users.user_id == user_id).first() # if cursor.rowcount == 0: if cursor is None: return error.error_authorization_fail() cursor.token = new_token cursor.terminal = terminal self.session.commit() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def receive_book(self, user_id: str, order_id: str): try: #判断该用户是否存在 if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) + (order_id, ) print("用户存在") #判断该订单是否存在在未收货里面 row = self.session.query(New_order_unreceived).filter_by( order_id=order_id) order = row.first() print("未收货订单", order) if order is None: return error.error_invalid_order_id(order_id) buyer_id = order.buyer_id #判断该用户是否有这个订单。。。。验证收货的人是否正确 if user_id != buyer_id: return error.error_authorization_fail() #有该订单,收货 #添加收货时间 timenow = datetime.now().strftime("%Y-%m-%d %H:%M:%S") print("******已收货") row.update({New_order_unreceived.receive_time: timenow}) self.session.commit() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def deliver_book(self,user_id:str,order_id:str): try: #判断该用户是否存在 if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) + (order_id, ) print("用户存在") #判断该订单是否存在 row=self.session.query(New_order_undelivered).filter_by(order_id=order_id) order=row.first() print("未发货订单",order) if order is None: return error.error_invalid_order_id(order_id) store_id=order.store_id #判断该用户是否有这个店铺。。。。验证发货的人是否正确 query=self.session.query(User_store).filter_by(store_id=store_id).first() seller_id=query.user_id if seller_id != user_id: return error.error_authorization_fail() #有该订单,发货 buyer_id=order.buyer_id price=order.price #删除未发货 row.delete() #添加已发货 timenow = datetime.now().strftime("%Y-%m-%d %H:%M:%S") print("******已发货") new_order=New_order_unreceived( order_id=order_id, buyer_id=buyer_id, store_id=store_id, price=price, purchase_time=timenow, receive_time=None ) self.session.add(new_order) self.session.commit() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def login(self, user_id: str, password: str, terminal: str) -> (int, str, str): token = "" try: code, message = self.check_password(user_id, password) if code != 200: return code, message, "" token = jwt_encode(user_id, terminal) # cursor = self.session.execute( # "UPDATE usr set token= '%s' , terminal = '%s' where user_id = '%s'"% (token, terminal, user_id) ) cursor=self.session.query(Users).filter(Users.user_id == user_id).first() #if cursor.rowcount == 0: if cursor is None: return error.error_authorization_fail() + ("", ) cursor.token = token cursor.terminal = terminal self.session.commit() except sqlite.Error as e: return 528, "{}".format(str(e)), "" except BaseException as e: return 530, "{}".format(str(e)), "" return 200, "ok", token
def unregister(self, user_id: str, password: str) -> (int, str): try: code, message = self.check_password(user_id, password) if code != 200: return code, message # cursor = self.session.execute("DELETE from usr where user_id='%s'"% (user_id,)) # if cursor.rowcount == 1: # self.session.commit() # else: # return error.error_authorization_fail() query = self.session.query(Users).filter(Users.user_id == user_id) query.delete() if query.first() is None: self.session.commit() else: return error.error_authorization_fail() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def payment(self, user_id: str, password: str, order_id: str) -> (int, str): #conn = self.conn try: # cursor = conn.execute("SELECT order_id, user_id, store_id FROM new_order WHERE order_id = ?", (order_id,)) # row = cursor.fetchone() #待支付中是否有该用户该订单 # row = self.session.execute( # "SELECT buyer_id,price,store_id FROM new_order_unpaid WHERE order_id = '%s'" % (order_id)).fetchone() row = self.session.query(New_order_unpaid).filter_by( order_id=order_id).first() print(row) if row is None: return error.error_invalid_order_id(order_id) # buyer_id = row[0] # price=row[1] # store_id = row[2] buyer_id = row.buyer_id price = row.price store_id = row.store_id if buyer_id != user_id: return error.error_authorization_fail() # cursor = conn.execute("SELECT balance, password FROM user WHERE user_id = ?;", (buyer_id,)) # row = cursor.fetchone() # 检查密码 余额 # row = self.session.execute( # "SELECT balance, password FROM usr WHERE user_id = '%s';" % (buyer_id)).fetchone() row = self.session.query(Users).filter_by(user_id=buyer_id).first() if row is None: return error.error_non_exist_user_id(buyer_id) check_password = row.password balance = row.balance #balance = row[0] #if password != row[1]: if password != check_password: return error.error_authorization_fail() #记录卖家id # cursor = self.session.execute("SELECT store_id, user_id FROM user_store WHERE store_id = '%s';"%(store_id)) # row = cursor.fetchone() row = self.session.query(User_store).filter_by( store_id=store_id).first() if row is None: return error.error_non_exist_store_id(store_id) #seller_id = row[1] seller_id = row.user_id if not self.user_id_exist(seller_id): return error.error_non_exist_user_id(seller_id) #cursor = self.session.execute("SELECT book_id, count, price FROM new_order_detail WHERE order_id = '%s';"%(order_id)) cursor = self.session.query(New_order_detail).filter_by( order_id=order_id) total_price = 0 for row in cursor.all(): count = row.count price = row.price total_price = total_price + price * count if balance < total_price: return error.error_not_sufficient_funds(order_id) #买家支付 余额扣钱 #cursor = self.session.execute("UPDATE usr set balance = balance - %d WHERE user_id = '%s' AND balance >= %d"%(total_price, buyer_id, total_price)) cursor = self.session.query(Users).filter( Users.user_id == buyer_id, Users.balance >= total_price) rowcount = cursor.update( {Users.balance: Users.balance - total_price}) if rowcount == 0: return error.error_not_sufficient_funds(order_id) #卖家加钱 #cursor = self.session.execute("UPDATE usr set balance = balance + %d WHERE user_id = '%s'"%(total_price, seller_id)) cursor = self.session.query(Users).filter( Users.user_id == seller_id) rowcount = cursor.update( {Users.balance: Users.balance + total_price}) if rowcount == 0: return error.error_non_exist_user_id(seller_id) # 删除待付订单 #cursor = self.session.execute("DELETE FROM new_order_unpaid WHERE order_id = '%s';"% (order_id )) query = self.session.query(New_order_unpaid).filter( New_order_unpaid.order_id == order_id) query.delete() print("*****") rowcount = query.first() #print(cursor.rowcount) #if cursor.rowcount == 0: if rowcount == 0: return error.error_invalid_order_id(order_id) #删除订单的详细信息 #还未发货暂不删除订单详细信息 # cursor = self.session.execute("DELETE FROM new_order_detail where order_id = ?", (order_id, )) # if cursor.rowcount == 0: # return error.error_invalid_order_id(order_id) #在待发货中加入该订单 timenow = datetime.now().strftime("%Y-%m-%d %H:%M:%S") print("******") new_orde = New_order_undelivered(order_id=order_id, buyer_id=buyer_id, store_id=store_id, price=price, purchase_time=timenow) self.session.add(new_orde) # re=self.session.execute( # "INSERT INTO new_order_undelivered(order_id, buyer_id,store_id,price,purchase_time) VALUES('%s', '%s','%s',%d,'%s');" % ( # order_id, buyer_id, store_id, price,timenow)) # print(re.rowcount) #self.session.commit() self.session.commit() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"