def getOneMainData():
result = {}
db.connect()
date_now = time.strftime("%Y-%m-%d", time.localtime())
one = ONE()
one.get_main_page(date_now)
sql = "select count(*) from spider_one where spider_index = '%s' and spider_date = '%s'" % (
one.MAIN_INDEX, date_now)
logger.info(sql)
db_result = db.executeSql(sql)
logger.info("查询数据库结果:")
logger.info(db_result)
if 0 == db_result[0][0]:
yesterday = datetime.date.today() - datetime.timedelta(days=1)
date_yesterday = yesterday.strftime("%Y-%m-%d")
logger.info("昨天日期:" + date_yesterday)
sql = "select spider_result from spider_one where spider_index = '%s' and spider_date = '%s';" % (
one.MAIN_INDEX, date_yesterday)
logger.info(sql)
db_result = db.executeSql(sql)
result = db_result[0][0]
else:
sql = "select spider_result from spider_one where spider_index = '%s' and spider_date = '%s';" % (
one.MAIN_INDEX, date_now)
logger.info(sql)
db_result = db.executeSql(sql)
result = db_result[0][0]
db.close()
return result
def setUpClass(cls):
# configurations
app.config["TESTING"] = True
app.config["DEBUG"] = False
app.config["SECRET_KEY"] = "JEJ78623YBEEHKUK21"
db.disconnect()
db.connect("test", host="mongomock://localhost")
def create_tables():
"""
Create all DB tables
"""
try:
db.connect()
except OperationalError:
pass
models = (Organisation, User, UserOrg)
db.create_tables(models)
def onlyone_login():
result = {}
data = json.loads(request.data.decode('utf-8'))
logger.info(data['code'])
db.connect()
queryString = "appid=%s&secret=%s&js_code=%s&grant_type=authorization_code" % (
app.config['APP_ID'], app.config['APP_SECRETKEY'], data['code'])
requestUrl = app.config['WEIXIN_LOGIN_URL'] + "?" + queryString
logger.info("访问url:" + requestUrl)
response = requests.get(requestUrl)
if 200 != response.status_code:
result['status_code'] = "1001"
result['status_msg'] = "访问ONE网页失败,code:[%d]" % response.status_code
else:
result['status_code'] = "0000"
jsonObject = json.loads(response.text)
openid = jsonObject['openid']
session_key = jsonObject['session_key']
logger.info("session_key:" + jsonObject['session_key'])
logger.info("openid:" + jsonObject['openid'])
# 根据session_key生成token
token = generateToken(jsonObject['session_key'])
# 保存token
sql = "select count(*) from weixin_session where openid = '%s';" % (
openid)
logger.info(sql)
db_result = db.executeSql(sql)
# 数据库结果
if 0 == db_result[0][0]:
sql = "insert into weixin_session values ('%s', '%s');" % (
openid, session_key)
logger.info(sql)
db.executeSql(sql)
else:
# 更新数据库结果
sql = "update weixin_session set session_key = '%s' where openid = '%s';" % (
session_key, openid)
logger.info(sql)
db.executeSql(sql)
result['openid'] = openid
result['token'] = token
logger.info("结果:" + json.dumps(result, ensure_ascii=True))
db.close()
return json.dumps(result, ensure_ascii=True)
def onlyone_check():
logger.info("开始验证token...")
result = {}
db.connect()
if not request.headers.has_key('openid'):
result['code'] = "1001"
result['msg'] = "openid not found in headers"
if not request.headers.has_key('token'):
result['code'] = "1001"
result['msg'] = "token not found in headers"
logger.info(request.headers)
openid = request.headers['openid']
token = request.headers['token']
sql = "select count(*) from weixin_session where openid = '%s';" % (openid)
logger.info(sql)
db_result = db.executeSql(sql)
logger.info(db_result)
if 0 == db_result[0][0]:
logger.info("openid在数据库中不存在")
result['code'] = "1002"
result['msg'] = '没有找到该openid的登陆记录'
else:
sql = "select session_key from weixin_session where openid = '%s';" % (
openid)
logger.info(sql)
db_result = db.executeSql(sql)
logger.info(db_result)
session_key = db_result[0][0]
logger.info(session_key)
[result['code'], result['msg']] = checkToken(session_key, token)
logger.info("code:" + result['code'])
logger.info("msg:" + result['msg'])
db.close()
return json.dumps(result, ensure_ascii=True)
def restaurant():
# establish a db connection
conn = db.connect()
cur = conn.cursor()
# handle requests when the user does not provide url query parameters by returning all the restaurants in the db
if not request.args:
cur.execute('''SELECT * FROM restaurants;''')
rv = cur.fetchall()
rows = fetchRows(
cur, rv
) #rows [{"restaurant_name": "name", "restaurant_address": "address"}, ]
return jsonify(rows)
# else, limit the return values to the users' query parameters
location = request.args['location']
mealType = request.args['mealType']
first_venue = findARestaurant(mealType, location)
if first_venue:
venue_name = first_venue[0]['Restaurant Name']
venue_id = first_venue[0]['venue id']
venue_address = first_venue[0]['Restaurant Address']
venue_image = first_venue[0]['Image']
try:
cur.execute("SELECT post_id FROM restaurants WHERE venue_id=%s",
(venue_id, ))
rv = cur.fetchone()
if not rv:
raise NoQueryResultException
except NoQueryResultException as noMatch:
cur.execute(
"INSERT INTO restaurants (restaurant_name, restaurant_address, image, venue_id) "
"VALUES (%s, %s, %s, %s)",
(venue_name, venue_address, venue_image, venue_id))
conn.commit()
finally:
cur.execute("SELECT post_id FROM restaurants WHERE venue_id=%s",
(venue_id, ))
rv = cur.fetchone()
first_venue[0]['entry id'] = rv[0]
conn.close()
return jsonify(first_venue)
else:
# if a venue could not be found
return 'We couldn\'t find anything there!'
def getRestaurantWithId(id):
method = request.method
conn = db.connect()
cur = conn.cursor()
try:
cur.execute('''SELECT * FROM restaurants WHERE post_id=%s''', (id, ))
rv = cur.fetchmany(
1
) # fetchone() may not function well with a helper function fetchRows; use fetchmany(1) instead
print("retrun value fgro db", rv)
if not rv:
raise NoQueryResultException
else:
rows = fetchRows(cur, rv)
if method == 'DELETE':
cur.execute('''DELETE FROM restaurants WHERE post_id=%s''', (id, ))
conn.commit()
return "Deleted entry {id:0>5d}!".format(id=id)
if method == 'PUT':
# set names to the request args or else the name already in the db
args = {}
for key, ref in zip(
['name', 'location', 'image'],
['restaurant_name', 'restaurant_address', 'image']):
args[key] = request.args.get(key)
if not args[key]:
args[key] = rows[0][ref]
cur.execute(
'''UPDATE restaurants SET restaurant_name=%s, restaurant_address=%s, image=%s WHERE post_id=%s''',
(args['name'], args['location'], args['image'], id))
conn.commit()
return re.sub(
r'[\[\]\']', '',
'''Updated entry as Restaurant Name to {name:}'''.format(
**request.args))
except NoQueryResultException as noMatch:
return noMatch.msg
finally:
conn.close()
return jsonify(rows)
def get_all_reservations(cls):
reservation_list = set()
cur = db.connect().cursor()
cur.execute(
"SELECT DISTINCT reservation_id, start_date, end_date, user_name, is_cancelled, card_number, total_cost FROM Reservation WHERE is_cancelled='0';")
reservation_instance = cur.fetchall()
for val in reservation_instance:
reservation_id = val[0]
start_date = val[1]
end_date = val[2]
user_name = val[3]
hotel_loc = val[4]
is_cancelled = val[5]
card_num = val[6]
total_cost = val[7]
reservation_list.add(
Reservation(reservation_id, user_name, hotel_loc, start_date, end_date, is_cancelled,
card_num, total_cost))
cur.close()
return reservation_list
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from peewee import *
from application import Uzanto, Parto, UzantoVidPunkto, db
db.connect()
Uzanto.update(mono=Uzanto.mono + 7).where(Uzanto.id != 1).execute()
partoj = Parto.select().where((Parto.minajxo > 0) & (Parto.uzanto != 1))
for parto in partoj:
if parto.materialo == 'argxento':
Uzanto.update(mono=Uzanto.mono + parto.nivelo).where(
Uzanto.id == parto.uzanto.id).execute()
elif parto.materialo == 'oro':
Uzanto.update(oro=Uzanto.oro + parto.nivelo).where(
Uzanto.id == parto.uzanto.id).execute()
parto.minajxo -= parto.nivelo
if parto.minajxo < 0:
parto.minajxo = 0
parto.save()
Uzanto.update(oro=40).where(Uzanto.oro > 40).execute()
db.close()
def novel_bookshelf():
'''
书架模块 包括书架查询 书架新增
区别参数:
method: 'SELECT' --查询
'INSERT' --新增
openid
novelInfo
'''
result = {}
db.connect()
logger.info("书架模块...")
logger.info("请求数据:" + request.data.decode("utf-8"))
request_data = json.loads(request.data.decode('utf-8'))
openid = request_data['openid']
logger.info("openid:" + openid)
if 0 == len(openid):
db.close()
return createResultString("1001", "请求数据中没有获取到openid信息")
method = request_data['method']
logger.info("method:" + method)
if 0 == len(method):
db.close()
return createResultString("1001", "请求数据中没有找到method信息")
elif str.upper(method) not in ['SELECT', 'INSERT']:
db.close()
return createResultString("1001", "METHOD字段[%s]不合法" % (method))
novelInfo = request_data['novelInfo']
logger.info("小说信息:" + str(novelInfo))
if 0 == len(novelInfo):
db.close()
return createResultString("1001", "请求数据中没有找到novelInfo信息")
if 'novelUrl' not in novelInfo.keys():
db.close()
return createResultString("1001", "novelInfo中没有novelUrl")
logger.info(str.upper(method))
logger.info("SELECT" == str.upper(method))
if "SELECT" == str.upper(method):
logger.info("查询用户的书架信息...")
sql = "select count(*) from novel_bookshelf where openid = '%s' and novel_url = '%s'" % (
openid, novelInfo['novelUrl'])
logger.info("查询书架SQL:" + sql)
db_result = db.executeSql(sql)
logger.info("数据库查询结果:" + str(db_result))
if 0 == db_result[0][0]:
db.close()
return createResultString(
"1000", "用户[%s]书架上没有[%s]信息!" % (openid, novelInfo['novelUrl']))
else:
db.close()
return createResultString(
"0000",
"该用户[%s]书架上已有[%s]的信息" % (openid, novelInfo['novelUrl']))
elif "INSERT" == str.upper(method):
logger.info("加入书架操作...")
# 首先需要查询是否在书架中，如果不在再进行插入操作
sql = "select count(*) from novel_bookshelf where openid = '%s' and novel_url = '%s'" % (
openid, novelInfo['novelUrl'])
logger.info("查询书架SQL:" + str(sql))
db_result = db.executeSql(sql)
logger.info("数据库查询结果:" + str(db_result))
if 1 == db_result[0][0]:
logger.info("该小说[%s]已在书架中" % (novelInfo['novelUrl']))
db.close()
return createResultString("1111",
"该小说[%s]已在书架中" % (novelInfo['novelUrl']))
else:
sql = "INSERT INTO NOVEL_BOOKSHELF(openid, novel_name, novel_url, novel_author, novel_image, novel_brief) \
VALUES('%s', '%s', '%s', '%s', '%s', '%s')" % \
(pymysql.escape_string(openid), \
pymysql.escape_string(novelInfo['novelName']), \
pymysql.escape_string(novelInfo['novelUrl']), \
pymysql.escape_string(novelInfo['novelAuthor']), \
pymysql.escape_string(novelInfo['novelImage']), \
pymysql.escape_string(novelInfo['novelBrief']))
logger.info("执行SQL语句:")
logger.info(sql)
db.executeSql(sql)
db.close()
return createResultString("0000", "加入书架成功.")
def novel_current_chapter():
'''
当前小说阅读章节模块
包括查询、更新、插入的功能
method:
UPDATE --更新，如果没有数据就插入
SELECT --查询
'''
db.connect()
logger.info("书架模块...")
logger.info("请求数据:" + request.data.decode("utf-8"))
request_data = json.loads(request.data.decode('utf-8'))
# 检查请求的必要数据
check_result = checkRequestData(request_data,
['openid', 'method', 'chapterInfo'])
if "TRUE" != check_result:
db.close()
return check_result
method = request_data['method']
openid = request_data['openid']
chapterInfo = request_data['chapterInfo']
logger.info("请求方法:" + method)
logger.info("OPENID:" + openid)
logger.info("小说信息:" + str(chapterInfo))
if "SELECT" == str.upper(method):
# 查询用户当前小说的阅读章节
check_result = checkRequestData(chapterInfo, ['novelInfo'])
if "TRUE" != check_result:
db.close()
return check_result
novelInfo = chapterInfo['novelInfo']
sql = "select count(*) from novel_current_chapter where openid = '%s' and novel_url = '%s'" % ( \
openid, novelInfo['novelUrl'] \
)
logger.info(sql)
db_result = db.executeSql(sql)
logger.info("查询当前小说阅读章节结果:" + str(db_result))
if 1 != db_result[0][0]:
logger.info("该用户[%s]没有该小说的阅读记录" % (openid))
db.close()
return createResultString("1001", "该用户[%s]没有该小说的阅读记录" % (openid))
else:
sql = "select chapter_id from novel_current_chapter where openid = '%s' and novel_url = '%s'" % ( \
openid, novelInfo['novelUrl'] \
)
logger.info(sql)
db_result = db.executeSql(sql)
logger.info("查询阅读章节ID结果:" + str(db_result))
chapterId = db_result[0][0]
result = {}
result['code'] = "0000"
result["message"] = "查询成功"
result["chapterId"] = chapterId
db.close()
return json.dumps(result, indent=4, ensure_ascii=False)
elif "UPDATE" == str.upper(method):
# 查询用户当前小说的阅读章节
check_result = checkRequestData(chapterInfo, ['novelInfo'])
if "TRUE" != check_result:
db.close()
return check_result
novelInfo = chapterInfo['novelInfo']
sql = "select count(*) from novel_current_chapter where openid = '%s' and novel_url = '%s'" % ( \
openid, novelInfo['novelUrl'] \
)
logger.info(sql)
db_result = db.executeSql(sql)
logger.info("查询当前小说阅读章节结果:" + str(db_result))
if 1 != db_result[0][0]:
# 插入当前阅读进度
logger.info("插入当前阅读进度...")
sql = "insert into novel_current_chapter(openid, novel_name, novel_url, novel_author, chapter_id, chapter_title, chapter_url, novel_image) values \
('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % ( \
pymysql.escape_string(openid), \
pymysql.escape_string(novelInfo['novelName']), \
pymysql.escape_string(novelInfo['novelUrl']), \
pymysql.escape_string(novelInfo['novelAuthor']), \
str(chapterInfo['id']), \
pymysql.escape_string(chapterInfo['title']), \
pymysql.escape_string(chapterInfo['url']), \
pymysql.escape_string(novelInfo['novelImage']) \
)
logger.info(sql)
db_result = db.executeSql(sql)
logger.info("插入当前阅读进度结果:" + str(db_result))
db.close()
return createResultString("0000", "插入当前阅读进度成功...")
else:
# 更新阅读进度
logger.info("更新当前阅读进度...")
sql = "update novel_current_chapter set chapter_id = '%s', chapter_title = '%s', chapter_url = '%s' \
where openid = '%s' and novel_url = '%s'" % ( \
str(chapterInfo['id']), \
pymysql.escape_string(chapterInfo['title']), \
pymysql.escape_string(chapterInfo['url']), \
pymysql.escape_string(openid), \
pymysql.escape_string(novelInfo['novelUrl']) \
)
logger.info(sql)
db_result = db.executeSql(sql)
logger.info("更新当前阅读进度结果:" + str(db_result))
db.close()
return createResultString("0000", "更新当前阅读进度成功...")
else:
return createResultString("9999", "错误的METHOD请求[%s]" % (method))
def before_request():
try:
db.connect()
except OperationalError:
pass