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