def getCitiesOverview(): # Database query conn = create_connection('./database/wanderweg.db') cur = conn.cursor() sql = 'SELECT id,name,country,population,latitude,longitude,hostelworld_pic,trainline_id FROM cities' cur.execute(sql) data = cur.fetchall() conn.close() # Format response cities = [] for entry in data: if entry[-1] != '0' and entry[-1] != 'ID not found' and entry[ 1] not in exclude_ids: city = { 'name': entry[1], 'city_id': entry[0], 'country': entry[2], 'population': entry[3], 'location': { 'lat': entry[4], 'lng': entry[5] }, 'alt_cover': entry[6] } cities.append(city) response = flask.jsonify(cities) return response
def user_signup(user, settings=settings): """Create a new user in the users table :param user: dict :return: user id """ token = secrets.token_urlsafe(16) sql = ''' INSERT INTO users(name,email,password,is_active,login_attempts,last_login_attempt,token) VALUES(?,?,?,?,?,?,?) ''' try: conn = create_connection(settings.DATABASE) cur = conn.cursor() cur.execute(sql, ( user['name'], user['email'], hash_password(user['password']), 0, 0, None, token )) conn.commit() cur.close() except Error as e: log(e) result = None else: result = user_find_by_email(user['email']) finally: if (conn): conn.close() return result
def main(settings=settings): """ Creates sqlite database and users table """ database = settings.DATABASE sql_create_users_table = """CREATE TABLE IF NOT EXISTS users ( id integer PRIMARY KEY, name text NOT NULL, email text NOT NULL UNIQUE, password text NOT NULL, is_active integer NOT NULL, login_attempts integer NOT NULL, last_login_attempt timestamp, token text NOT NULL );""" # create a database connection conn = create_connection(database) if conn is not None: try: # create users table create_table(conn, sql_create_users_table) except Error as e: print(e) finally: conn.close() else: print('Error! could not create database connection') exit(0)
def getLocalExonyms(): #Get city name and country conn = create_connection('../database/wanderweg.db') cur = conn.cursor() city_sql = 'SELECT name, country FROM cities' cur.execute(city_sql) city_data = cur.fetchall() city_country = {entry[0]:entry[1] for entry in city_data} exo_sql = 'SELECT * FROM exonyms' cur.execute(exo_sql) col_names = [country[0] for country in cur.description[1:]] country_index = {country:index+1 for index, country in enumerate(col_names)} exonyms = cur.fetchall() conn.close() #Map cities to their local exonym exonym_dict = {} for row in exonyms: english_index = country_index['england'] if row[english_index] in city_country.keys(): cur_city = row[english_index] #Get the country for the current city country = city_country[cur_city] #Get the local city name for the current city if country.lower() in country_index: local_name = row[country_index[country.lower()]] else: local_name = cur_city exonym_dict[cur_city] = local_name return exonym_dict
def create_cities_table(database): # create a database connection conn = create_connection(database) #Create table for city features sql_create_cities_table = """ CREATE TABLE IF NOT EXISTS cities ( id integer PRIMARY KEY, name text NOT NULL, country text NOT NULL, hostel_url text, hostelworld_pic text, population integer, latitude real, longitude real, weather text, trainline_id text ); """ if conn is not None: # create cities table create_table(conn, sql_create_cities_table) else: print("Error! cannot create the database connection.") return conn
def user_activate(user_id, token, settings=settings): """Activate a user with a passed user id and token :param user_id: :param token: :return: user id """ sql = ''' UPDATE users SET is_active = 1 WHERE token = ? AND id = ? ''' try: conn = create_connection(settings.DATABASE) cur = conn.cursor() cur.execute(sql, (token, user_id)) conn.commit() cur.close() except Error as e: log(e) result = False else: result = True finally: if (conn): conn.close() return result
def result(): """ sends results :return: json object with results """ data = request.args user_id = data["user_id"] query = "SELECT words, topic from user_content WHERE user_id = {}".format( user_id) conn = create_connection() (queryResults) = run_query(conn, query) print(queryResults) words = [] topics = [] for i in queryResults: words.append(i["words"]) topics.append(i["topic"]) word_freq = get_word_percent(queryResults) freq = [] conn.commit() conn.close() return jsonify({ "code": 1, "message": "Stats fetched successfully", "words_stats": word_freq, "alldata": queryResults, "content_type": "application/json" })
def result(): """ sends results :return: json object with results """ data = request.args user_id, session_id, content_id = data["user_id"], data[ "session_id"], data["content_id"] query = "SELECT words from user_content WHERE content_id = {}".format( content_id) conn = create_connection() words = run_query(conn, query) query = "SELECT * from content WHERE content_id = {}".format(content_id) content = run_query(conn, query)[0] word_freq = get_word_percent(words) conn.commit() conn.close() return jsonify({ "code": 1, "message": "Stats fetched successfully", "content_id": content_id, "words_stats": word_freq, "content_type": content["content_type"], "content_image_url": content["content_url"] })
def get_email(user_id): sql = "select * from users where user_id=" + str(user_id) conn = create_connection() results = run_query(conn=conn, query=sql) try: return results[0]["email"] except Exception as e: print(e) return None
def saveDBToText(): conn = create_connection('../database/wanderweg.db') cur = conn.cursor() sql = 'SELECT id, name, trainline_id FROM cities' cur.execute(sql) data = cur.fetchall() string = json.dumps(data) f = open('../database/trainline_ids.txt', 'w+') f.write(string) f.close()
def vt_lookup(): conn = create_connection("./maldns.db") if conn == None: logging.error("No database connection, exiting.") sys.exit(1) while True: scan_expired_or_unscanned_domains(conn) logging.info("Done scanning")
def insert_user(data): """ insert new user :param data: user data from front-end """ token = secrets.token_urlsafe(20) query = "INSERT into users(name,email,image_url,api_token) VALUES(%s,%s,%s,%s)" conn = create_connection() run_query(conn, query, [data["name"], data["email"], data["image"], token]) conn.close()
def update_user_image(data): """ update the user image :param data: data recieved from the client side """ query = "UPDATE users SET image_url='%s' where email='%s'" % ( data["image"], data["email"]) conn = create_connection() run_query(conn, query, []) conn.close()
def update_user_token(data): """ update token for already registered users """ token = secrets.token_urlsafe(20) query = "UPDATE users SET api_token='%s' where email='%s'" % ( token, data["email"]) conn = create_connection() run_query(conn, query, []) conn.close()
def fetchID(sql_id): conn = create_connection('./database/wanderweg.db') cur = conn.cursor() sql = "SELECT trainline_id FROM cities WHERE id = '" + sql_id + "'" cur.execute(sql) data = cur.fetchone() trainline_id = data[0] if trainline_id == '0' or trainline_id == 'ID not found': trainline_id = None return trainline_id
def addIDsToDB(trainline_ids): db_vals = [] for key, val in trainline_ids.items(): if val == 'One city in pair could not be found': continue else: db_vals.append((val, key)) conn = create_connection('../database/wanderweg.db') cur = conn.cursor() sql = 'UPDATE cities SET trainline_id = ? WHERE name = ?' cur.executemany(sql, db_vals) conn.commit() conn.close()
async def load_data(): db_path = "/data/db_config/koster_lab-nm.db" movie_dir = "/uploads" conn = db_utils.create_connection(db_path) df = pd.read_sql_query( "SELECT b.filename, b.frame_number, a.species_id, a.x_position, a.y_position, a.width, a.height FROM agg_annotations_frame AS a LEFT JOIN subjects AS b ON a.subject_id=b.id", conn, ) df["movie_path"] = (movie_dir + "/" + df["filename"].apply( lambda x: os.path.basename(x.rsplit("_frame_")[0]) + ".mov")) return {"data": df.to_dict()}
def loadDBFromText(): with open('../database/trainline_ids.txt', 'r') as f: content = f.read() data = json.loads(content) tuple_data = [] for entry in data: city_id, city_name, trainline_id = entry if len(trainline_id) > 20: tuple_data.append((trainline_id, city_id, city_name)) conn = create_connection('../database/wanderweg.db') cur = conn.cursor() sql = 'UPDATE cities SET trainline_id = ? WHERE id = ? AND name = ?' cur.executemany(sql, tuple_data) conn.commit() conn.close()
def topics(): """ returns topics :return: json object of topic data """ data = request.args offset = int(data["offset"]) if "limit" in data: limit = data["limit"] else: limit = 10 query = "select * from topics" conn = create_connection() results = run_query(conn, query) conn.close() return jsonify(get_topics_data(results, offset, limit))
def login(): """ handles login :return: json object of login data """ name, email, image = request.form["name"], request.form[ "email"], request.form["image"] data = {"name": name, "email": email, "image": image} query = "select * from users where email='{}'".format(email) conn = create_connection() results = run_query(conn, query) if len(results) == 0 or results is None: insert_user(data) else: update_user_token(data) update_user_image(data) return jsonify(get_user_data(email))
def submit(): """ handles user submits :return: json object of submit confirmation """ conn = create_connection() data = request.json user_id, words_input, datetime, topic = data["user_id"], data[ "words_input"], data["datetime"], data["topic"] email = get_email(user_id) for i, word in enumerate(words_input): print("topic is : ", topic[i]) query = "INSERT into user_content(words, user_id, time,email, topic) VALUES(%s,%s,%s,%s,%s)" run_query(conn, query, [word, user_id, datetime[i], email, topic[i]]) conn.commit() conn.close() return jsonify({"code": 1, "message": "user response saved"})
def dns_capture(): conn = create_connection("./maldns.db") if conn == None: sys.exit(1) create_table(conn) if config.interface not in netifaces.interfaces(): logging.error("Bad interface. Check config.py") sys.exit(1) cap = pyshark.LiveCapture(interface=config.interface, bpf_filter='udp port 53') for pkt in cap.sniff_continuously(): store_dns_info(pkt, conn) conn.close()
def summary(): """ sends result summary :return: json object with results """ data = request.args user_id, session_id = data["user_id"], data["session_id"] query = "SELECT user_content.content_id, words, content_url, content_type FROM user_content " \ "INNER JOIN content ON content.content_id = user_content.content_id WHERE user_content.session_id = '" + session_id + "' GROUP BY content_id" conn = create_connection() data = run_query(conn, query) conn.commit() conn.close() data_json = { "code": 1, "message": "Results fetched successfully", "session_id": session_id, "data": data } return jsonify(data_json)
def submit(): """ handles user submits :return: json object of submit confirmation """ conn = create_connection() data = request.form user_id, session_id, words_input, content_id, click_type = data["user_id"], data["session_id"], data["words_input"], \ data["content_id"], data["click_type"] email = get_email(user_id) words = ast.literal_eval(str(words_input)) for word in words: query = "INSERT into user_content(words,time,session_id,content_id,email) VALUES(%s,%s,%s,%s,%s)" run_query( conn, query, [word[0], get_datetime(word[1]), session_id, content_id, email]) conn.commit() conn.close() return jsonify({"code": 1, "message": "user response saved"})
def topics(): """ returns topics :return: json object of topic data """ data = request.args offset = int(data["offset"]) if "limit" in data: limit = data["limit"] else: limit = 10 query = "select * from topics" conn = create_connection() results = run_query(conn, query) newresult = [] for i in results: newresult.append(i["topic_name"]) print(newresult) conn.close() return jsonify(get_topics_data(newresult, offset, limit))
def create_exonyms_table(database): # create a database connection conn = create_connection(database) #Create table for city features sql_create_exonyms_table = """ CREATE TABLE IF NOT EXISTS exonyms ( id integer PRIMARY KEY, england text NOT NULL, germany text, france text, italy text, spain text, portugal text, brazil text, sweden text, poland text, finland text, denmark text, netherlands text, norwegian text, czech_republic text, russia text, turkey text, japan text, korea text, china text ); """ if conn is not None: # create exonyms table create_table(conn, sql_create_exonyms_table) else: print("Error! cannot create the database connection.") return conn
def user_find_by_email(email, settings=settings): """Get user data by email :param: email: string :return: results """ find_user_sql = '''SELECT * FROM users WHERE email = ? ''' try: conn = create_connection(settings.DATABASE) cur = conn.cursor() cur.execute(find_user_sql, [email]) user = cur.fetchone() cur.close() except Error as e: log(e) result = None else: result = user finally: if (conn): conn.close() return result
def get_user_data(email): """ Get user data :param email: email of the user :return: formatted response for login function """ query = "SELECT * from users where email='{}'".format(email) # print(query) conn = create_connection() result = run_query(conn, query)[0] # print(result) conn.close() data = { "code": 1, "message": "logged In successfully", "data": { "user_id": result["user_id"], "name": result["name"], "email": result["email"], "image": result["image_url"], "api_token": result["api_token"] } } return data
def getCityInfo(cid): # Database query conn = create_connection('./database/wanderweg.db') cur = conn.cursor() sql = 'SELECT name,country,hostel_url,population,weather,hostelworld_pic,latitude,longitude FROM cities WHERE id=' + cid cur.execute(sql) data = cur.fetchone() conn.close() if not data: info = {'error': 'no country with that ID found'} response = flask.jsonify(info) return response # Fetch activities activityScraper = GatherActivities() #Use latitude and longitude to refine activity gathering activities = activityScraper.scrapeCity(data[0], (data[-2], data[-1])) # Fetch hostel info hostelData = gatherHostelData(data[2]) # Format response info = { 'city_id': int(cid), 'population': data[3], 'name': data[0], 'country': data[1], 'activities': activities, 'hostels': hostelData, 'weather': data[4], 'alt_cover': data[5] } response = flask.jsonify(info) return response
def user_login(email, password, settings=settings): """Login a user with email and password :param: email: string :param: password: string :return: user: """ currtime = datetime.now() user = user_find_by_email(email) if not user: return None try: conn = create_connection(settings.DATABASE) cur = conn.cursor() if not verify_password(user[3], password): # password does not match! # incr login_attempts and update last_login_attempt for email update_user_sql = ''' UPDATE users SET login_attempts = login_attempts + 1, last_login_attempt = ? WHERE email = ? ''' cur.execute(update_user_sql, [ currtime, email ]) conn.commit() cur.close() return None else: # Return None if user is not activated if (user[4] != 1): return None # Calculate time since last login minutes = 0 if user[6]: difference = datetime.now() - user[6] minutes = (difference.seconds // 60) % 60 # Return None if failed attempts > 3 and last_login_attempt < 1 # hour ago print(user[5], minutes) if ((user[5] >= 3) and (minutes < 60)): return None # reset login attempts and last_login_attempt print('reset login_attempts') update_user_sql = ''' UPDATE users SET login_attempts = 0, last_login_attempt = ? WHERE email = ? ''' cur.execute(update_user_sql, [ currtime, email ]) conn.commit() cur.close() user = user_find_by_email(email) except Error as e: log(e) result = None else: result = user finally: if (conn): conn.close() return user