def DeleteFood(id):
db, cursor = connect()
tables_name = 'food'
cursor.execute("delete from %s where id='%s' " %(tables_name,id))
db.commit()
close(db)
print("该食物数据已删除成功")
def DeleteUserInformation(UID):
db, cursor = connect()
tables_name = 'User'
cursor.execute("delete from %s where UID='%s' " %(tables_name,UID))
db.commit()
close(db)
print("该数据已删除成功")
def ResearchFood(tables_name='food',
id=None,
FoodName=None,
NUM=3,
Ingredients=None):
"""
:param id: 唯一标签
:param FoodName: 食物名称
:param Ingredients: 材料需求
:param num: 返回结果数目
:return: 数据库查询结果(list)
"""
lists = []
db, cursor = connect()
# 使用execute()方法执行SQL查询,根据传输参数不同，获得不同参数结果
if FoodName != None and Ingredients != None:
cursor.execute("select * from %s WHERE FoodName LIKE '%%%s%%' and Ingredients LIKE '%%%s%%' limit %d" % \
(tables_name, FoodName, Ingredients, NUM))
elif id != None:
cursor.execute("select * from %s WHERE id = '%s'" % \
(tables_name, id))
elif FoodName != None:
cursor.execute("select * from %s WHERE FoodName LIKE '%%%s%%' limit %d" % \
(tables_name, FoodName, NUM))
elif Ingredients != None:
cursor.execute("select * from %s WHERE Ingredients LIKE '%%%s%%' limit %d" % \
(tables_name,Ingredients, NUM))
else:
print("所提供参数不足以查询数据库！")
return -1
# 使用 fetchone() 方法获取单条数据.
# data = cursor.fetchone()
# 选择所有数据
data = cursor.fetchall()
for d in data:
id = d[0]
FoodName = d[1]
Url = d[2]
Ingredients = CleanData(d[3])
Mainpicture = d[4]
Steps = d[5]
StepPicture = d[6]
list = [
id, FoodName, Url, Ingredients, Mainpicture, Steps, StepPicture
]
lists.append(list)
# 关闭数据库连接
close(db)
return lists
def addFoodInformation(FoodName, Url, Ingredients, Mainpicture, Steps,
StepPicture):
db, cursor = connect()
tables_name = 'food'
sql = ("insert into {} (FoodName, Url, Ingredients, Mainpicture, Steps, StepPicture) "\
"values(%s,%s,%s,%s,%s,%s)".format(tables_name))
cursor.execute(
sql, (FoodName, Url, Ingredients, Mainpicture, Steps, StepPicture))
db.commit()
close(db)
print("成功加入新菜谱")
def UpdateUserInfmation(UID,Name=None,Passwd=None,Region=None,Flavor=None,History=None):
db, cursor = connect()
tables_name = 'User'
cursor.execute("select * from %s WHERE UID = %s" % (tables_name, UID))
datas = cursor.fetchall()
for data in datas:
UID = data[0]
new_Name = data[1] if Name == None else Name
new_Passwd = data[2] if Passwd == None else Passwd
new_Region = data[3] if Region == None else Region
new_Flavor = data[4] if Flavor == None else Flavor
new_History = data[5] if History == None else History
cursor.execute(
"update %s set Name = '%s',Passwd = '%s',Region='%s',Flavor='%s', History='%s' WHERE UID = %s" % \
(tables_name, new_Name, new_Passwd, new_Region, new_Flavor, new_History, UID))
db.commit()
close(db)
print("用户数据库中UID={}更新成功".format(id))
def addUserInformation(Name, Passwd, Region, Flavor=None, History=None):
'''
用户注册函数
:param Name:注册人昵称
:param Passwd:注册人密码
:param Region:注册人地区
:param Flavor:注册人口味爱好
:param History:注册人历史搜索
:return:用户唯一UID
'''
db, cursor = connect()
tables_name = 'User'
sql = ("insert into {} (Name,Passwd,Region,Flavor,History) "\
"values(%s,%s,%s,%s,%s)".format(tables_name))
cursor.execute(sql, (Name, Passwd, Region, Flavor, History))
UID = cursor.lastrowid
db.commit()
close(db)
print("成功加入用户")
return UID
def UpdateFood(id,FoodName =None,Url = None,Ingredients=None,\
Mainpicture=None,Steps=None,StepPicture=None,\
tables_name='food'):
db, cursor = connect()
cursor.execute("select * from %s WHERE id = %s" %(tables_name, id))
datas = cursor.fetchall()
for data in datas:
id = data[0]
new_FoodName = data[1] if FoodName == None else FoodName
new_Url = data[2] if Url == None else Url
new_Ingredients = data[3] if Ingredients == None else Ingredients
new_Mainpicture = data[4] if Mainpicture == None else Mainpicture
new_Steps = data[5] if Steps == None else Steps
new_StepPicture = data[6] if StepPicture == None else StepPicture
cursor.execute("update %s set FoodName = '%s',Url = '%s',Ingredients='%s',Mainpicture='%s', Steps='%s',StepPicture='%s' WHERE id = %s" % \
(tables_name, new_FoodName, new_Url,new_Ingredients,new_Mainpicture,new_Steps,new_StepPicture,id))
db.commit()
close(db)
print("食物数据库中id={}更新成功".format(id))