def getPlaceReview(): db = pymysql.connect(host="localhost", user="******", password="******", db="selectrip") cursor = db.cursor() try: sql = "SELECT storename, COUNT(*) AS reviewCount, SUM(rating)/COUNT(*) AS rating FROM userreview GROUP BY storename" cursor.execute(sql) review = gen.dataToJson(cursor.description, cursor.fetchall()) cursor.close() except Exception as e: print(e) return review
def getUserReview(): storeName = request.form['stName'] db = pymysql.connect(host="localhost", user="******", password="******", db="selectrip") cursor = db.cursor() try: sql = "SELECT * FROM userreview WHERE storename LIKE (%s);" cursor.execute(sql, storeName) review = gen.dataToJson(cursor.description, cursor.fetchall()) cursor.close() except Exception as e: print(e) return review
def toMain(): db = pymysql.connect(host="localhost", user="******", password="******", db="selectrip") cursor = db.cursor() try: sql = "SELECT * FROM maincity " cursor.execute(sql) data = gen.dataToJson(cursor.description, cursor.fetchall()) print(data) cursor.close() except Exception as e: print(e) return data
def sendImage(): placeName = request.form['placeName'] db = pymysql.connect(host="localhost", user="******", password="******", db="selectrip") cursor = db.cursor() p = 'p' sql = 'SELECT NAME, image FROM place where name LIKE (%s) UNION SELECT storename AS name, image FROM userreview WHERE storename LIKE (%s) AND image NOT LIKE(%s);' try: cursor.execute(sql, (placeName, placeName, p)) imgList = gen.dataToJson(cursor.description, cursor.fetchall()) cursor.close() except Exception as e: print(e) return imgList
def getAllPlace(): word = request.form['word'] print('받아온 단어', word) db = pymysql.connect(host="localhost", user="******", password="******", db="selectrip") cursor = db.cursor() res = ' ' try: wort = '%' + word + '%' sql = 'SELECT * FROM place where NAME LIKE(%s) OR TYPE LIKE(%s)' cursor.execute(sql, (wort, wort)) res = gen.dataToJson(cursor.description, cursor.fetchall()) cursor.close() print(res) except Exception as e: print(e) return res
def getPlaceInfo(): if request.method == 'POST': city = request.form['city'] tp = request.form['type'] print("받은 도시명? " + city + "\n받은 타입명? " + tp) cursor = pymysql.connect( host="localhost", user="******", password="******", db="selectrip").cursor() #Connection에서 Cursor생성 if (tp == '핫플레이스'): sql = "SELECT * FROM selectrip.PLACE WHERE CITYNAME LIKE(%s) AND TYPE IN(%s)" cursor.execute(sql, (city, tp)) elif (tp == 'all'): sql = "SELECT * FROM PLACE;" cursor.execute(sql) else: sql = "SELECT * FROM selectrip.PLACE WHERE CITYNAME LIKE(%s) AND TYPE IN('레스토랑','술집','카페')" cursor.execute(sql, (city)) data = gen.dataToJson(cursor.description, cursor.fetchall()) cursor.close() return data
def getMyPage(): userid = request.form['userid'] content_type = request.form['type'] print("아이디는", userid, "타입은", content_type) db = pymysql.connect(host="localhost", user="******", password="******", db="selectrip") cursor = db.cursor() res = 0 try: if content_type == 'bookmark': sql = 'SELECT p.ID,p.NAME,p.TYPE,p.ADDRESS,p.tel,p.lat,p.lng,p.image,p.cityname,p.hour FROM place p, bookmark b WHERE p.NAME = b.place and b.userId LIKE(%s);' cursor.execute(sql, userid) else: sql = 'SELECT * FROM userreview WHERE userid LIKE(%s);' cursor.execute(sql, userid) res = gen.dataToJson(cursor.description, cursor.fetchall()) cursor.close() print(res) except Exception as e: print(e) return res