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()
