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 get_comms(res):
try:
client = POOL.connection() #链接池创建链接
res_data = None #定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) #游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_mycollection.get('get_comments').format(
recipe_id=res['recipe_id']) #sql语句
cursor.execute(sql) #执行sql语句
res_data = cursor.fetchone() #获取返回的数据 只取一条
print(res_data)
re = res_data if res_data else None
# print(re)
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return re
def insert_detail(res):
try:
client = POOL.connection()
res_user = True
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('insert_detail').format(recipe_id=int(
res['recipe_id']),
src=res['src'],
describe=res['describe'])
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 getnewid1(id):
try:
client = POOL.connection()
res_id = None
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
sql = sql_user.get('getnewid').format(telephone=id['telephone'],
scenic_id=id['scenic_id'])
cursor.execute(sql)
res_id = cursor.fetchone()
client.commit()
except Exception as ex:
client.rollbake()
finally:
client.close()
return res_id
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 insert_order_mydetail(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
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
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 user_colleg(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_college').format(
recipe_id=i['id'])
# print(sql1)
cursor.execute(sql1)
re = cursor.fetchone()
# print(re)
res += re['college']
# print(res)
re = res if res else None
print(re)
client.commit() #链接提交事务 还有游标结束
except Exception as ex:
client.rollback() #出错的话 链接回滚
finally:
client.close() #结束链接 返回结果给服务层
return re
# if __name__ == '__main__':
# res={"recipe_id":8}
# user_colleg(res)
def get_info(res):
try:
res3 = {}
client = POOL.connection()
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
# 4. 准备sql语句
sql = sql_order.get('getOrderinfo').format(order_id=res["order_id"])
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
cursor.execute(sql)
res1 = cursor.fetchone()
sql1 = sql_order.get('get_ticket').format(tid=res1['scenic_id'])
cursor.execute(sql1)
res2 = cursor.fetchone()
# print(res1)
# print(res2)
client.commit()
res3.update(res1)
res3.update(res2)
# print(res3)
except Exception as ex:
client.rollback()
finally:
client.close()
return res3
def inset_detail(res):
try:
client = POOL.connection()
cursor = client.cursor(cursor=pymysql.cursors.DictCursor)
res_user = insert_recipe(res)
# 4. 准备sql语句
print(res_user)
sql1 = sql_user.get('insert_detail').format(recipe_id=int(res_user),
src=res['src'],
describes=res['describes'])
cursor.execute(sql1)
# a = int(cursor.lastrowid)
# print(a)
sql2 = sql_user.get('insert_make').format(step=res['step'],
recipe_id=int(res_user))
cursor.execute(sql2)
# b=int(cursor.lastrowid)
# print(b)
for a in res['listItem']:
sql3 = sql_user.get('insert_ingredients').format(
recipe_id=int(res_user),
ingredients=a['ingredients'],
amount=a['amount'])
cursor.execute(sql3)
# 5. 通过游标进行操作,execute()执行sql语句,这时结果为：1.如果插入成功返回受影响的行数 2. 如果插入失败返回None
print(res_user)
res = 1
# res=res_user if res_user else None
client.commit()
except Exception as ex:
client.rollback()
finally:
# print(res_user)
return res
def change_personal_date(user_id):
try:
client = POOL.connection() # 链接池创建链接
res_data = None # 定义返回值
cursor = client.cursor(
cursor=pymysql.cursors.DictCursor) # 游标 操作数据和获取数据库结果都要通过游标来操作
sql = sql_personal_date.get('change_personal_date').format(
nickname=user_id['nickname'],
email=user_id['email'],
name=user_id['name'],
card_id=user_id['card_id'],
sex=user_id['sex'],
qq=user_id['qq'],
telephone=user_id['telephone']) # 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
