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 add_todo(): try: session = cassandra.connect() session.set_keyspace("todo") task_name = request.form['task_name'] task_description = request.form['task_description'] task_priority = request.form['task_priority'] task_start = request.form['task_start'] task_end = request.form['task_end'] task_difficulty = request.form['task_difficulty'] task_assignee = request.form['task_assignee'] subtasks_names = request.form['subtasks_names'] subtasks_descriptions = request.form['subtasks_descriptions'] subtasks_difficulties = request.form['subtasks_difficulties'] subtasks_refs = request.form['subtasks_refs'] subtasks_assignees = request.form['subtasks_assignees'] id = str(uuid.uuid4()) task_cql = "INSERT INTO todo.tasks(id,name,description,priority,difficulty,start,end,assignee) VALUES("+id+",'"+nullTostr(task_name.replace("'","''"))+"','"+nullTostr(task_description.replace("'","''"))+"','"+nullTostr(task_priority.replace("'","''"))+"','"+nullTostr(task_difficulty.replace("'","''"))+"','"+nullTostr(task_start.replace("'","''"))+"','"+nullTostr(task_end.replace("'","''"))+"','"+nullTostr(task_assignee.replace("'","''"))+"');" print(task_cql) session.execute(task_cql) for i,s in enumerate(subtasks_names.split('|')): sub_tasks_cql = "INSERT INTO todo.sub_tasks(id,task_id,name,description,difficulty,ref,assignee) VALUES(UUID()"+",'"+id+"','"+nullTostr(s.replace("'","''"))+"','"+nullTostr(subtasks_descriptions.split('|')[i].replace("'","''"))+"','"+nullTostr(subtasks_difficulties.split('|')[i].replace("'","''"))+"','"+nullTostr(subtasks_refs.split('|')[i].replace("'","''"))+"','"+nullTostr(subtasks_assignees.split('|')[i].replace("'","''"))+"')" print(s) session.execute(sub_tasks_cql) result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message"+":\"Task '" + task_name + "' added Successfully!\"}"+"],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/get_all_tasks\" \n },{ \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/get_task_details_by_id<"+str(id)+"\" \n },{ \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/delete_task_by_id<"+str(id)+"\" \n },{ \n \"rel\" : \"child\",\n \"href\": \""+ "/rest/get_all_sub_tasks\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }" return Response (result, status = 200, mimetype = 'application/json') res="Task '" + task_name + "' added Successfully!" result = res 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 query5(): hashtag = request.form['query5'] cluster = Cluster(['127.0.0.1']) session = cluster.connect() session.set_keyspace("midsem") query = session.prepare( """ SELECT * from table_query5 where hashtag = ?""") open_read = session.execute(query, (hashtag, )) page = " <style> table, th, td { border: 1px solid black; } </style>" page = page + "<table style = \"width:100%\"> <tr> <th> HASHTAG </th> <th> DATE </th> <th> TWEET COUNT </th> </tr> " for row in open_read: page = page + "<tr> " page += "<td> %s </td>" % repr(row.hashtag) page += "<td> %s </td>" % repr(row.date) page += "<td> %s </td>" % repr(row.tweet_count) page += "</tr>" page += "</table>" return page
def get_user_by_id(id): try: id = id.replace("<", "") id = id.replace(">", "") print(id) result = "" session = cassandra.connect() session.set_keyspace("todo") cql = "SELECT * FROM login 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) + "\", \"user name\": \"" + str( row.uname) + "\", \"role\": \"" + str( row.role) + "\"\n}" else: res = res + "{\"id\": \"" + str( row.id) + "\", \"user name\": \"" + str( row.uname) + "\", \"role\": \"" + str( row.role) + "\"}," res = res + "],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \"" + "/rest/delete_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_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 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 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 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_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 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 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 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 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 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_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 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 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 get_chart_data(session, current_time): session.set_keyspace("cep_analytics") cql = "SELECT * FROM cep_analytics.smarthome_cep_table LIMIT 10" r = session.execute(cql) return r
def render_map(): global session session = cassandra.connect() session.set_keyspace("cep_analytics") return render_template('leaflet_template.html')