def addUser(user):
'''
:param user:
:return: 返回注册成功后用户的id；如果id为None说明注册失败，id如果为0表示该用户已经存在，id为1表示注册成功
'''
if not getUserById(user['telephone']):
try:
client = POOL.connection()
user_id = None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
# 4. 准备sql语句
# sql = 'insert into user(telephone,password,regist_date) ' \
# 'values("{telephone}","{password}",CURDATE())'. \
# format(telephone=user['telephone'], password=user['password'])
sql = sql_user.get('addUser').format(telephone=user['telephone'],
password=user['password'],
nickname=user['nickname'])
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
cursor.execute(sql)
user_id = int(cursor.lastrowid)
client.commit()
except Exception as ex:
client.rollback()
finally:
return user_id
else:
return -1
def delete_recipe(res):
try:
client = POOL.connection()
res_user = True
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('delete_make').format(recipe_id=res['recipe_id'])
cursor.execute(sql)
sql1 = sql_user.get('delete_ingredients').format(
recipe_id=res['recipe_id'])
cursor.execute(sql1)
sql2 = sql_user.get('delete_detail').format(recipe_id=res['recipe_id'])
cursor.execute(sql2)
sql4 = sql_user.get('delete_college').format(
recipe_id=res['recipe_id'])
cursor.execute(sql4)
sql5 = sql_user.get('delete_counter').format(
recipe_id=res['recipe_id'])
cursor.execute(sql5)
sql6 = sql_user.get('delete_comment').format(
recipe_id=res['recipe_id'])
cursor.execute(sql6)
sql3 = sql_user.get('delete_recipe').format(recipe_id=res['recipe_id'])
cursor.execute(sql3)
# print(res_user)
# res=res_user if res_user else None
client.commit()
except Exception as ex:
client.rollback()
finally:
# print(res_user)
return res_user
def insert_recipe_comment(res):
try:
res_data = None # 定义返回值
# re = None
# res_su = None
# time=None
client = POOL.connection() #链接池创建链接
# today_date = datetime.datetime.now().strftime('%Y-%m-%d')
# print(today_date)
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_comment.get("get_sceipe_comment").format(
recipe_id=res['recipe_id'])
cursor.execute(sql)
res_data = cursor.fetchall()
print(res_data)
# res_su = int(cursor.lastrowid)#获取返回的数据 只取一条
# print(res_data)
# print(res_su)
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
print(ex)
finally:
client.close()
return res_data
def select_order(res):
try:
client = POOL.connection()
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
res1 = None
# 4. 准备sql语句
sql = sql_order.get('select_ticket').format(order_id=res["order_id"])
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
cursor.execute(sql)
res1 = cursor.fetchone()
client.commit()
except Exception as ex:
client.rollback()
finally:
client.close()
return res1
# if __name__ == '__main__':
# # user = {
# # "order_id": "201902161512720", "scenic_id": 1,
# # "tourst_time": "2019-2-16", "onum": 4, "oname":"唐扬", "otelephone": "18792031925", "order_start": 1,
# # "telephone":"18792031925"
# # }
# user={
# "order_id":"201902161706756",
# }
# # print(user)
# get_info(user)
# if __name__ == '__main__':
# user={
# "order_id":"201902201702110"
# }
# select_order(user)
def insert_into_comment(res):
try:
res_data = None # 定义返回值
# re = None
# res_su = None
# time=None
client = POOL.connection() #链接池创建链接
today_date = datetime.datetime.now().strftime('%Y-%m-%d')
print(today_date)
print(res)
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_comment.get("insert_sceipe_comment").format(
user_id=res['user_id'],
comm=res['comm'],
comment_time=today_date,
recipe_id=res['recipe_id'])
cursor.execute(sql)
# res_data=cursor.fetchall()
# print(res_data)
res_data = int(cursor.lastrowid) #获取返回的数据 只取一条
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
print(ex)
finally:
client.close()
return res_data
# if __name__ == '__main__':
# a={"recipe_id":6}
# insert_recipe_comment(a)
def user_count(res):
try:
client = POOL.connection() #链接池创建链接
res_data = None #定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_mycollection.get('user_coun').format(
recipe_id=res['recipe_id']) #sql语句
cursor.execute(sql) #执行sql语句
res_data = cursor.fetchall() #获取返回的数据 只取一条
print(res_data)
res = 0
for i in res_data:
# print(i)
sql1 = sql_mycollection.get('select_counter').format(
recipe_id=i['id'])
# print(sql1)
cursor.execute(sql1)
re = cursor.fetchone()
# print(re)
res += re['counter']
# print(res)
re = res if res else None
print(re)
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return re
def update_user(res):
try:
client = POOL.connection()
res_user = -1
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
print(res)
# 4. 准备sql语句
sql = sql_user.get('update_user').format(nickname=res['nickname'],
email=res['email'],
name=res['name'],
qq=res['qq'],
sex=res['sex'],
age=res['age'],
id=res['user_id'])
print(sql)
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
cursor.execute(sql)
res_user = int(cursor.lastrowid)
print(res_user)
# res=res_user if res_user else None
client.commit()
except Exception as ex:
client.rollback()
finally:
# print(res_user)
return res_user
def getdatabyid6(id):
try:
client = POOL.connection()
res_data6 = None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('getdatabyid6').format(id=id['id'])
cursor.execute(sql)
res_data6 = cursor.fetchone()
client.commit()
except Exception as ex:
client.rollbake()
finally:
client.close()
return res_data6
def getpic2(id):
try:
client = POOL.connection()
res_pic2=None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('getpic2').format(tid=id['tid'])
cursor.execute(sql)
res_pic2= cursor.fetchone()
client.commit()
print(res_pic2)
except Exception as ex:
client.rollbake()
finally:
client.close()
return res_pic2
def rande(id):
try:
client = POOL.connection()
res_pic1=None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('rande').format()
cursor.execute(sql)
res_pic1= cursor.fetchall()
client.commit()
print(res_pic1)
except Exception as ex:
client.rollbake()
finally:
client.close()
return res_pic1
def uid(id):
try:
client = POOL.connection()
res_id = None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('get_u_id').format(telephone=id['telephone'])
cursor.execute(sql)
res_id = cursor.fetchone()
client.commit()
except Exception as ex:
client.rollbake()
finally:
client.close()
return res_id
def select_info(res):
try:
client = POOL.connection()
res_user = True
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('select_userinfo').format(user_id=res['user_id'])
cursor.execute(sql)
res_user = cursor.fetchall()
print(res_user)
# res=res_user if res_user else None
client.commit()
except Exception as ex:
client.rollback()
finally:
# print(res_user)
return res_user
def get_alluser():
try:
client = POOL.connection()
res_user = True
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('select_alluser')
cursor.execute(sql)
res_user = cursor.fetchall()
# print(res_user)
# res=res_user if res_user else None
client.commit()
except Exception as ex:
client.rollback()
finally:
# print(res_user)
return res_user
def shanchu(id):
try:
client = POOL.connection()
res_id = None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('shanchu').format(scenic_id=id['scenic_id'])
cursor.execute(sql)
res_id = int(cursor.lastrowid)
# print(res_id)
client.commit()
except Exception as ex:
client.rollbake()
finally:
client.close()
return res_id
def get_order_detail(usertelephone):
try:
client = POOL.connection() #链接池创建链接
res_data = None #定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_myorder.get('get_order_detail').format(
telephone=usertelephone['telephone']) #sql语句
cursor.execute(sql) #执行sql语句
res_data = cursor.fetchall() #获取返回的数据 只取一条
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return res_data
def get_personal_date(user_id):
try:
client = POOL.connection() #链接池创建链接
res_data = None #定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_personal_date.get('get_personal_date').format(
user_id=user_id['user_id']) #sql语句
cursor.execute(sql) #执行sql语句
res_data = cursor.fetchone() #获取返回的数据 只取一条
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return res_data
def select_recipe_collection(res):
try:
client = POOL.connection() #链接池创建链接
res_data = None #定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_mycollection.get('select_college').format(
recipe_id=res['recipe_id']) #sql语句
cursor.execute(sql) #执行sql语句
res_data = cursor.fetchone() #获取返回的数据 只取一条
print(res_data)
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return res_data
def del_comment(res):
try:
client = POOL.connection() #链接池创建链接
res_data = None #定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_comment.get('del_comment').format(
order_id=res['order_id']) #sql语句
cursor.execute(sql) #执行sql语句
res = cursor.fetchall() # 获取返回的数据 只取一条
res_data = res if res else -1
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return res_data
def into(id):
try:
client = POOL.connection()
res_id = None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('into').format(user_id=id['user_id'],
scenic_id=id['scenic_id'])
cursor.execute(sql)
res_id = cursor.fetchone()
# print(res_id)
client.commit()
except Exception as ex:
client.rollbake()
finally:
client.close()
return res_id
def getUserById(id):
try:
client = POOL.connection()
res_user = -1
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
# 4. 准备sql语句
sql = sql_user.get('getUserById').format(telephone=id)
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
cursor.execute(sql)
res_user = cursor.fetchone()
client.commit()
except Exception as ex:
client.rollback()
finally:
# print(res_user)
return res_user
def delete_counter_on_2(res):
try:
client = POOL.connection() #链接池创建链接
res_data = None #定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_mycollection.get('delete_counter').format(
recipe_id=res['recipe_id'], user_id=res['user_id']) #sql语句
cursor.execute(sql) #执行sql语句
res_data = int(cursor.lastrowid) #获取返回的数据 只取一条
re = res_data if res_data else None
# print(res_data)
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return re
def insert_recipe(res):
try:
client = POOL.connection()
res_user = None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('insert_recipe').format(rname=res['rname'],
user_id=int(res['user_id']),
src=res['src'])
cursor.execute(sql)
res_user = int(cursor.lastrowid)
# print(res_user)
# res=res_user if res_user else None
client.commit()
except Exception as ex:
client.rollback()
finally:
# print(res_user)
return res_user
def select_recipe_comment(res):
try:
res_data = None # 定义返回值
# re = None
# res_su = None
# time=None
client = POOL.connection() #链接池创建链接
today_date = datetime.datetime.now().strftime('%Y-%m-%d')
# print(today_date)
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql1 = sql_mydetails.get("select_user_id").format(
telephone=res['telephone'])
cursor.execute(sql1)
re = cursor.fetchone()
# print(re)
sql2 = sql_mydetails.get('insert_comment').format(
order_id=res['order_id'],
user_id=re['id'],
comm=res['content'],
comment_time=today_date,
scenic_id=res['scenic_id'])
cursor.execute(sql2) # 执行sql语句
res_data = int(cursor.lastrowid)
# print(res_data)
sql = sql_mydetails.get('update_order_ticket').format(
order_id=res['order_id'], order_start=res['order_start']) #sql语句
cursor.execute(sql) #执行sql语句
res_su = int(cursor.lastrowid) #获取返回的数据 只取一条
# print(res_data)
# print(res_su)
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
print(ex)
finally:
client.close()
return res_data
# if __name__ == '__main__':
# a={"order_id":'201902192001568'}
# get_order_mydetail(a)
def get_ticket(res):
try:
res1 = None
client = POOL.connection()
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
# 4. 准备sql语句
sql = sql_order.get('get_ticket').format(tid=res["tid"])
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
cursor.execute(sql)
res1 = cursor.fetchall()
client.commit()
except Exception as ex:
client.rollback()
finally:
client.close()
return res1
def update_order(res):
try:
res1 = None
client = POOL.connection()
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
# 4. 准备sql语句
sql = sql_order.get('update_ticket').format(
order_id=res["order_id"], order_start=res["order_start"])
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
cursor.execute(sql)
res1 = int(cursor.lastrowid)
client.commit()
except Exception as ex:
client.rollback()
finally:
client.close()
return res1
def sele_info(res):
try:
client = POOL.connection()
res_user = -1
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
print(res)
# 4. 准备sql语句
sql = sql_user.get('select_adm').format(id=res['user_id'])
print(sql)
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
cursor.execute(sql)
res_user = cursor.fetchone()
print(res_user)
# res=res_user if res_user else None
client.commit()
except Exception as ex:
client.rollback()
finally:
# print(res_user)
return res_user
def get_order_mydetail(order_id):
try:
client = POOL.connection() #链接池创建链接
res_data = None #定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_mydetails.get('get_order_mydetail').format(
order_id=order_id['order_id']) #sql语句
cursor.execute(sql) #执行sql语句
res_data = cursor.fetchall() #获取返回的数据 只取一条
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return res_data
# if __name__ == '__main__':
# a={"telephone":18792031925}
# get_order_mydetail(a)
def select_comment(id):
try:
client = POOL.connection()
res_id = None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('select_comment').format(scenic_id=id['scenic_id'])
cursor.execute(sql)
res_id = cursor.fetchall()
print(res_id)
client.commit()
except Exception as ex:
client.rollbake()
finally:
client.close()
return res_id
# if __name__ == '__main__':
# s={"scenic_id":1}
# select_comment(s)
def del_order_detail(usertelephone):
try:
client = POOL.connection() #链接池创建链接
res_data = None #定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_myorder.get('del_order_detail').format(
order_id=usertelephone['order_id']) #sql语句
cursor.execute(sql) #执行sql语句
res = cursor.fetchall() #获取返回的数据 只取一条
res_data = res if res else -1
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return res_data
# if __name__ == '__main__':
# a={"telephone":"18715276983"}
# get_order_detail(a)
def order_create(res):
try:
from datetime import datetime
now = datetime.now()
strnow = datetime.strftime(now, '%Y-%m-%d %H:%M:%S')
res1 = None
res2 = None
user_id = None
client = POOL.connection()
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
# 4. 准备sql语句
sql1 = sql_order.get('getUserById').format(telephone=res["telephone"])
cursor.execute(sql1)
res2 = cursor.fetchone()
user_id = res2['id']
sql = sql_order.get('order_create').format(
order_id=res["order_id"],
scenic_id=res["scenic_id"],
user_id=user_id,
order_time=strnow,
tourst_time=res["tourst_time"],
onum=res["onum"],
oname=res["oname"],
otelephone=res["otelephone"],
order_start=res["order_start"])
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
cursor.execute(sql)
res1 = int(cursor.lastrowid)
# print(res1)
client.commit()
except Exception as ex:
print(ex)
client.rollback()
finally:
client.close()
return res1
