def get(self): "Retrieve the n most recent comments on a URL" data = request.get_json() author = data['author'] title = data['title'] n = data['n'] conn = get_db() c = conn.cursor() c.execute( """SELECT * from articles where title == (?) and author == (?)""", (title, author)) row = c.fetchone() article_id = row[0] if row != None: try: c.execute( """SELECT * from comments where article_id == (?) ORDER BY post_time DESC LIMIT (?)""", (article_id, n)) rows = c.fetchall() return jsonify(rows), 200 except Exception: conn.rollback() return jsonify(message="Failed to get comments"), 409
def get_user_details(username): conn = get_db() c = conn.cursor() c.execute("""SELECT * FROM users WHERE username = ?""", (username, )) rows = c.fetchall() if len(rows) == 0: return False return rows
def delete_user(username): conn = get_db() c = conn.cursor() try: c.execute("""DELETE FROM users WHERE username = ?""", (username, )) conn.commit() except Exception: conn.rollback()
def delete_tag(tag_id): conn = get_db() c = conn.cursor() try: c.execute("""DELETE FROM tags WHERE tag_id = ?""", (tag_id, )) conn.commit() except Exception: conn.rollback()
def delete_article(article_id): conn = get_db() c = conn.cursor() try: c.execute("""DELETE FROM articles WHERE article_id = ?""", (article_id, )) conn.commit() except Exception: conn.rollback()
def create_user(username, password, display_name): hash_password = encode_password(password) conn = get_db() c = conn.cursor() try: c.execute("""INSERT into users values (NULL, ?, ?, ?)""", (username, hash_password, display_name)) conn.commit() except Exception: conn.rollback()
def update_password(username, new_password): hash_password = encode_password(new_password) conn = get_db() c = conn.cursor() try: c.execute("""UPDATE users SET password = ? WHERE username = ?""", (hash_password, username)) conn.commit() except Exception: conn.rollback()
def get_article_by_url(url): conn = get_db() c = conn.cursor() try: c.execute("""SELECT * FROM articles where url = ?""", (url, )) rows = c.fetchall() if len(rows) == 0: return False return rows except Exception as e: return e
def get_tag_details(url): conn = get_db() c = conn.cursor() try: c.execute("""SELECT * FROM tags where url = ?""", (url, )) rows = c.fetchall() if len(rows) == 0: return False return rows except Exception as e: return e
def get_article_details(user_id, title): conn = get_db() c = conn.cursor() try: c.execute("""SELECT * FROM articles where user_id = ? AND title = ?""", (user_id, title)) rows = c.fetchall() if len(rows) == 0: return False return rows except Exception as e: return e
def post_tag(tag_name, url): conn = get_db() c = conn.cursor() unix = int(time.time()) post_time = str( datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S')) try: c.execute("""INSERT INTO tags VALUES (NULL, ?, ?, ?)""", (tag_name, url, post_time)) conn.commit() except Exception: conn.rollback()
def post(self): data = request.get_json() title = data['title'] author_name = data['author'] comment = data['comment'] post_time = datetime.now() success_flag = 0 if author == "Anonymous Coward": user_id = -1 display_name = "Anonymous Coward" else: user_id = data['user_id'] display_name = data['display_name'] conn = get_db() c = conn.cursor() try: c.execute( """SELECT * from articles where title == (?) and author == (?)""", (title, author_name)) row = c.fetchone() article_id = row[0] except Exception: response = app.response_class(response=json.dumps( {"message": "Article Not Found"}, indent=4), status=404, content_type='application/json') return response if row != None: try: c.execute( """INSERT into comments(user_id, article_id, display_name, comment, post_time) values (?,?,?,?,?) """, (user_id, article_id, display_name, comment, post_time)) conn.commit() success_flag = 1 except Exception: conn.rollback() if success_flag == 1: data['user_id'] = user_id data['display_name'] = display_name resp = jsonify(data) resp.status_code = 201 return resp else: response = app.response_class(response=json.dumps( {"message": "Failed to add Comment"}, indent=4), status=409, content_type='application/json') return response
def get_article_id(data): title = data['title'] author = data['author'] conn = get_db() c = conn.cursor() c.execute( """SELECT * from articles where title == (?) and author == (?)""", (title, author)) row = c.fetchone() article_id = row[0] return article_id
def get_article(title): conn = get_db() c = conn.cursor() try: c.execute( """SELECT * FROM articles WHERE title = ? ORDER BY post_time desc""", (title, )) rows = c.fetchall() if len(rows) == 0: return False return rows except Exception as e: return e
def edit_article(title, author, text, article_id): conn = get_db() c = conn.cursor() unix = int(time.time()) last_updated_time = str( datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S')) try: c.execute( """UPDATE articles SET text = ?, author = ?, title = ?,last_updated_time = ? WHERE article_id = ?""", (text, author, title, last_updated_time, article_id)) conn.commit() except Exception: conn.rollback()
def get_n_articles(n): conn = get_db() c = conn.cursor() c.execute( """SELECT text, author, title, post_time, last_updated_time FROM articles ORDER BY \ post_time desc LIMIT ?""", (n, )) rows = c.fetchall() if len(rows) == 0: return False row_headers = [x[0] for x in c.description] articles = [] for article in rows: articles.append(dict(zip(row_headers, article))) return articles
def get_tags_metadata(n): conn = get_db() c = conn.cursor() c.execute( """SELECT tag, title, url FROM tags ORDER BY post_time LIMIT ?""", (n, )) row_headers = [x[0] for x in c.description] rows = c.fetchall() tags = [] for tag in rows: tags.append(dict(zip(row_headers, tag))) return tags
def post_article(user_id, text, author, title, url): conn = get_db() c = conn.cursor() unix = int(time.time()) post_time = str( datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S')) last_updated_time = str( datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S')) try: c.execute( """INSERT INTO articles VALUES (NULL, ?, ?, ?, ?, ?, ?, ?)""", (user_id, text, author, title, url, post_time, last_updated_time)) conn.commit() except Exception: conn.rollback()
def get_tags_by_url(url): conn = get_db() c = conn.cursor() try: c.execute("""SELECT tag_name, url FROM tags where url = ?""", (url, )) rows = c.fetchall() if len(rows) == 0: return False row_headers = [x[0] for x in c.description] tags = [] for tag in rows: tags.append(dict(zip(row_headers, tag))) return tags except Exception as e: return e
def get(self): "Retrieve the number of comments on a given article" data = request.get_json() # title = data['title'] # author = data['author'] article_id = data['article_id'] conn = get_db() c = conn.cursor() try: c.execute('SELECT COUNT(*) from comments where article_id == (?)', (article_id)) rows = c.fetchone() return jsonify(rows), 200 except Exception: conn.rollback() error_msg = "Failed to retrieve the Number of Comments" return jsonify(error_msg), 409
def delete(self): # Delete an individual comment data = request.get_json() comment_id = data['comment_id'] conn = get_db() c = conn.cursor() c.execute("""SELECT * from comments where comment_id == (?)""", (comment_id)) row = c.fetchone() if row: c.execute("""DELETE from comments where comment_id == (?)""", (comment_id)) conn.commit() response = app.response_class(response=json.dumps( {"message": "OK"}, indent=4), status=200, content_type='application/json') return response else: response = app.response_class(response=json.dumps( {"message": "Comment Not Found"}, indent=4), status=404, content_type='application/json') return response
#!/usr/bin/python # -*- coding: utf-8 -*- import db_connection # Set up database connection con = db_connection.get_db() def get_busline(relation_id): # Execute query to obtain line of bus route sql = "SELECT ST_LineMerge(ST_Transform(ST_Collect(way), 4326)) FROM planet_osm_line JOIN (SELECT ltrim(member, 'w')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id = " + str(relation_id) + ") u WHERE member LIKE 'w%') x USING (osm_id)"; busline = con.ExecuteSQL(sql); return busline; def get_busstops(relation_id): # Execute query to obtain points of bus stops sql = "SELECT name, public_transport, highway, '' AS official_status, amenity, osm_id, ST_Transform(way, 4326) FROM planet_osm_point JOIN (SELECT ltrim(member, 'n')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id = " + str(relation_id) + ") u WHERE member LIKE 'n%') x USING (osm_id) WHERE public_transport = 'platform'"; busstops = con.ExecuteSQL(sql); return busstops; def get_stoparea(node_id): # Execute query to obtain bus stop area relation sql = "SELECT id, tags FROM planet_osm_rels WHERE ARRAY['public_transport','stop_area']<@tags AND ARRAY['n" + str(node_id) + "','platform']<@members"; stoparea = con.ExecuteSQL(sql); return stoparea; def get_routemaster(relation_id): # Execute query to obtain route master relation
#!/usr/bin/python # -*- coding: utf-8 -*- import db_connection # Set up database connection con = db_connection.get_db() def get_busline(relation_id): # Execute query to obtain line of bus route sql = "SELECT ST_LineMerge(ST_Transform(ST_Collect(way), 4326)) FROM planet_osm_line JOIN (SELECT ltrim(member, 'w')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id = " + str( relation_id) + ") u WHERE member LIKE 'w%') x USING (osm_id)" busline = con.ExecuteSQL(sql) return busline def get_busstops(relation_id): # Execute query to obtain points of bus stops sql = "SELECT name, public_transport, highway, amenity, osm_id, ST_Transform(way, 4326) FROM planet_osm_point JOIN (SELECT ltrim(member, 'n')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id = " + str( relation_id ) + ") u WHERE member LIKE 'n%') x USING (osm_id) WHERE public_transport = 'platform'" busstops = con.ExecuteSQL(sql) return busstops def get_length(relation_id): sql = "SELECT ST_Length(ST_Transform(ST_Collect(way), 4326)) FROM planet_osm_line JOIN (SELECT ltrim(member, 'w')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id =" + str( relation_id) + ") u WHERE member LIKE 'w%') x USING (osm_id)" busline_length = con.ExecuteSQL(sql)