def create_unique(cls, session, id): # if the site user was cached, manually upgrade it to a local user if SEUser.query.get(id) is not None: session.execute(cls.__table__.insert().values(id=id)) return cls.query.get(id) o = cls(id=id) session.add(o) return o
def get_a4(): data = [] session = cluster.connect('shop_receipts') session.row_factory = dict_factory rows = session.execute(a2_query) for row in rows: data.append(row) return data
def alljokes(): print(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>") rows = session.execute("""SELECT * from jokes.data""") print(rows._current_rows) k=rows._current_rows if rows: return jsonify({'data':k}), 200 return jsonify({'error':'No data present'}), 404
def cassandra(): cluster = Cluster(['cassandra']) session = cluster.connect() session.set_keyspace("people") cql = "SELECT mylist FROM mylist" qwe = session.execute(cql) rows = list(qwe) row_list = list(rows) return jsonify(row_list)
def delete_user_by_id(id): try: id=id.replace("<","") id=id.replace(">","") print(id) session = cassandra.connect() session.set_keyspace("todo") namecql = "SELECT * FROM login where id = "+id snameres = session.execute(namecql) sname = snameres[0].uname cql = "DELETE FROM login where id = "+id print(cql) session.execute(cql) result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message\""+":\"Deleted user'"+ sname +"'\"}"+"],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/get_all_users\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }" result = Response (result, status = 200, mimetype = 'application/json') except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def delete_all_tasks(): try: session = cassandra.connect() session.set_keyspace("todo") tcql = "TRUNCATE TABLE tasks" session.execute(tcql) scql = "TRUNCATE TABLE sub_tasks" session.execute(scql) result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message" + ":\"Deleted all tasks and corresponding sub-tasks successfully!\"}" + "],\n\"links\": [ { \n \"rel\" : \"child\",\n \"href\": \"" + "/dashboard\" \n }],\"host-IP\" :\"" + request.environ.get( 'HTTP_X_REAL_IP', request.remote_addr) + "\" }" result = Response(result, status=200, mimetype='application/json') except Exception as e: result = Response( "{ \n \"success\" : \"false\", \n \"code\": \"" + type(e).__name__ + "\",\n \"message\" : \"" + str(e) + "\"\n}", status=500, mimetype='application/json') return result
def add_accounts_to_db(session, access): client = MondoClient(access.access_token) accounts = client.list_accounts() token = MondoToken( access_token=access.access_token, refresh_token=access.refresh_token, expires_in=datetime.now() + timedelta(seconds=access.expires_in)) for account in accounts: db_account, _ = get_or_create(session, MondoAccount, account_id=account.id, created=account.created, description=account.description) db_account.token = token session.commit() cmd = "DELETE from mondo_tokens t WHERE NOT EXISTS (SELECT token_id FROM mondo_accounts a WHERE t.id = a.token_id);" session.execute(cmd) session.commit()
def add_worker(): try: session = cassandra.connect() session.set_keyspace("todo") name = request.form['name'] role = request.form['role'] idcql = "SELECT MAX(id) as id FROM users " idres = session.execute(idcql) print(idres[0].id) id = idres[0].id + 1 print(id) worker_cql = "INSERT INTO todo.users(id,name,role) VALUES("+str(id)+",'"+nullTostr(name.replace("'","''"))+"','"+nullTostr(role.replace("'","''"))+"');" print(worker_cql) session.execute(worker_cql) result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message"+":\"Employee '" + name + "' with role '"+ role+"' added Successfully!\"}"+"],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/get_all_workers\" \n },{ \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/get_worker_by_id<"+str(id)+"\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }" result = Response (result, status = 200, mimetype = 'application/json') except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def search(id, token): cursor = session.execute(token_query2, {'token': token, 'id': id}) rows = cursor.fetchall() return render_template("token.html", title="Text Token Graphs", id=id, token=token, rows=rows)
def getdeck(): response = requests.get(deck_url_template) if response.ok: parsed = response.json() print(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>",parsed) insert = session.execute("""INSERT INTO deck.data (success, id, shuffled, remainings) VALUES ({}, '{}', {}, {})""".format(parsed['success'], parsed['deck_id'], parsed['shuffled'], parsed['remaining'])) return jsonify(parsed) else: return jsonify(response.reason)
def getjoke(): response = requests.get(jokes_url_template) if response.ok: parsed = response.json() print(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>",parsed) joke = parsed['value']['joke'].replace("'","") insert = session.execute("""INSERT INTO jokes.data (Id, Joke) VALUES ({}, '{}')""".format(parsed['value']['id'], joke)) return jsonify(parsed) else: return jsonify(response.reason)
def get_task_by_id(id): try: id=id.replace("<","") id=id.replace(">","") session = cassandra.connect() session.set_keyspace("todo") print(id) cql = "SELECT * FROM sub_tasks where task_id = '"+str(id)+"' ALLOW FILTERING" r = list (session.execute(cql)) print(len(r)) res_htm = "<h1>Task Details</h1><h2> Sub Tasks to perform : </h2><table><tr style ='border: 1px solid black'><th style ='border: 1px solid #dddddd'>Sequence Number</th><th style ='border: 1px solid #dddddd'>Sub-TaskName</th><th style ='border: 1px solid #dddddd'>Assignee</th><th style ='border: 1px solid #dddddd'>Role</th><th style ='border: 1px solid #dddddd'>Action</th></tr>" for i,row in enumerate(r,0): u_cql = "SELECT * FROM users where id = "+str(row.assignee) u = list (session.execute(u_cql)) res_htm = res_htm +"<tr><td style ='border: 1px solid #dddddd'>"+str(i+1)+"</td><td style ='border: 1px solid #dddddd'><a href =/get_sub_task_by_id<"+str(row.id)+">"+str(row.name)+"</a></td><td style ='border: 1px solid #dddddd'>"+str(u[0].name).capitalize()+"</td><td style ='border: 1px solid #dddddd'>"+str(u[0].role).capitalize()+"</td><td style ='border: 1px solid #dddddd'><a href=\\rest\delete_sub_task_by_id<"+str(row.id)+">Delete</a></td></tr>" res_htm = res_htm+"</ol>" result = res_htm except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def create_user(): if request.json is None: return jsonify({'error': 'missing arguments!'}), 400 username = request.json.get('username') password = request.json.get('password') email = request.json.get('email') name = request.json.get('name') if username is None or password is None: # missing arguments return jsonify({'error': 'missing arguments!'}), 400 prepared_statement = session.prepare("SELECT * FROM CCMiniProject.users WHERE Username = ?;") rows = session.execute(prepared_statement,(username,)) if rows: if rows[0][u'username'] == username: # existing user return jsonify({'error': 'existing user!'}), 400 user = User(username=username, name=name, email=email) user.hash_password(password) rows = session.execute("INSERT INTO CCMiniProject.users (ID,Username,Password_hash, Name, Email, Role) VALUES (%s,%s,%s,%s,%s,%s);", (uuid.uuid4(),user.username, user.password_hash, user.name, user.email, 2)) return jsonify({'username': user.username, 'name': user.name, 'email': user.email}), 201
def get_sub_task_by_id(id): try: id = id.replace("<","") id=id.replace(">","") session = cassandra.connect() session.set_keyspace("todo") cql = "SELECT * FROM sub_tasks where id = "+id r = list (session.execute(cql)) res_htm = "<h1> Sub Task Details : </h1><br><h2>Click on Sub-Task ID for REST call (with HATEOAS implementation) </h2><table><tr style ='border: 1px solid black'><th style ='border: 1px solid #dddddd'>Sub-Task ID</th><th style ='border: 1px solid #dddddd'>Name</th><th style ='border: 1px solid #dddddd'>Description</th><th style ='border: 1px solid #dddddd'>Difficulty</th><th style ='border: 1px solid #dddddd'>Assignee</th><th style ='border: 1px solid #dddddd'>Role</th><th style ='border: 1px solid #dddddd'>References</th><th style ='border: 1px solid #dddddd'>Connected to Task</th></tr>" for i,row in enumerate(r,0): t_cql = "SELECT * FROM tasks where id = "+str(row.task_id) t = list (session.execute(t_cql)) u_cql = "SELECT * FROM users where id = "+str(row.assignee) u = list (session.execute(u_cql)) res_htm = res_htm + "<tr><td style ='border: 1px solid #dddddd'><a href = /rest/get_sub_task_details_by_id<"+str(row.id)+">"+str(row.id)+"</a></td><td style ='border: 1px solid #dddddd'>"+str(row.name).capitalize()+"</td><td style ='border: 1px solid #dddddd'>"+str(row.description)+"</td><td style ='border: 1px solid #dddddd'>"+str(row.difficulty)+"</td><td style ='border: 1px solid #dddddd'>"+str(u[0].name).capitalize()+"</td><td style ='border: 1px solid #dddddd'>"+str(u[0].role).capitalize()+"</td><td style ='border: 1px solid #dddddd'><a href = "+str(row.ref)+">"+str(row.ref)+"</a></td><td style ='border: 1px solid #dddddd'><a href= /get_task_by_id<"+str(row.task_id)+">"+str(t[0].name).capitalize()+"</a></td></tr>" res_htm = res_htm+"</table>" result = res_htm except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def update_user(username): if request.json is None: return jsonify({'error': 'missing arguments!'}), 400 password = request.json.get('password') email = request.json.get('email') name = request.json.get('name') if password is None or email is None or name is None: # missing arguments return jsonify({'error': 'missing arguments!'}), 400 prepared_statement = session.prepare('SELECT ID, Username FROM CCMiniProject.users WHERE Username = ?') rows = session.execute(prepared_statement, (username,)) # if not admin then not allowed to update or create other users if g.user.role == 2: if (rows is None) or (not rows): return jsonify({'error': 'only authorized to update your user'}), 401 else: if rows[0][u'username'] != g.user.username: # existing user return jsonify({'error': 'only authorized to update your user'}), 401 user = User(username=username, name=name, email=email) user.hash_password(password) user.update_id(rows[0][u'id']) prepared_statement = session.prepare("UPDATE CCMiniProject.users SET Password_hash = ?, Name = ?, Email = ? WHERE ID = ?") rows = session.execute(prepared_statement, (user.password_hash, user.name, user.email, user.id)) return jsonify({'username': user.username, 'name': user.name, 'email': user.email}), 200 # if admin then allowed to update and create users else: # if user doesn't exist create new one if (rows is None) or (not rows): user = User(username=username, name=name, email=email) user.hash_password(password) rows = session.execute("INSERT INTO CCMiniProject.users (ID,Username,Password_hash, Name, Email, Role) VALUES (%s,%s,%s,%s,%s,%s)", (uuid.uuid4(),user.username, user.password_hash, user.name, user.email,2)) return jsonify({'username': user.username, 'name': user.name, 'email': user.email}), 201 # if user exists then update else: user = User(username=username, name=name, email=email) user.hash_password(password) user_id = rows[0][u'id'] prepared_statement = session.prepare("UPDATE CCMiniProject.users SET Password_hash = ?, Name = ?, Email = ? WHERE ID = ?") rows = session.execute(prepared_statement, (user.password_hash, user.name, user.email, user_id)) return jsonify({'username': user.username, 'name': user.name, 'email': user.email}), 200
def first_time_load(): session = db.session() #In case personal_data contains no data try: dictionary = session.execute("SELECT personal_data_dictionary FROM personal_data WHERE id=(select max(id) from personal_data)") personal_info_dict = {} dictionary = dictionary.fetchall() session.close() return jsonify(dictionary[0][0]) except: personal_info_dict = data() return personal_info_dict
def delete_task_by_id(id): try: id=id.replace("<","") id=id.replace(">","") print(id) session = cassandra.connect() session.set_keyspace("todo") namecql = "SELECT * FROM tasks where id = "+id tnameres = session.execute(namecql) tname = tnameres[0].name tcql = "DELETE FROM tasks where id = "+id print(tcql) session.execute(tcql) scql = "SELECT * FROM sub_tasks where task_id='"+id+"' ALLOW FILTERING" print(scql) listOfSubTasks = session.execute(scql) for row in listOfSubTasks: deleteSubTaskcql = "DELETE FROM sub_tasks where id="+str(row.id) print(deleteSubTaskcql) session.execute(deleteSubTaskcql) result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message"+":\"Deleted task '"+ tname +"' and corresponding sub-tasks!\"}"+"],\n\"links\": [ { \n \"rel\" : \"child\",\n \"href\": \""+ "/rest/get_all_sub_tasks\" \n },{ \n \"rel\" : \"child\",\n \"href\": \""+ "/rest/get_sub_task_by_id<"+id+"\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }" result = Response (result, status = 200, mimetype = 'application/json') except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def actions_review(stmnt_id): statement = Statement.query.filter_by(id=stmnt_id).first() if statement is None: abort(404) session = db_session() update_query = Statement.__table__.update().\ values(reviewed=(not statement.reviewed)).\ where(Statement.id==stmnt_id) session.execute(update_query) session.commit() session.close() resp = { "status": True, "msg": "OK", "stmnt_id": stmnt_id, "reviewed": not statement.reviewed } return jsonify(**resp)
def login(username, password): salt = '5gz' password = password + salt password = hashlib.md5(password.encode()) password = password.hexdigest() rows = session.execute( """SELECT * FROM user.usernamesandpassword WHERE username='******' AND password='******'""" .format(username, password)) result = rows.all() if (len(result) > 0): return {username: '******'} else: return {username: '******'}
def get_all_users(): try: session = cassandra.connect() session.set_keyspace("todo") cql = "SELECT * FROM login" r = list (session.execute(cql)) print(len(r)) res_htm = "<h1> List of Application users : </h1><table><tr style ='border: 1px solid black'><th style ='border: 1px solid #dddddd'>User ID</th><th style ='border: 1px solid #dddddd'>Name</th><th style ='border: 1px solid #dddddd'>Role</th><th style ='border: 1px solid #dddddd'>Action</th></tr>" for i,row in enumerate(r,0): res_htm = res_htm +"<tr><td style ='border: 1px solid #dddddd'>"+str(row.id)+"</td><td style ='border: 1px solid #dddddd'><a href =/rest/get_user_by_id<"+str(row.id)+">"+str(row.uname).capitalize()+"</a></td><td style ='border: 1px solid #dddddd'>"+str(row.role).capitalize()+"</td><td style ='border: 1px solid #dddddd'><a href=/rest/delete_user_by_id<"+str(row.id)+">Delete</a></td></tr>" res_htm = res_htm+"</ol>" result = res_htm except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def get_tasks(): try: session = cassandra.connect() session.set_keyspace("todo") cql = "SELECT * FROM tasks" r = list (session.execute(cql)) print(len(r)) res_htm = "<h1> Tasks to-do : </h1><ol>" for i,row in enumerate(r,0): res_htm = res_htm +"<li><a href= /get_task_by_id<"+str(row.id)+">"+str(row.name)+"</a> "+" - Click Task ID for rest call to get task details (with HATEOAS) - <a href = /rest/get_task_details_by_id<"+str(row.id)+">"+str(row.id)+"</a>   <a href = /rest/delete_task_by_id<"+str(row.id)+">Delete Task</a></li>" res_htm = res_htm+"</ol>" result = res_htm except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def signup(): try: session = cassandra.connect() session.set_keyspace("todo") error = None if request.method == 'POST': pw_hash = hashing.hash_value(request.form['password'], salt=app.config['SALT']) cql = "INSERT INTO login (id,pwd,uname,role) VALUES(UUID(),'"+pw_hash+"','"+request.form['username']+"','user')" print(cql) r = list (session.execute(cql)) error = "Login Created Successfully!" return redirect(url_for('login')) return render_template('signup.html', error=error) except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def verify_auth_token(token): s = Serializer(app.config['SECRET_KEY']) try: data = s.loads(token) except SignatureExpired: return None # valid token, but expired except BadSignature: return None # invalid token print("that f*****g thing" + data['id']) prepared_statement = session.prepare('SELECT ID ,Username,Role FROM CCMiniProject.users WHERE ID= ?') rows = session.execute(prepared_statement, (uuid.UUID(data['id']),)) if not rows: return None else: user = User(rows[0][u'username'], "", "") user.update_role(rows[0][u'role']) return user
def query1(): author = request.form['query1'] cluster = Cluster(['127.0.0.1']) session = cluster.connect() session.set_keyspace("twitter_key_space") query = session.prepare(""" SELECT * from table_query1 where author = ?""") open_read = session.execute(query, (author, )) page = '' for row in open_read: page += '<p>%s</p>' % repr(row) return page
def get_all_sub_tasks_rest(): result = "" try: session = cassandra.connect() session.set_keyspace("todo") cql = "SELECT * FROM sub_tasks" r = list (session.execute(cql)) res = "{ \"success\": true,\n\"payload\":[" for i,row in enumerate(r,0): if((i+1)==len(r)): res = res + "{\"id\": \""+str(row.id)+"\", \"assignee\": \""+str(row.assignee)+"\", \"description\": \""+str(row.description)+"\", \"difficulty\": \""+str(row.difficulty)+"\", \"name\": \""+str(row.name)+"\", \"ref\": \""+str(row.ref)+"\", \"task_id\": \""+str(row.task_id)+"\"}" else: res = res + "{\"id\": \""+str(row.id)+"\", \"assignee\": \""+str(row.assignee)+"\", \"description\": \""+str(row.description)+"\", \"difficulty\": \""+str(row.difficulty)+"\", \"name\": \""+str(row.name)+"\", \"ref\": \""+str(row.ref)+"\", \"task_id\": \""+str(row.task_id)+"\"}," res = res + "],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \""+ "/get_sub_task_by_id< bae50b56-19a8-4ece-92ae-c5eeb2dc81a2\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }" result = Response (res, status = 200, mimetype = 'application/json') except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def verify_password(username_or_token, password): # verify based on authentication token user = User.verify_auth_token(username_or_token) # if not verified, try to verify using username and password if not user: prepared_statement = session.prepare("SELECT ID,Username,Password_hash,Role FROM CCMiniProject.users WHERE Username = ?;") rows = session.execute(prepared_statement, (username_or_token,)) if not rows: return False else: user = User(rows[0][u'username'],"","") user.update_password_hash(rows[0][u'password_hash']) user.update_role(rows[0][u'role']) user.update_id(str(rows[0][u'id'])) if not user.verify_password(password): return False g.user = user return True
def get_all_users_rest(): result = "" try: session = cassandra.connect() session.set_keyspace("todo") cql = "SELECT * FROM login" r = list (session.execute(cql)) res = "{ \"success\": true,\n\"payload\":[" for i,row in enumerate(r,0): if((i+1)==len(r)): res = res + "{\"id\": \""+str(row.id)+"\", \"name\": \""+str(row.uname)+"\", \"role\": \""+str(row.role)+"\"\n}" else: res = res + "{\"id\": \""+str(row.id)+"\", \"name\": \""+str(row.uname)+"\", \"role\": \""+str(row.role)+"\"}," res = res + "],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/get_user_by_id< "+str(row.id)+"\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\"}" result = Response (res, status = 200, mimetype = 'application/json') except Exception as e: result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json') return result
def get_task_details_by_id_rest(id): id = id.replace("<", "") id = id.replace(">", "") result = "" try: session = cassandra.connect() session.set_keyspace("todo") cql = "SELECT * FROM tasks where id = " + id r = list(session.execute(cql)) res = "{ \"success\": true,\n\"payload\":[" for i, row in enumerate(r, 0): if ((i + 1) == len(r)): res = res + "{\"id\": \"" + str( row.id) + "\", \"assignee\": \"" + str( row.assignee) + "\", \"priority\": \"" + str( row.priority) + "\", \"description\": \"" + str( row.description ) + "\", \"difficulty\": \"" + str( row.difficulty) + "\", \"name\": \"" + str( row.name) + "\", \"start\": \"" + str( row.start) + "\", \"end\": \"" + str( row.end) + "\"}" else: res = res + "{\"id\": \"" + str( row.id) + "\", \"assignee\": \"" + str( row.assignee) + "\", \"priority\": \"" + str( row.priority) + "\", \"description\": \"" + str( row.description ) + "\", \"difficulty\": \"" + str( row.difficulty) + "\", \"name\": \"" + str( row.name) + "\", \"start\": \"" + str( row.start) + "\", \"end\": \"" + str( row.end) + "\"}," res = res + "],\n\"links\": [ { \n \"rel\" : \"child\",\n \"href\": \"" + "/rest/get_all_sub_tasks\" \n },{ \n \"rel\" : \"child\",\n \"href\": \"" + "/rest/get_sub_task_by_id<" + id + "\" \n },{ \n \"rel\" : \"self\",\n \"href\": \"" + "/rest/delete_task_by_id<" + id + "\" \n }],\"host-IP\" :\"" + request.environ.get( 'HTTP_X_REAL_IP', request.remote_addr) + "\" }" result = Response(res, status=200, mimetype='application/json') except Exception as e: result = Response( "{ \n \"success\" : \"false\", \n \"code\": \"" + type(e).__name__ + "\",\n \"message\" : \"" + str(e) + "\"\n}", status=500, mimetype='application/json') return result
def query3(): hashtag = request.form['query3'] print(hashtag) cluster = Cluster(['127.0.0.1']) session = cluster.connect() session.set_keyspace("twitter_key_space") query = session.prepare( """ SELECT * from table_query3 where hashtgs = ?""") open_read = session.execute(query, (hashtag, )) page = '' for row in open_read: page += '<p>%s</p>' % repr(row) return page
def query6(): date = request.form['query6'] cluster = Cluster(['127.0.0.1']) session = cluster.connect() session.set_keyspace("midsem") query = session.prepare(""" SELECT * from table_query8 where date = ?""") open_read = session.execute(query, (date, )) page = " <style> table, th, td { border: 1px solid black; } </style>" page = page + "<table style = \"width:50%\"> <tr> <th> HASHTAG </th> <th> DATE </th> </tr> " for row in open_read: page = page + "<tr> " page += "<td> %s </td>" % repr(row.date) page += "<td> %s </td>" % repr(row.hashtag_mention) page += "</tr>" page += "</table>" return page
def dates_completed_goals(): now = dt.datetime.now() rescue_time = RescueTime() toggl = Toggl().get_daily_pomodoros() meditation = Meditation() total_pomodoros = sum(toggl.values()) #Check that we are almost done the day, we use two minutes in case the url #doesn't get pinged during the 59th minute for whatever reason if now.hour == 23 and now.minute == 59 and now.second <= 30: if meditation.get_current_meditation_time() >= MEDITATION_GOAL and \ total_pomodoros >= FOCUS_GOAL and \ rescue_time.get_current_days_data()["unproductive_hours"] < UNPRODUCTIVITY_GOAL: now = time.time() todayDict = {str(now): 100} try: goalCompletion = GoalCompletion(date=todayDict) db.session.add(goalCompletion) db.session.commit() except Exception as e: print(e) print("Unable to add items to database") try: session = db.session() rows = session.execute("SELECT * FROM dates_completed_goals") datesDict = {} # Returns the dates in a tuple (1, dict_value) rows = rows.fetchall() for row in rows: #iterate through the dict value pair and save to our dictionary for key in row[1]: datesDict[key] = row[1][key] session.close() return jsonify(datesDict) except psycopg2.DatabaseError as e: print('Error %s') % e sys.exit(1)
shavessel = f.read() shavessellist = shavessel.split("\n") shavessellist = filter(None, [i.strip() for i in shavessellist]) engine = create_engine( 'mysql+mysqldb://root:900502@localhost:3306/voyagecheck?charset=utf8') DBSession = sessionmaker(bind=engine) # 创建Session: session = DBSession() resultslist1 = [] for i in shavessellist: actsql = r"""select * From sipg1 where id = (select max(id) from sipg1 where trim(replace(VESSELEN, ' ', ''))=trim(replace(""" + r'"' + i.lower( ) + r'"' + r""",' ','')))""" result = session.execute(actsql).fetchall() resultslist1.append(result) resultslist2 = [] for i in shavessellist: actsql = r"""select * From snlvessel where id = (select max(id) from snlvessel where trim(replace(VESSELEN, ' ', ''))=trim(replace(""" + r'"' + i.lower( ) + r'"' + r""",' ','')))""" result = session.execute(actsql).fetchall() resultslist2.append(result) resultall = [] for i in zip(resultslist1, resultslist2): if i[0]: if i[0][0][5] == i[1][0][5]: _temp = list(i[0][0], ) _temp.append(i[1][0][-1]) resultall.append(_temp) else:
def on_search(query): print("In search") auth_provider = PlainTextAuthProvider(username='******', password='******') cluster = Cluster(auth_provider=auth_provider) session = cluster.connect('rudiment') print(query) #query_actor_string = "select title from actors where name = \'%s\';" % query #print(query_actor_string) rows = '' rows2 = '' """ Variables to hold query results """ # Result from querying an actor name actorResults = False #Results from querying a movie name movieResults = False #First try searching for movies an actor was in try: rows = session.execute( "select title from actors where name = \'%s\';" % query) except: print("not an actor") actorResults = True #Otherwise we are searching a movie title if not rows: actorResults = False try: rows = session.execute( "select title, score, votes, yr from movies where title = \'%s\' limit 1;" % query) except: print("not a movie") if rows: movieResults = True try: rows2 = session.execute( "select name from actors where title = \'%s\';" % query) except: print("this should never print") if actorResults: for i in range(1, 2): divider = {'title': '--------Movie Title--------'} emit('displayactors1', divider) for row in rows: tmp = {'title': row.title} print(tmp) emit('displayactors1', tmp) elif movieResults: for i in range(1, 2): divider = { 'title': '-Title-', 'score': '-Score-', 'votes': '-Votes-', 'yr': '-Year-' } emit('displaymovies', divider) for row in rows: tmp = { 'title': row.title, 'score': row.score, 'votes': row.votes, 'yr': row.yr } print(tmp) emit('displaymovies', tmp) for i in range(1, 2): divider = {'name': '--------Actors--------'} emit('displayactors2', divider) for row in rows2: tmp = {'name': row.name} print(tmp) emit('displayactors2', tmp) else: print("Nothing sport")