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