def logout(self, user_id: str, token: str) -> (int, str): try: code, message = self.check_token(user_id, token) if code != 200: return code, message terminal = "terminal_{}".format(str(time.time())) dummy_token = jwt_encode(user_id, terminal) # cursor = self.conn.execute( # "UPDATE user SET token = ?, terminal = ? WHERE user_id=?", # (dummy_token, terminal, user_id), ) # if cursor.rowcount == 0: # return error.error_authorization_fail() # self.conn.commit() row = db_session.query(User).filter_by(user_id=user_id).first() if row is None: return error.error_authorization_fail() row.token = dummy_token row.terminal = terminal db_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 add_book(self, user_id: str, store_id: str, book_id: str, book_json_str: str, stock_level: int, title: str, author: str, content: str, tag: str): try: if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) if not self.store_id_exist(store_id): return error.error_non_exist_store_id(store_id) if self.book_id_exist(store_id, book_id): return error.error_exist_book_id(book_id) # self.conn.execute("INSERT into store(store_id, book_id, book_info, stock_level)" # "VALUES (?, ?, ?, ?)", (store_id, book_id, book_json_str, stock_level)) # self.conn.commit() store_tmp = Store(store_id=store_id, book_id=book_id, book_info=book_json_str, stock_level=stock_level, tag=tag, author=author, content=content, title=title) db_session.add(store_tmp) db_session.commit() except BaseException as e: print(str(e)) return 530, "{}".format(str(e)) return 200, "ok"
def change_password(self, user_id: str, old_password: str, new_password: str) -> (int, str): 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), ) # if cursor.rowcount == 0: # return error.error_authorization_fail() # self.conn.commit() row = db_session.query(User).filter_by(user_id=user_id).first() if row is None: return error.error_authorization_fail() row.password = new_password row.token = token row.terminal = terminal db_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 add_funds(self, user_id: str, password: str, add_value: int) -> (int, str): try: # cursor = self.conn.execute("SELECT password from user where user_id=?", (user_id,)) # row = cursor.fetchone() row = User.query.filter_by(user_id=user_id).first() if row is None: return error.error_authorization_fail() if row.password != password: return error.error_authorization_fail() balance = row.balance # cursor = self.conn.execute( # "UPDATE user SET balance = balance + ? WHERE user_id = ?", # (add_value, user_id)) cursor = User.query.filter_by(user_id=user_id).update({"balance": balance + add_value}) if cursor == 0: return error.error_non_exist_user_id(user_id) db_session.commit() # self.conn.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.conn.execute( # "UPDATE user set token= ? , terminal = ? where user_id = ?", # (token, terminal, user_id), ) # if cursor.rowcount == 0: # return error.error_authorization_fail() + ("", ) # self.conn.commit() row = db_session.query(User).filter_by(user_id=user_id).first() if row is None: return error.error_authorization_fail() + ("", ) row.token = token row.terminal = terminal db_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 cancel_order(self, user_id: str, password: str, order_id: str) -> (int, str): try: if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) row = User.query.filter_by(user_id=user_id).first() if password != row.password: return error.error_authorization_fail() buyer_balance = row.balance row = New_Order.query.filter(New_Order.user_id == user_id, New_Order.order_id == order_id).first() if row is None: return error.error_invalid_order_id(order_id) store_id = row.store_id # 已经发货了就不能取消订单了,因此状态码只能是1才能主动取消订单 row = New_Order_Detail.query.filter_by(order_id=order_id).first() if row.state != 1: return error.error_cancel_order(order_id) # 可以取消,则买家收钱,卖家减钱,状态码改为-1,表示订单关闭 cursor = New_Order_Detail.query.filter_by(order_id=order_id).all() total_price = 0 for row in cursor: count = row.count price = row.price total_price = total_price + price * count row = User.query.filter_by(user_id=user_id).update({"balance": buyer_balance + total_price}) if row == 0: return error.error_non_exist_user_id(user_id) row = User_Store.query.filter_by(store_id=store_id).first() if row is None: return error.error_non_exist_store_id(store_id) seller_id = row.user_id row = User.query.filter_by(user_id=seller_id).first() if row is None: return error.error_non_exist_user_id(seller_id) seller_balance = row.balance row = User.query.filter_by(user_id=seller_id).update({"balance": seller_balance - total_price}) if row == 0: return error.error_not_sufficient_funds(order_id) # 将订单状态改为-1 row = New_Order_Detail.query.filter_by(order_id=order_id).update({"state": -1}) if row == 0: return error.error_invalid_order_id(order_id) db_session.commit() except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def register(self, user_id: str, password: str): try: terminal = "terminal_{}".format(str(time.time())) token = jwt_encode(user_id, terminal) user_tmp = User(user_id, password, 0, token, terminal) db_session.add(user_tmp) db_session.commit() except BaseException as e: return error.error_exist_user_id(user_id) return 200, "ok"
def get_book_info(self, start, size) -> [Book]:#得到爬虫爬到的db.book里的内容 conn = sqlite.connect(self.book_db) cursor = conn.execute( "SELECT id, title, author, " "publisher, original_title, " "translator, pub_year, pages, " "price, currency_unit, binding, " "isbn, author_intro, book_intro, " "content, tags FROM book ORDER BY id " "LIMIT ? OFFSET ?", (size, start)) for row in cursor: # book = Book() # book.id = row[0] # book.title = row[1] # book.author = row[2] # book.publisher = row[3] # book.original_title = row[4] # book.translator = row[5] # book.pub_year = row[6] # book.pages = row[7] # book.price = row[8] # # book.currency_unit = row[9] # book.binding = row[10] # book.isbn = row[11] # book.author_intro = row[12] # book.book_intro = row[13] # book.content = row[14] book_tag=[] tags = row[15] for tag in tags.split("\n"): if tag.strip() != "": book_tag.append(tag) # book.tags.append(tag) # for i in range(0, random.randint(0, 9)): # if picture is not None: # encode_str = base64.b64encode(picture).decode('utf-8') # book_pic.append(encode_str) # book.pictures.append(encode_str) book_tag=str(book_tag)#要转换成string类型 book = Book(book_id=row[0], title=row[1], author=row[2], publisher=row[3], original_title=row[4],translator=row[5],pub_year=row[6], pages=row[7], price=row[8], currency_unit=row[9], binding=row[10],isbn=row[11],author_intro=row[12], book_intro=row[13], content=row[14],tags=book_tag) db_session.add(book) db_session.commit()
def create_store(self, user_id: str, store_id: str) -> (int, str): try: if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) if self.store_id_exist(store_id): return error.error_exist_store_id(store_id) # self.conn.execute("INSERT into user_store(store_id, user_id)" # "VALUES (?, ?)", (store_id, user_id)) # self.conn.commit() user_store = User_Store(user_id=user_id, store_id=store_id) db_session.add(user_store) db_session.commit() except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def send_stock(self, user_id: str, order_id: str) -> (int, str): try: print(user_id, order_id) row = New_Order.query.filter_by(order_id=order_id).first() if row is None: return error.error_invalid_order_id(order_id) row = User.query.filter_by(user_id=user_id).first() if row is None: return error.error_non_exist_user_id(user_id) db_session.query(New_Order_Detail).filter( New_Order_Detail.order_id == order_id).update( {New_Order_Detail.state: 2}) db_session.commit() except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def auto_cancel(): cursor = New_Order_Detail.query.filter_by(state=0).all() # 得到order_id_list order_id_list = [] for item in cursor: order_id_list.append(item.order_id) order_id_list = list(set(order_id_list)) # 去重 for id in order_id_list: row = New_Order_Detail.query.filter_by(order_id=id).first() end_time = time.time() print(end_time - row.time) if (end_time - row.time >= 600): # 付款时间超过10分钟自动取消 New_Order_Detail.query.filter_by(order_id=id).update({"state": -1}) print("自动取消", id) db_session.commit()
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.conn.execute("DELETE from user where user_id=?", (user_id,)) # if cursor.rowcount == 1: # self.conn.commit() # else: # return error.error_authorization_fail() row = db_session.query(User).filter_by(user_id=user_id).first() if row is None: return error.error_authorization_fail() else: db_session.delete(row) db_session.commit() except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def confirm_stock(self, user_id: str, password: str, order_id: str) -> (int, str): try: if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) row = User.query.filter_by(user_id=user_id).first() if password != row.password: return error.error_authorization_fail() cursor = New_Order_Detail.query.filter_by(order_id=order_id).first() if cursor is None: return error.error_invalid_order_id(order_id) if cursor.state != 2: return error.error_order_not_dispatched(order_id) cursor = New_Order_Detail.query.filter_by(order_id=order_id).update({"state": 3}) # 订单全部更新为3 if cursor == 0: return error.error_invalid_order_id(order_id) db_session.commit() except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def add_stock_level(self, user_id: str, store_id: str, book_id: str, add_stock_level: int): try: if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) if not self.store_id_exist(store_id): return error.error_non_exist_store_id(store_id) if not self.book_id_exist(store_id, book_id): return error.error_non_exist_book_id(book_id) print(user_id, store_id, book_id) # self.conn.execute("UPDATE store SET stock_level = stock_level + ? " # "WHERE store_id = ? AND book_id = ?", (add_stock_level, store_id, book_id)) # self.conn.commit() db_session.query(Store).filter( Store.store_id == store_id, Store.book_id == book_id).update( {Store.stock_level: Store.stock_level + add_stock_level}) db_session.commit() # except Error as e: # logging.info("528, {}".format(str(e))) # return 528, "{}".format(str(e)), "" except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
from be.model.database import db_session from be.table.user import User u = User(123, 231, 30,'sa', 'dsa') db_session.add(u) db_session.commit() # from be.model.db_conn import DBConn # a = DBConn.user_id_exist(user_id=0) # print(a)
def new_order(self, user_id: str, store_id: str, id_and_count: [(str, int)]) -> (int, str, str): order_id = "" try: if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) + (order_id,) if not self.store_id_exist(store_id): return error.error_non_exist_store_id(store_id) + (order_id,) uid = "{}_{}_{}".format(user_id, store_id, str(uuid.uuid1())) for book_id, count in id_and_count: # cursor = self.conn.execute( # "SELECT book_id, stock_level, book_info FROM store " # "WHERE store_id = ? AND book_id = ?;", # (store_id, book_id)) row = Store.query.filter_by(store_id=store_id, book_id=book_id).first() if row is None: return error.error_non_exist_book_id(book_id) + (order_id,) stock_level = row.stock_level book_info = row.book_info book_info_json = json.loads(book_info) price = book_info_json.get("price") if stock_level < count: return error.error_stock_level_low(book_id) + (order_id,) # cursor = self.conn.execute( # "UPDATE store set stock_level = stock_level - ? " # "WHERE store_id = ? and book_id = ? and stock_level >= ?; ", # (count, store_id, book_id, count)) row = Store.query.filter_by(store_id=store_id, book_id=book_id, stock_level=stock_level).update( {'stock_level': stock_level - count}) if row == 0: return error.error_stock_level_low(book_id) + (order_id,) # self.conn.execute( # "INSERT INTO new_order_detail(order_id, book_id, count, price) " # "VALUES(?, ?, ?, ?);", # (uid, book_id, count, price)) row = New_Order_Detail(order_id=uid, book_id=book_id, count=count, price=price, state=0, time=time.time()) db_session.add(row) # self.conn.execute( # "INSERT INTO new_order(order_id, store_id, user_id) " # "VALUES(?, ?, ?);", # (uid, store_id, user_id)) # self.conn.commit() row = New_Order(order_id=uid, store_id=store_id, user_id=user_id) db_session.add(row) order_id = uid db_session.commit() # except sqlite.Error as e: # logging.info("528, {}".format(str(e))) # return 528, "{}".format(str(e)), "" except BaseException as e: print(e) logging.info("530, {}".format(str(e))) return 530, "{}".format(str(e)), "" return 200, "ok", order_id
def payment(self, user_id: str, password: str, order_id: str) -> (int, str): try: # cursor = conn.execute("SELECT order_id, user_id, store_id FROM new_order WHERE order_id = ?", (order_id,)) # row = cursor.fetchone() row = New_Order.query.filter_by(order_id=order_id).first() if row is None: return error.error_invalid_order_id(order_id) order_id = row.order_id buyer_id = row.user_id 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 = User.query.filter_by(user_id=buyer_id).first() if row is None: return error.error_non_exist_user_id(buyer_id) balance = row.balance if password != row.password: return error.error_authorization_fail() # cursor = conn.execute("SELECT store_id, user_id FROM user_store WHERE store_id = ?;", (store_id,)) # row = cursor.fetchone() # 买家 row = User_Store.query.filter_by(store_id=store_id).first() if row is None: return error.error_non_exist_store_id(store_id) seller_id = row.user_id # 卖家 row = User.query.filter_by(user_id=seller_id).first() if row is None: return error.error_non_exist_user_id(seller_id) seller_balance = row.balance if not self.user_id_exist(seller_id): return error.error_non_exist_user_id(seller_id) # cursor = conn.execute("SELECT book_id, count, price FROM new_order_detail WHERE order_id = ?;", (order_id,)) cursor = New_Order_Detail.query.filter_by(order_id=order_id).all() total_price = 0 for row in cursor: 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 = conn.execute("UPDATE user set balance = balance - ?" # "WHERE user_id = ? AND balance >= ?", # (total_price, buyer_id, total_price)) cursor = User.query.filter(User.user_id == buyer_id, User.balance >= total_price).update( {'balance': balance - total_price}) if cursor == 0: return error.error_not_sufficient_funds(order_id) # cursor = conn.execute("UPDATE user set balance = balance + ?" # "WHERE user_id = ?", # (total_price, buyer_id)) # 卖家收钱,钱增多 cursor = User.query.filter_by(user_id=seller_id).update({'balance': seller_balance + total_price}) if cursor == 0: return error.error_non_exist_user_id(seller_id) # 将记录的state改为1,表示已付款 # cursor = conn.execute("DELETE FROM new_order WHERE order_id = ?", (order_id, )) cursor = New_Order_Detail.query.filter_by(order_id=order_id).update({"state": 1}) if cursor == 0: return error.error_invalid_order_id(order_id) # cursor = conn.execute("DELETE FROM new_order_detail where order_id = ?", (order_id, )) # cursor = New_Order_Detail.query.filter_by(order_id=order_id).delete() # if cursor == 0: # return error.error_invalid_order_id(order_id) db_session.commit() except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"