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