def update_commodity(self): self._create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())) success = 0 sql = "update commodity set commodity_name = '%s',commodity_type='%s',owner_student_id='%s',price=%.2f,\ commodity_introduction='%s',commodity_photo_url1='%s',commodity_photo_url2='%s',commodity_photo_url3='%s',\ commodity_photo_url4='%s',commodity_photo_url5='%s',create_time='%s',status=%d \ where id = %d" % ( self._commodity_name, self._commodity_type, self._owner_student_id, self._price, self._commodity_introduction, self._commodity_photo_url1, self._commodity_photo_url2, self._commodity_photo_url3, self._commodity_photo_url4, self._commodity_photo_url5, self._create_time, self._status, self._id) try: db = get_db() cursor = db.cursor() print(sql) cursor.execute(sql) db.commit() success = 1 except: print(sql) db.rollback() db.close() return success
def add_commodity(self): self._create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())) success = 0 sql = "insert into commodity(commodity_name,commodity_type,owner_student_id,price,\ commodity_introduction,commodity_photo_url1,commodity_photo_url2,commodity_photo_url3,\ commodity_photo_url4,commodity_photo_url5,create_time,status) \ VALUES('%s','%s','%s',%.2f,'%s','%s','%s','%s','%s','%s','%s',%d)" % ( self._commodity_name, self._commodity_type, self._owner_student_id, self._price, self._commodity_introduction, self._commodity_photo_url1, self._commodity_photo_url2, self._commodity_photo_url3, self._commodity_photo_url4, self._commodity_photo_url5, self._create_time, self._status) try: db = get_db() cursor = db.cursor() print(sql) cursor.execute(sql) db.commit() success = 1 except: db.rollback() db.close() return success
def search_user_without_page(id="%%",commodity_id="%%",commodity_name="%%",buyer_id="%%",school_number="%%",status="%%",create_time="%%"): sql=""" SELECT * FROM comorder WHERE id LIKE %s AND commodity_id LIKE %s AND commodity_name LIKE %s AND buyer_id LIKE %s AND school_number LIKE %s AND status LIKE %s AND create_time LIKE %s """ db = get_db() cursor=db.cursor() cursor.execute(sql,(id,commodity_id,commodity_name,buyer_id,school_number,status,create_time)) print(id,commodity_id,commodity_name,buyer_id,school_number,status,create_time) print(cursor.mogrify(sql,(id,commodity_id,commodity_name,buyer_id,school_number,status,create_time))) results=cursor.fetchall() ordes=[] if results is None: return None for result in results: orde=Order.create_ord_from_rows(result) ordes.append(orde) return ordes,len(ordes) db.close()
def get_order_by_commodity_id(commodity_id): db = get_db() cursor = db.cursor() cursor.execute('SELECT* FROM comorder WHERE commodity_id = %s',(str(commodity_id))) results = cursor.fetchone() if results is None: return None orde=Order.create_ord_from_rows(results) return orde db.close()
def end_time(self,new_val): db=get_db() cursor=db.cursor() sql="UPDTE block SET end_time=%s WHERE id=%s" try: cursor.execute(sql,(new_val,self.id)) db.commit() except: db.rollback() raise
def del_block(self): db=get_db() cursor=db.cursor() sql="DELETE FROM block WHERE id=%s" try: cursor.execute(sql,self.id) db.commit() except: db.rollback() raise
def get_block_by_id(id): db=get_db() cursor=db.cursor() sql="SELECT id,user_id,reason,start_time,end_time FROM block WHERE id=%s" try: cursor.execute(sql,id) result=cursor.fetchone() return Block(*result) except: db.rollback() raise
def get_order_by_buyer_id(buyer_id): db = get_db() cursor = db.cursor() cursor.execute('SELECT* FROM comorder WHERE buyer_id = %s',str(buyer_id)) results = cursor.fetchone() print("results=",results) if results is None: return None orde=Order.create_ord_from_rows(results) return orde db.close()
def password(self, new_val): if self.check_password(new_val): return self._password = generate_password_hash(new_val) db = get_db() cursor = db.cursor() try: cursor.execute("UPDATE user SET password=%s WHERE id=%s", (self._password, self._id)) db.commit() except: db.rollback() abort(500)
def salt(self, new_val): if self._salt == new_val: return self._salt = new_val db = get_db() cursor = db.cursor() try: cursor.execute("UPDATE user SET salt=%s WHERE id=%s", (self._salt, self._id)) db.commit() except: db.rollback() abort(500)
def get_block_list_by_user_id(user_id): db=get_db() cursor=db.cursor() sql="SELECT id,user_id,reason,start_time,end_time FROM block WHERE user_id=%s" try: cursor.execute(sql,user_id) results=cursor.fetchall() ans=[] for result in results: ans.append(Block(*result)) return ans except: db.rollback() raise
def get_order_by_school_number_and_status(school_number,new_value): db = get_db() cursor = db.cursor() cursor.execute('SELECT* FROM comorder WHERE school_number = %s and status = %s',(str(school_number),(str(new_value)))) results = cursor.fetchall() print("results=",results) ordes=[] if results is None: return None for result in results: orde=Order.create_ord_from_rows(result) ordes.append(orde) return ordes,len(ordes) db.close()
def status(self, new_val): # 如果新值等于原始值则跳过修改 if self._status == new_val: return self._status = new_val db = get_db() cursor = db.cursor() try: cursor.execute("UPDATE user SET status=%s WHERE id=%s", (self._status, self._id)) db.commit() except: db.rollback() abort(500)
def get_order_by_school_number(school_number): db = get_db() cursor = db.cursor() cursor.execute('SELECT* FROM comorder WHERE school_number = %s or buyer_id=%s',(str(school_number),str(school_number))) results = cursor.fetchall() print(results) ordes=[] if results is None: return None for result in results: orde=Order.create_ord_from_rows(result) ordes.append(orde) return ordes,len(ordes) db.close()
def activation(self, new_val): # 如果新值等于原始值则跳过修改 if self._activation == new_val: return self._activation = new_val db = get_db() cursor = db.cursor() try: cursor.execute("UPDATE user SET activation=%s WHERE id=%s", (self._activation, self._id)) db.commit() except: db.rollback() abort(500)
def delete_comment_by_id(id): success = 0 sql = "delete from comment where id = %d" % (id) try: db = get_db() cursor = db.cursor() cursor.execute(sql) db.commit() success = 1 except: db.rollback() db.close() return success
def username(self, new_val): if self._username == new_val: return user = User.get_user_by_username(new_val) if user is not None: raise UsernameDuplicate(new_val) self._username = new_val db = get_db() cursor = db.cursor() try: cursor.execute("UPDATE user SET username=%s WHERE id=%s", (self._username, self._id)) db.commit() except: db.rollback() abort(500)
def delete_order(self): db = get_db() # 使用cursor()方法获取操作游标 cursor = db.cursor() print(self._id) sql = "DELETE FROM comorder WHERE id = %s" try: # 执行sql语句 cursor.execute(sql,(self._id)) # 提交到数据库执行 db.commit() except: # 如果发生错误则回滚 db.rollback() # 关闭数据库连接 db.close()
def create_block(user_id,reason,start_time,end_time): db=get_db() cursor=db.cursor() sql="INSERT INTO block (user_id,reason,start_time,end_time) VALUES(%s,%s,%s,%s)" try: cursor.execute(sql,(user_id,reason,start_time,end_time)) db.commit() sql="SELECT LAST_INSERT_ID();" cursor.execute(sql) result=cursor.fetchone() print(result) block=Block.get_block_by_id(result[0]) print(block) return block except: db.rollback() raise
def email(self, new_val): # 如果新值等于原始值则跳过修改 from free_shark.utils import is_email if self._email == new_val: return if not is_email(new_val): raise UserEmailInvalid(new_val) self._email = new_val db = get_db() cursor = db.cursor() try: cursor.execute("UPDATE user SET email=%s WHERE id=%s", (self._email, self._id)) db.commit() self.status = 2 except: db.rollback() abort(500)
def status(self,new_val): self._status=new_val db = get_db() # 使用cursor()方法获取操作游标 cursor = db.cursor() # 创建时间 sql = "UPDATE comorder SET status=%s WHERE id=%s" try: # 执行sql语句 cursor.execute(sql,(self._status,self._id)) # 提交到数据库执行 db.commit() except: # 如果发生错误则回滚 db.rollback() raise # 关闭数据库连接 db.close()
def add_comment(self): self._create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())) success = 0 sql = "insert into comment(comment_content,commodity_id,user_id,username,status,create_time) \ VALUES('%s',%d,%d,'%s',%d,'%s')" % ( self._comment_content, self._commodity_id, self._user_id, self._username, self._status, self._create_time) try: db = get_db() cursor = db.cursor() print(sql) cursor.execute(sql) db.commit() success = 1 except: db.rollback() db.close() return success
def get_order_by_id_commodity(id=None,commodity_name=None,school_number=None): db = get_db() cursor = db.cursor() if commodity_name is None and id is None: cursor.execute('SELECT* FROM comorder WHERE school_number = %s or buyer_id=%s',(str(school_number),str(school_number))) elif commodity_name is None: cursor.execute('SELECT* FROM comorder WHERE id = %s and (school_number = %s or buyer_id=%s)',(str(id),str(school_number),str(school_number))) elif id is None: cursor.execute('SELECT* FROM comorder WHERE commodity_name = %s and (school_number = %s or buyer_id=%s)',(str(commodity_name),str(school_number),str(school_number))) else: cursor.execute('SELECT* FROM comorder WHERE (id = %s or commodity_name = %s) and (school_number = %s or buyer_id=%s)',(str(id),str(commodity_name),str(school_number),str(school_number))) results = cursor.fetchall() print(results) ordes=[] if results is None: return None for result in results: orde=Order.create_ord_from_rows(result) ordes.append(orde) return ordes,len(ordes) db.close()