def createWXcode(venueID): WXcodeUrl = '' data = {"scene": venueID} data = json.dumps(data).encode(encoding='utf-8') # header_dict = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko', # "Content-Type": "application/json"} db = get_db() results = db.run("match (n:CONFIG)" "return n.ACCESS_TOKEN as ACCESS_TOKEN") ACCESS_TOKEN = results.data()[0]["ACCESS_TOKEN"] url = 'https://api.weixin.qq.com/wxa/getwxacodeunlimit?access_token=' + ACCESS_TOKEN res = requests.post(url=url, data=data) # headers=header_dict # print(res.text) if "42001" in res.text: print("token过期了") getTokenurl = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=" + app.config[ 'WECHAT']["APPID"] + "&secret=" + app.config['WECHAT']["APPKEY"] res = requests.get(url=getTokenurl) # headers=header_dict jsondata = json.loads(res.text) db.run("match (n:CONFIG)" f"set n.ACCESS_TOKEN='{jsondata['access_token']}'") url = 'https://api.weixin.qq.com/wxa/getwxacodeunlimit?access_token=' + jsondata[ 'access_token'] res = requests.post(url=url, data=data) try: imgdata = res.content save_dir = app.root_path + app.config["UPLOAD"]["wxcode_prefix_path"] if not os.path.exists(save_dir): os.mkdir(save_dir) os.chmod(save_dir, stat.S_IRWXU | stat.S_IRGRP | stat.S_IRWXO) filename = "%s.jpeg" % venueID file = open(os.path.join(save_dir, filename), 'wb') file.write(imgdata) # 将二维码图片传至统一的文件服务器上 用于分布式环境 # transport = paramiko.Transport((app.config.get("FILESERVER")["ip"],app.config.get("FILESERVER")["port"])) # 获取Transport实例 # transport.connect(username=app.config.get("FILESERVER")["username"], password=app.config.get("FILESERVER")["password"]) # 建立s连接 # # 创建sftp对象,SFTPClient是定义怎么传输文件、怎么交互文件 # sftp = paramiko.SFTPClient.from_transport(transport) # # 将本地 api.py 上传至服务器 。文件上传并重命名为 # sftp.put(os.path.join(save_dir, filename),app.config.get("FILESERVER")["path"]+filename) # # 关闭连接 # transport.close() file.close() WXcodeUrl = app.config["UPLOAD"]["wxcode_prefix_url"] + filename except Exception as e: print("图片异常了" + str(e)) WXcodeUrl = None return WXcodeUrl
def general_field_options(field: str, value: str, num: int = 3): db = get_db() rows = db.execute( F"SELECT DISTINCT {field} FROM movie WHERE {field} != ? ORDER BY RANDOM()", (value, )).fetchall() options = [rows[i][field] for i in range(num)] return options
def updateWhiteList(adminno, tag, id, whitelist): db = get_db() if adminno == "": results = db.run( "MATCH (venue:VENUE { id : $id}) with venue " "FOREACH ( " "no IN $whitelist | " "MERGE (user:USER{no:no}) " "CREATE (user)-[r:PERMISSION{id:$tagID,tag:$tagName,active:$tagActive}]->(venue) ) ", { "tagID": tag['id'], "tagName": tag['tag'], "tagActive": tag['active'], "id": id, "whitelist": whitelist }) else: results = db.run( "MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE{id:$id}) with venue " "FOREACH ( " "no IN $whitelist | " "MERGE (user:USER{no:no}) " "CREATE (user)-[r:PERMISSION{id:$tagID,tag:$tagName,active:$tagActive}]->(venue) ) ", { "adminno": adminno, "tagID": tag['id'], "tagName": tag['tag'], "tagActive": tag['active'], "id": id, "whitelist": whitelist })
def update(adminno, id, name, lon, lat, permissionType): db = get_db() if adminno == "": #超级管理员 results = db.run( "MATCH (venue:VENUE { id : $id}) " "SET venue.name = $name,venue.lon = $lon,venue.lat = $lat,venue.permissionType = $permissionType", { "id": id, "name": name, "lon": lon, "lat": lat, "permissionType": permissionType }) else: results = db.run( "MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE{ id : $id}) " "SET venue.name = $name,venue.lon = $lon,venue.lat = $lat,venue.permissionType = $permissionType", { "adminno": adminno, "id": id, "name": name, "lon": lon, "lat": lat, "permissionType": permissionType })
def create_quiz(): """ - create a quiz game if no any game is alive else return the alive quiz game - lock all the unlocked games if new game is being created - DO NOT CREATE THE GAME IF LESS THAN 10 MOVIES SCRAPPED IN DB """ db = get_db() if db.execute( "SELECT COUNT(*) as count FROM movie").fetchone()['count'] < 10: error = "Not enough questions in the database. Game could not be loaded." return render_template('quiz/error.html', error=error) quiz_states = db.execute( "SELECT * FROM quiz_state WHERE user_id = ? AND locked = 0", (g.user['id'], )).fetchall() if quiz_states: if is_game_alive(quiz_states[-1]['created_at']): flash("You have incomplete quiz", category='info') return redirect( url_for('quiz.question', quiz_id=quiz_states[-1]['id'])) else: db.execute( "UPDATE quiz_state SET locked = 1 WHERE user_id = ? AND locked = 0", (g.user['id'], )) cursor = db.execute( "INSERT INTO quiz_state (user_id, created_at) VALUES (?, ?)", (g.user['id'], datetime.datetime.now().strftime( current_app.config['DATETIME_FORMAT']))) db.commit() return redirect(url_for('quiz.question', quiz_id=cursor.lastrowid))
def getAllForIndex(adminno): db = get_db() if adminno == "": results = db.run( "MATCH (venue:VENUE {status:1}) " "RETURN venue.id as id,venue.name as name,venue.lat as lat,venue.lon as lon ORDER BY venue.createtime DESC" ) else: results = db.run( "MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE {status:1}) " "RETURN venue.id as id,venue.name as name,venue.lat as lat,venue.lon as lon ORDER BY venue.createtime DESC", {"adminno": adminno}) if not results: #if user_info.status != 1: return False return [] venueList = [] for record in results: venueList.append({ "id": record['id'], "name": record['name'], "lat": record['lat'], "lon": record['lon'], }) return venueList
def updateToken(no, token): db = get_db() result = db.run("MATCH (user:ADMIN {no:$no}) " "SET user.token=$token", { "no": no, "token": token })
def movie_detail_options(field, *answers: str): db = get_db() placeholders = ", ".join("?" * len(answers)) movie_details = db.execute( F"SELECT DISTINCT value FROM movie_detail WHERE key = ? AND value NOT IN ({placeholders})", (field, *answers)).fetchall() items = [i['value'] for i in movie_details] return randomly_group_items(items, answers, tot_grps=3, max_per_grp=3)
def deleteAdminNo(id, no): db = get_db() results = db.run( "MATCH (user:USER:ADMIN{ no : $no})-[r:MANAGE]->(venue:VENUE { id : $id} ) " "DELETE r", { "no": no, "id": id })
def test_activate_username(client, auth, app): assert client.get('/auth/activate/other').status_code == 200 response = auth.activate_username() assert response.headers['Location'] == 'http://localhost/auth/login' with app.app_context(): assert get_db().execute("select * from user where username = '******'", ).fetchone()['is_activated']
def display_sceneries(): db = get_db() # results = db.run("match (scenery:xmu:signable) return scenery") results = db.run("match (scenery:signable) return scenery") venueList = [] for record in results: tmp = Venue(record['scenery']) # 转化为python中的数据库Venue类 venueList.append(tmp) return venueList
def getSceneryNodeInfo(id): # 获取数据库里的景点结点即可,在User.py中对结点进行提取信息 db = get_db() results = db.run( "MATCH (place:xmu:signable) WHERE place.id=$id RETURN place", {"id": id}) venue_list = [] for record in results: tmp = Venue(record["place"]) #构造python景点对象 # venue_list.append(tmp) return tmp
def getByID(id): db = get_db() results = db.run("MATCH (venue:VENUE {id:$id}) " "RETURN venue", {"id": id}) record = results.single() if not record: #if user_info.status != 1: return False return None venue = Venue(record['venue']) return venue
def test_quiz_question_incorrect_answer(client, auth, app): auth.check_login_required() client.get('/quiz/create') client.get('/quiz/1/question') with app.app_context(): cor_ans_id = \ get_db().execute("SELECT id FROM question_option WHERE question_id = 1 AND is_correct = 0").fetchone()['id'] response = client.post('/quiz/1/question', data={'answer': cor_ans_id}) assert b'Wrong answer' in response.data
def getByToken(token): db = get_db() results = db.run("MATCH (user:ADMIN {token:$token}) " "RETURN user", {"token": token}) record = results.single() if not record: #if user_info.status != 1: return False return None admin = Admin(record['user']) return admin
def getByNoWhenLogin(no): db = get_db() results = db.run( "MATCH (user:USER:ADMIN {no:$no,status:1}) " "RETURN user", {"no": no}) record = results.single() # 管理员不存在或status为禁用状态 if not record: return None return ""
def create(id, label_list, name, cloud, score, open_time, must_know, intro_text, intro_audio, intro_video, signable): # print(4, id, label_list, name, cloud, score, open_time, must_know, intro_text, intro_audio, intro_video,signable) try: db = get_db() #label_list = eval(label_list) label_string = ":".join(label_list) # expression = "CREATE(place"+":"+label_string+''' # { # id:$id, # name:$name, # cloud:$cloud, # score:$score, # open_time:$open_time, # must_know:$must_know, # intro_text:$intro_text, # intro_audio:$intro_audio, # intro_video:$intro_video # } # '''+")" # label_list[0]标签干什么用? expression = f"CREATE(scenery:SCENERY:" + label_string + ''' { id:$id, name:$name, cloud:$cloud, score:$score, open_time:$open_time, must_know:$must_know, intro_text:$intro_text, intro_audio:$intro_audio, intro_video:$intro_video, signable:$signable } ''' + ")" property_dict = { "id": id, "name": name, "cloud": cloud, "score": score, "open_time": open_time, "must_know": must_know, "intro_text": intro_text, "intro_audio": intro_audio, "intro_video": intro_video, "signable": signable } results = db.run(expression, property_dict) return 1 # 1代表成功 except Exception as e: print("服务器错误", str(e)) return None
def get(): db = get_db() quiz_state = db.execute( "SELECT * FROM quiz_state WHERE id = ? AND user_id = ?", (quiz_id, g.user['id'])).fetchone() if quiz_state is None: return render_template('404.html'), 404 if quiz_state['locked']: flash('Quiz complete.', category='info') return redirect(url_for('quiz.score', quiz_id=quiz_id)) if not is_game_alive(quiz_state['created_at']): db.execute( "UPDATE quiz_state SET locked = 1 WHERE id = ? AND locked = 0", (quiz_id, )) flash('Quiz expired.', category='warning') return redirect(url_for('quiz.score', quiz_id=quiz_id)) quiz_ques = db.execute( "SELECT * FROM quiz_question WHERE quiz_id = ? AND locked = 0 ORDER BY id DESC", (quiz_id, )).fetchone() now = datetime.datetime.now() if quiz_ques is None: question_id = _generate_random_question(db=db, quiz_id=quiz_id, question_no=1) db.commit() elif datetime.datetime.strptime( quiz_ques['created_at'], current_app.config['DATETIME_FORMAT'] ) + datetime.timedelta( seconds=current_app.config['QUESTION_TIMEOUT_SECONDS']) <= now: if quiz_ques['question_no'] >= 10: return _quiz_complete_action(db) flash('Question #%s expired' % quiz_ques['question_no'], category='warning') db.execute( "UPDATE quiz_question SET locked = 1 WHERE quiz_id = ? AND locked = 0", (quiz_id, )) question_id = _generate_random_question( db=db, quiz_id=quiz_id, question_no=quiz_ques['question_no'] + 1) db.commit() else: question_id = quiz_ques['id'] context = _get_context(question_id) return render_template('quiz/question.html', **context), 200
def updateAdminList(id, adminlist): db = get_db() results = db.run( "MATCH (venue:VENUE { id : $id}) with venue " "FOREACH ( " "no IN $adminlist | " "MERGE (user:USER{no:no}) " "CREATE (user)-[r:MANAGE]->(venue) " "SET user:ADMIN,user.status=1 )", { "id": id, "adminlist": adminlist })
def checkUniqueName(name): db = get_db() results = db.run( "MATCH (venue:VENUE) " "WHERE venue.name = $name " "RETURN venue", {"name": name}) record = results.single() if not record: return True else: return False
def test_register(client, app): assert client.get('/auth/register').status_code == 200 response = client.post('/auth/register', data={ 'username': '******', 'password': '******' }) assert 'http://localhost/auth/activate/a' == response.headers['Location'] with app.app_context(): assert get_db().execute( "select * from user where username = '******'", ).fetchone() is not None
def getMyVenueIdAndName(adminno): db = get_db() results = db.run( "MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE{status:1}) " "RETURN venue.id as id,venue.name as name ORDER BY venue.name DESC", {"adminno": adminno}) venueList = [] for record in results: venueList.append({"id": record['id'], "name": record['name']}) return venueList
def reverseStatus(adminno, id): db = get_db() if adminno == "": results = db.run( "MATCH (venue:VENUE { id : $id}) " "SET venue.status = -venue.status", {"id": id}) else: results = db.run( "MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE{ id : $id}) " "SET venue.status = -venue.status", { "adminno": adminno, "id": id })
def post(): db = get_db() answer = request.form.get('answer', None) quiz_ques = db.execute( "SELECT * FROM quiz_question WHERE quiz_id = ? AND locked = 0 ORDER BY id DESC", (quiz_id, )).fetchone() now = datetime.datetime.now() if quiz_ques is None: return render_404() elif datetime.datetime.strptime( quiz_ques['created_at'], current_app.config['DATETIME_FORMAT'] ) + datetime.timedelta( seconds=current_app.config['QUESTION_TIMEOUT_SECONDS']) <= now: db.execute( "UPDATE quiz_question SET locked = 1 WHERE quiz_id = ? AND locked = 0", (quiz_id, )) flash("Question expired.", category='warning') return redirect(url_for('quiz.question', quiz_id=quiz_id)) else: question_id = quiz_ques['id'] if answer is not None: correct_option = db.execute( "SELECT question_option.id AS option_id FROM quiz_question " "INNER JOIN question_option " "ON quiz_question.id = question_option.question_id " "WHERE quiz_question.id = ? AND is_correct = 1", (question_id, )).fetchone() if str(correct_option['option_id']) == answer: flash("Correct answer", category='success') else: flash("Wrong answer", category='danger') else: flash("Skipped question #%s" % quiz_ques['question_no'], category='warning') db.execute( "UPDATE quiz_question SET user_answer = ?, locked = 1 WHERE id = ?", (answer, question_id)) if quiz_ques['question_no'] >= 10: flash('Quiz complete', category='info') return _quiz_complete_action(db=db) question_id = _generate_random_question( db=db, quiz_id=quiz_id, question_no=quiz_ques['question_no'] + 1) db.commit() context = _get_context(question_id) return render_template('quiz/question.html', **context), 200
def create(id, name, lon, lat, permissionType, createtime): db = get_db() results = db.run( "CREATE (venue:VENUE{ " "id : $id ,name : $name ,lon : $lon ,lat : $lat,status : $status,permissionType : $permissionType,createtime : $createtime}) ", { "id": id, "name": name, "lon": lon, "lat": lat, "status": 1, "permissionType": permissionType, "createtime": createtime })
def on_user_web_access(self, user_id, get_array, post_array): TWITTER_KEY = "1" db = a.get_db() if 'connect' in get_array and get_array['connect'] == 'twitter': consumer = oauth.Consumer(consumer_key, consumer_secret) client = oauth.Client(consumer) resp, content = client.request(request_token_url, "GET") if resp['status'] != '200': raise Exception("Invalid response %s." % resp['status']) request_token = dict(parse.parse_qsl(content.decode())) db.sql("INSERT INTO request_tokens(user_id, token, token_secret) VALUES(%s, '"+request_token['oauth_token']+"', '"+request_token['oauth_token_secret']+"')", (str(user_id),)) db.commit() a.p('<p>Connexion a Twitter requise.</p>') a.p('<a href="'+authorize_url+'?oauth_token='+(request_token['oauth_token'])+'">Continuer sur Twitter</a>') elif 'twitter' in get_array and get_array["twitter"] == TWITTER_KEY: consumer = oauth.Consumer(consumer_key, consumer_secret) client = oauth.Client(consumer) oauth_token = get_array['oauth_token'] oauth_verifier = get_array['oauth_verifier'] request_token = db.sql('SELECT * FROM request_tokens WHERE user_id=%s', (str(user_id),))[0] token = oauth.Token(request_token[1], request_token[2]) token.set_verifier(oauth_verifier) client = oauth.Client(consumer, token) resp, content = client.request(access_token_url, "POST") if resp['status'] != '200': raise Exception("Invalid response %s." % resp['status']) access_token = dict(parse.parse_qsl(content.decode())) db.sql("INSERT INTO usr(usr_id, usr_token, usr_token_secret) VALUES(%s, '"+access_token['oauth_token']+"', '"+access_token['oauth_token_secret']+"')", (str(user_id),)) db.sql('DELETE FROM request_tokens WHERE user_id = %s', (str(user_id),)) db.commit() a.p('<p>Twitter pairing successful</p>') else: a.p('<a href="'+a.get_url()+'&connect=twitter">Connect with Twitter</a>')
def getAll(): db = get_db() results = db.run("MATCH (venue:VENUE {status:1}) " "RETURN venue.id as id,venue.name as name ") if not results: #if user_info.status != 1: return False return [] venueList = [] for record in results: venueList.append({ "id": record['id'], "name": record['name'], }) return venueList
def test_quiz_create_empty(client, auth, app): auth.check_login_required() with app.app_context(): get_db().execute("DELETE FROM movie_detail") get_db().execute("DELETE FROM movie") get_db().commit() response = client.get('/quiz/create') assert b'Not enough questions in the database. Game could not be loaded.' in response.data
def getVenueIdAndName(): db = get_db() # results = db.run("MATCH (venue:VENUE{status:1}) " # "RETURN venue.id as id,venue.name as name ORDER BY venue.name DESC" # ) results = db.run( "MATCH (venue:VENUE{status:1}) WHERE venue.name =~ '.*翔安.*' " "RETURN venue.id as id,venue.name as name ORDER BY venue.name DESC" ) venueList = [] for record in results: venueList.append({"id": record['id'], "name": record['name']}) return venueList
def delete_scenery_node(id): # db = get_db() # expression = "match (place:xmu:signable {id:$id}) delete place " # try: # db.run(expression,{"id":id}) # return "1" # except Exception as e: # return None # print(id) db = get_db() # expression = "match (place:xmu:signable {id:$id}) delete place " expression = "match (scenery:signable {id:$id}) delete scenery " try: db.run(expression, {"id": id}) return 1 except Exception as e: return None
def on_SMS(self, command, data): db = a.get_db() admin_token = db.sql("SELECT admin_token FROM admin WHERE admin_id = 1;") admin_token_secret = db.sql("SELECT admin_token_secret FROM admin WHERE admin_id =1;") if admin_token ==[] or admin_token_secret == [] : raise Exception("Error, admin not registered") auth = t.OAuthHandler(admin_token[0][0], admin_token_secret[0][0]) if command == "twittbc": usr_token = db.sql("SELECT usr_token FROM usr WHERE usr_id ="+ str(PROXYBC_UNIQUE_ID) + ";") usr_token_secret = db.sql("SELECT usr_token_secret FROM usr WHERE usr_id ="+ str(PROXYBC_UNIQUE_ID) + ";") if usr_token ==[] or usr_token_secret == [] : a.send_message(data['sender'], "ERROR.Sorry try again later.") raise Exception("Error, @ProxyBc not registered") exit() auth.set_access_token(usr_token[0][0], usr_token_secret[0][0]) api = t.API(auth) api.update_status(status=data['message']) a.send_message(data['sender'], "Tweet publié sur @ProxyBc !") exit() usr_token = db.sql("SELECT usr_token FROM usr WHERE usr_id ="+ str(data["sender"]) + ";") usr_token_secret = db.sql("SELECT usr_token_secret FROM usr WHERE usr_id ="+ str(data["sender"]) + ";") print('app21', str(data["sender"]), usr_token, usr_token_secret) if usr_token ==[] or usr_token_secret == [] : a.send_message(data['sender'], "Votre compte twitter n'est pas lié a Bobcat ! @see "+ a.get_url() + ". Utilisez 'Twittbc message' pour publier sur @ProxyBC ") exit() auth.set_access_token(usr_token[0][0], usr_token_secret[0][0]) api = t.API(auth) if command == "twittlt": a.send_message(data['sender'],(api.user_timeline(id=api.get_user(data['message']).id, count = 1)[0].text)) quit() if command == "twitter" : api.update_status(data["message"]) a.send_message(data["sender"], "Tweet publié !") quit()
def on_install(self): db = a.get_db() db.sql("CREATE TABLE usr(usr_id int primary key,usr_token text, usr_token_secret text);") db.sql("CREATE TABLE admin(admin_id int primary key,admin_token text, admin_token_secret text);") db.sql("create table request_tokens(user_id integer, token text, token_secret text)"); db.commit()