def findHotSail(): """ 查询热门航线 :param userId: :return: """ con = dbconection.dbConnection() cur = con.cursor() datas = [] try: sql = "SELECT DISTINCT hot_sail_content,count(1) as hotCount FROM hot_sail GROUP BY hot_sail_content having hotCount>=10 ORDER BY hotCount desc LIMIT 10" cur.execute(sql) result = cur.fetchall() if len(result) > 0: for item in result: info = HotSail() info.hotSailContent = item[0] info.hotCount = item[1] datas.append(info) except Exception as e: print(e) finally: con.close() return datas
def findUserByPhone(telephone): """ 根据手机号查询用户信息 :param telephone: :return: """ con = dbconection.dbConnection() cur = con.cursor() userInfo = User() try: sql = "select id,telephone,username,password from user where telephone=%s" cur.execute(sql, (telephone, )) result = cur.fetchone() if len(result) > 0: userInfo.id = result[0] userInfo.telephone = result[1] userInfo.username = result[2] userInfo.password = result[3] except Exception as e: print(e) finally: con.close() return userInfo
def findInfos(searchKey, departureDate): """ 根据搜索信息,查询航班信息 """ con = dbconection.dbConnection() cur = con.cursor() datas = [] dIndex = searchKey.find("到") try: sql = "select departureCityName,arrivalCityName,departureDate,departureDatetime,departureAirportName,arrivalDate,arrivalDatetime,arrivalAirportName,printprice,airlineName,flightNumber" if dIndex >= 0: sql = sql + " from ticket where" + (" departureDate=%s and " if len(departureDate) > 0 and departureDate != '-1' else "") + " departureCityName=%s and arrivalCityName=%s order by departureDate desc,departureDatetime" keys = searchKey.split("到") if len(departureDate)>0 and departureDate!='-1': cur.execute(sql, (departureDate,keys[0].strip(), keys[1].strip())) else: cur.execute(sql, (keys[0].strip(), keys[1].strip())) else: sql = sql + " from ticket where "+ (" departureDate=%s and " if len(departureDate) > 0 and departureDate != '-1' else "")+" (departureCityName=%s or arrivalCityName=%s) order by departureDate desc,departureDatetime" if len(departureDate) > 0 and departureDate != '-1': cur.execute(sql, (departureDate,searchKey, searchKey)) else: cur.execute(sql, (searchKey, searchKey)) result = cur.fetchall() if len(result) > 0: for item in result: info = Ticket() info.departureCityName = item[0] info.arrivalCityName = item[1] info.departureDate = item[2] info.departureDatetime = item[3] info.departureAirportName = item[4] info.arrivalDate = item[5] info.arrivalDatetime = item[6] info.arrivalAirportName = item[7] info.printprice = item[8] info.airlineName = item[9] info.flightNumber = item[10] datas.append(info) except Exception as e: print(e) finally: con.close() return datas
def addHotSail(searchKey): """ 添加热门航线 :param searchKey: :return: """ con = dbconection.dbConnection() cur = con.cursor() sql = "insert into hot_sail(hot_sail_content) values(%s)" try: result = cur.execute(sql, (searchKey,)) con.commit() except Exception as e: con.rollback() print(e) finally: con.close() return cur.lastrowid
def addSearchRecord(userId, searchKey): """ 添加用户搜索记录 :param userId: :param searchKey: :return: """ con = dbconection.dbConnection() cur = con.cursor() sql = "insert into search_record(userId,searchKey) values(%s,%s)" try: result = cur.execute(sql, (userId, searchKey)) con.commit() except Exception as e: con.rollback() print(e) finally: con.close() return cur.lastrowid
def addUser(telephone, username, password): """ 注册用户 :param telephone: :param username: :param password: :return: """ con = dbconection.dbConnection() cur = con.cursor() sql = "insert into user(telephone,username,password) values(%s,%s,%s)" try: result = cur.execute(sql, (telephone, username, password)) con.commit() except Exception as e: con.rollback() print(e) finally: con.close() return cur.lastrowid
def findDepartureDate(): """ 查询出发日期 :param userId: :return: """ con = dbconection.dbConnection() cur = con.cursor() datas = [] try: sql = "SELECT departureDate FROM ticket GROUP BY departureDate ORDER BY departureDate desc" cur.execute(sql) result = cur.fetchall() if len(result) > 0: for item in result: datas.append({"departureDate": item[0]}) except Exception as e: print(e) finally: con.close() return datas
def findSearchRecrods(userId): """ 查询用户搜索历史记录 :param userId: :return: """ con = dbconection.dbConnection() cur = con.cursor() datas = [] try: sql = "SELECT DISTINCT searchKey FROM search_record WHERE userId=%s ORDER BY id DESC LIMIT 20" cur.execute(sql, (userId,)) result = cur.fetchall() if len(result) > 0: for item in result: info = SearchRecord() info.searchKey = item[0] datas.append(info) except Exception as e: print(e) finally: con.close() return datas