def on_data(self, data): #print(data) #print(type(data)) #tweet = json.loads(data) dbconnect.connect(data) #dbconnect.connect(json.dumps(tweet._json)) return True
def reset_password(login_name): con, c = dbconnect.connect() if validate_email(login_name): query = " SELECT * FROM user WHERE user_email = %s " else: query = " SELECT * FROM user WHERE user_name = %s " con, c = dbconnect.connect() c.execute(query, (login_name,)) row = c.fetchall() if len(row) == 0: dbconnect.close(con, c) else: row = row[0] new_password = random_password() new_pass_hash = generate_password_hash(new_password) uid = row[0] email = row[1] username = row[2] query = " UPDATE user SET user.pass_hash = %s WHERE user.user_id = %s " c.execute(query, (new_pass_hash, uid)) con.commit() dbconnect.close(con, c) html_msg = render_template("resetpassword.html", username=username, new_password=new_password) from run import send_mail send_mail("Walk With Me New Password", [email], html_msg) return jsonify({"Status": 1, "Message": "New password has been sent to your email address if the account exist."})
def getfeaturesuser(): dbconnect.connect() mylist = dbconnect.getdeminfo() demdf = pd.DataFrame(mylist, columns=['author', 'subreddit', 'score']) demdf["leaning"] = 1 mylist = dbconnect.getrepinfo() repdf = pd.DataFrame(mylist, columns=['author', 'subreddit', 'score']) repdf["leaning"] = 0 frames = [demdf, repdf] df = pd.concat(frames) df = df.drop_duplicates() authorlist = df.author.unique() subredditlist = df.subreddit.unique() subredditlist = ['leaning'] + list(subredditlist) finallist = pd.DataFrame(index=list(authorlist), columns=subredditlist) finallist = finallist.fillna(0) subscriberlist = dbconnect.getsubscribercount() # print(subscriberlist) for row in tqdm(df.iterrows()): #each row is a tuple (index num, series) currentauthor = str(row[1]['author']) currentsubreddit = str(row[1]['subreddit']) currentleaning = str(row[1]['leaning']) currentscore = row[1]['score'] currentsubscriber = subscriberlist[currentsubreddit] # print(currentsubscriber) if currentsubscriber != 0: try: finallist.loc[currentauthor, currentsubreddit] += (float(currentscore) / currentsubscriber) * 1000 except: print(currentsubreddit, currentsubscriber) #finallist.loc[currentauthor, 'author'] = currentauthor finallist.loc[currentauthor, 'leaning'] = currentleaning # print(finallist) finallist.reset_index(drop=True, inplace=True) finallist = finallist.sample(frac=1) finallist.reset_index(drop=True, inplace=True) # print(finallist['politics']) # print(finallist) finallist = finallist.drop(columns=['democrats', 'Republican']) deletedlist = dbconnect.getdeletedsubreddits() for delsub in deletedlist: try: finallist = finallist.drop(columns=delsub) except: continue dbconnect.disconnect() return finallist
def getfinallist(): dbconnect.connect() mylist = dbconnect.getdeminfo() demdf = pd.DataFrame(mylist, columns=['author', 'subreddit', 'score']) demdf["leaning"] = "dem" mylist = dbconnect.getrepinfo() repdf = pd.DataFrame(mylist, columns=['author', 'subreddit', 'score']) repdf["leaning"] = "rep" frames = [demdf, repdf] df = pd.concat(frames) df = df.drop_duplicates() authorlist = df.author.unique() subredditlist = df.subreddit.unique() subredditlist = ['leaning'] + list(subredditlist) finallist = pd.DataFrame(index=list(authorlist), columns=subredditlist) finallist = finallist.fillna(0) for row in tqdm(df.iterrows()): #each row is a tuple (index num, series) currentauthor = str(row[1]['author']) currentsubreddit = str(row[1]['subreddit']) currentleaning = str(row[1]['leaning']) currentscore = row[1]['score'] #print(currentauthor) #print(currentsubreddit) finallist.loc[currentauthor, currentsubreddit] += float(currentscore) #finallist.loc[currentauthor, 'author'] = currentauthor finallist.loc[currentauthor, 'leaning'] = currentleaning for column in finallist: if column == 'leaning': continue max = finallist[column].max() if max == 0: continue max = float(max) mylist = finallist[column].astype('float') finallist[column] = mylist.divide(other=max).round(3) finallist.reset_index(drop=True, inplace=True) finallist = finallist.sample(frac=1) finallist.reset_index(drop=True, inplace=True) # print(finallist['politics']) # print(finallist) finallist = finallist.drop(columns=['democrats', 'Republican']) print(finallist) finallist.to_pickle("../activitydata2020.pkl") dbconnect.disconnect()
def lost_account(): cursor, conn = connect() email = request.form.get("email") check_existing_users_stmt = "SELECT user_id FROM users WHERE email=%s" cursor.execute(check_existing_users_stmt, (email)) result = cursor.fetchall() if len(result) is not 1: return error_with_message("user does not exist") user_id = result[0][0] create_recovery_stmt = "INSERT INTO password_recovery (user_id, recovery_token) VALUES (%s, %s)" recovery_token = ''.join( random.choice(string.ascii_letters + string.digits) for _ in range(32)) cursor.execute(create_recovery_stmt, (user_id, recovery_token)) if cursor.rowcount is not 1: return error_with_message("account recovery failed") conn.commit() # Send confirmation email FROM = "*****@*****.**" TO = [email] SUBJECT = "Recover your StudyBuddy Account" MSG = ( "Hello " + name + ",\nYou have requested to recovery your account. Please change your password by visiting this link: " + "http://34.214.169.181:5000/account_recovery?recovery_token=" + recovery_token + "\n" + "\nThank you,\nThe StudyBuddies Team") message = 'Subject: {}\n\n{}'.format(SUBJECT, MSG) server = smtplib.SMTP('localhost') server.sendmail(FROM, TO, message) server.quit() print(message) return success_with_data({"confirmation_token": recovery_token})
def delete_user(**kwargs): cursor, conn = connect() password = request.form.get("password") user_id = kwargs["user_id"] del_user_stmt = "DELETE from users where id=%s AND password=%s" cursor.execute(del_user_stmt, (user_id, password)) if cursor.rowcount is not 1: return error_with_message("user does not exist") del_user_class_stmt = "DELETE from user_classes where user_id=%s" cursor.execute(del_user_class_stmt, (user_id, )) del_user_sess_stmt = "DELETE from sessions where user_id=%s" cursor.execute(del_user_sess_stmt, (user_id, )) find_group_stmt = "SELECT id FROM groups WHERE leader_id=%s" cursor.execute(find_group_stmt, (user_id, )) results = cursor.fetchall() if len(results) != 0: group_id = results[0][0] find_member_stmt = "SELECT id FROM users WHERE group_id=%s" cursor.execute(find_member_stmt, (group_id, )) results = cursor.fetchall() if len(results) == 0: del_group_stmt = "DELETE from groups where leader_id=%s" cursor.execute(del_group_stmt, (user_id, )) else: newlead = results[0][0] change_leader_stmt = "UPDATE groups SET leader_id=%s where leader_id=%s" cursor.execute(change_leader_stmt, (newlead, user_id)) conn.commit() return success_with_data({})
def create_group(**kwargs): userID = kwargs["user_id"] cursor, conn = connect() class_id = request.form.get("class_id") end_time = request.form.get("end_time") category = request.form.get("category") description = request.form.get("description") location_lat = request.form.get("location_lat") location_lon = request.form.get("location_lon") location_des = request.form.get("location_description") check_user_group_id_stmt = "SELECT group_id FROM users WHERE id=%s" cursor.execute(check_user_group_id_stmt, (userID, )) results = cursor.fetchall() if results[0][0] == 1: return error_with_message("User already in group") create_group_stmt = "INSERT INTO groups (class_id, leader_id, end_time, category, description, location_lat, location_lon, location_description) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute(create_group_stmt, (class_id, userID, end_time, category, description, location_lat, location_lon, location_des)) if cursor.rowcount is not 1: return error_with_message("msg_creating_group_failed") group_id = cursor.lastrowid update_user_group_id_stmt = "UPDATE users SET group_id=%s WHERE id=%s" cursor.execute(update_user_group_id_stmt, (group_id, userID)) conn.commit() return success_with_data({"group_id": group_id})
def add(): form = RecipeForm() connection = connect() if form.validate_on_submit(): try: cursor = connection.cursor() recipe_name = form.recipe_name.data ingredients = form.ingredients.data instructions = form.instructions.data serving_size = form.serving_size.data category = form.category.data notes = form.notes.data now = datetime.now() formatted_date = now.strftime('%Y-%m-%d %H:%M:%S') sql = "INSERT INTO recipes " \ "(recipe_name, ingredients, instructions, serving_size, category, notes, date_added) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s)" cursor.execute(sql, (recipe_name, ingredients, instructions, serving_size, category, notes, formatted_date)) connection.commit() flash('Your new recipe has been created!', 'success') return redirect(url_for('home')) finally: connection.close() return render_template('add.html', title='Add Recipe', form=form)
def list_user_classes(**kwargs): user_id = kwargs["user_id"] user_classes_stmt = """SELECT classes.id, course_title, course_name FROM classes LEFT JOIN user_classes ON classes.id = user_classes.class_id WHERE user_classes.user_id = %s""" cursor, conn = connect() cursor.execute(user_classes_stmt, (user_id, )) classes = cursor.fetchall() resultsDict = [] for the_class in classes: groups = [] get_group_stmt = """SELECT id, leader_id, start_time, end_time, category, description, location_lat, location_lon, location_description, chat_id FROM groups WHERE class_id=%s AND end_time > CURRENT_TIMESTAMP""" cursor.execute(get_group_stmt, (the_class[0], )) group_list = cursor.fetchall() for the_group in group_list: group_id = the_group[0] leader_id = the_group[1] start_time = the_group[2] end_time = the_group[3] category = the_group[4] description = the_group[5] location_lat = the_group[6] location_lon = the_group[7] location_description = the_group[8] chat_id = the_group[9] get_size_stmt = "SELECT COUNT(*) FROM users WHERE group_id=%s" cursor.execute(get_size_stmt, (group_id, )) size = cursor.fetchone()[0] group_result = { "id": group_id, "leader_id": leader_id, "start_time": str(start_time), "end_time": str(end_time), "category": category, "description": description, "location_lat": location_lat, "location_lon": location_lon, "location_description": location_description, "chat_id": chat_id, "size": size } groups.append(group_result) id = the_class[0] course_title = the_class[1] course_name = the_class[2] result = { "id": id, "course_title": course_title, "course_name": course_name, "active_groups": groups } resultsDict.append(result) return success_with_data({"classes": resultsDict})
def login(): form = LoginForm() bcrypt = Bcrypt() connection = connect() if form.validate_on_submit(): try: email = form.email.data cursor = connection.cursor() sql = "SELECT email, password FROM users WHERE email = (%s)" cursor.execute(sql, email) data = cursor.fetchone() if data is not None and email == data[ 'email'] and bcrypt.check_password_hash( data['password'], form.password.data): flash('You have successfully logged in!', 'success') return redirect(url_for('home')) else: flash('Login unsuccessful! Please check email and password.', 'danger') finally: connection.close() return render_template('login.html', title='Login Page', form=form)
def sendSMS(sms, ebs=0, channel=0): db = connect() cur = db.cursor() ebschannel = "B" + str(ebs) + "C" + str(channel) if len(sms) >= 1: ksend = call('asterisk -rx "khomp sms %s %s %s"' % (ebschannel, sms['telefone'], sms['msg']), shell=True) outprocess = getstatusoutput('asterisk -rx "khomp sms %s %s %s"' % (ebschannel, sms['telefone'], sms['msg'])) data = "%s" % (strftime("%Y_%m_%d")) if 'ERROR' in outprocess[1]: status = '{}'.format(outprocess[1][0:63]) print(status, sms['id']) update_torperdo = "UPDATE torpedos_khomp set data_envio = '%s', status = '%s' where id = '%s'" % ( data, status, sms['id']) cur.execute(update_torperdo) db.commit() else: status = '{}'.format(outprocess[1]) update_torperdo = "UPDATE torpedos_khomp set data_envio = '%s', status = '%s' where id = '%s'" % ( data, status, sms['id']) cur.execute(update_torperdo) db.commit() else: print("Erro!")
def update(rid): form = UpdateForm() connection = connect() try: cursor = connection.cursor() sql = "SELECT * FROM recipes WHERE recipe_id = (%s)" cursor.execute(sql, rid) recipes = cursor.fetchall() finally: connection.close() connection = connect() if form.validate_on_submit(): try: cursor = connection.cursor() recipe_name = form.recipe_name.data ingredients = form.ingredients.data instructions = form.instructions.data serving_size = form.serving_size.data category = form.category.data notes = form.notes.data now = datetime.now() formatted_date = now.strftime('%Y-%m-%d %H:%M:%S') sql = "UPDATE recipes SET " \ "recipe_name = (%s), " \ "ingredients = (%s), " \ "instructions = (%s), " \ "serving_size = (%s), " \ "category = (%s), " \ "notes = (%s), " \ "date_modified = (%s) " \ "WHERE recipe_id = (%s)" cursor.execute( sql, (recipe_name, ingredients, instructions, serving_size, category, notes, formatted_date, rid)) connection.commit() flash('Your new recipe is updated!', 'success') return redirect(url_for('home')) finally: connection.close() return render_template('update.html', title='Update Recipe', form=form, recipes=recipes)
def dbpref(col=[], pin=[]): cursor = connect() col = col[0]+col[1] pin = pin[0]+pin[1]+pin[2]+pin[3] query = "select "+col+" from pref where pin='"+pin+"'" cursor.execute(query) print "reached here" data = cursor.fetchone() return str(data[0])
def get_data_grouping(symbol, period): query = """\ DECLARE @v_sp_rtn INT EXEC dbo.SP_T_CRYPTO_GROUP_MONITORING_R @i_symbol = ? , @i_period = ? , @o_sp_rtn = @v_sp_rtn OUTPUT; SELECT @v_sp_rtn as out """ params = (symbol, period) with db.connect() as cnxn: cursor = cnxn.cursor() cursor.execute(query, params) row = cursor.fetchall() idx = 0 data = [] column = [] result = 0 while row: if idx == 0: data = row # for r in row: # print (r) elif idx == 1: for r in row: # print (r) column = r[0].split(',') else: # print(row) result = row[0][0] if cursor.nextset(): row = cursor.fetchall() else: row = None idx +=1 # print(result) # print(column) # print(data) if result == 0: table = tablib.Dataset(*data, headers=column) print(table) print(type(table)) else: print('NO DATA')
def get_order_book_data(exchange_name, symbol, timestamp=None): query = """\ DECLARE @v_sp_rtn INT EXEC dbo.SP_T_CRYPTO_ORDER_BOOK_INFO_FILTER_R @i_exchange_name = ? , @i_symbol = ? , @i_timestamp = ? , @o_sp_rtn = @v_sp_rtn OUTPUT; SELECT @v_sp_rtn as out """ params = (exchange_name, symbol, timestamp) # print(query, params) with db.connect() as cnxn: cursor = cnxn.cursor() cursor.execute(query, params) row = cursor.fetchall() idx = 0 data = [] result = 0 while row: if idx == 0: data = get_order_book_dic(row) else: # print(row[0][0]) result = row[0][0] if cursor.nextset(): row = cursor.fetchall() else: row = None idx +=1 if result == 0: # print(column) return data else: time.sleep(3) return get_order_book_data(exchange_name, symbol, timestamp) # while True: # data = get_price_data() # print(data) # order_book = get_order_book_data('bitmex', 'BTC/USD', 1589862151591) # print(order_book) # time.sleep(1)
def get_data_price(exchange_name, symbol, period=None, timestamp=None, log_time=None, page_size=None): query = """\ DECLARE @v_sp_rtn INT EXEC dbo.SP_T_CRYPTO_PRICE_INFO_R @i_exchange_name = ? , @i_symbol = ? , @i_period = ? , @i_timestamp = ? , @i_log_time = ? , @i_page_size = ? , @o_sp_rtn = @v_sp_rtn OUTPUT; SELECT @v_sp_rtn as out """ params = (exchange_name, symbol, period, timestamp, log_time, page_size) with db.connect() as cnxn: cursor = cnxn.cursor() cursor.execute(query, params) row = cursor.fetchall() idx = 0 data = [] column = ['exchange_name', 'symbol', 'period', 'timestamp', 'datetime', 'open', 'high', 'low', 'close', 'volumn'] result = 0 while row: if idx == 0: data = row # for r in row: # print (r) else: # print(row) result = row[0][0] if cursor.nextset(): row = cursor.fetchall() else: row = None idx +=1 # print(result) # print(column) # print(data) if result == 0: table = tablib.Dataset(*data, headers=column) print(table) print(type(table)) else: print('NO DATA')
def show_actors(): username = get_cookie() conn = dbconnect.connect() try: with conn.cursor() as curs: curs.execute('SELECT actor_id, first_name, last_name FROM actor ORDER BY last_name ;') actors = curs.fetchall() finally: conn.close() return render_template('actors.html', username=username, actors=actors)
def show_films(): username = get_cookie() conn = dbconnect.connect() try: with conn.cursor() as curs: curs.execute('SELECT film_id, title, release_year, description FROM film ORDER BY title;') films = curs.fetchall() finally: conn.close() return render_template('films.html', username=username, films=films)
def logout(): cursor, conn = connect() token = request.form.get("session_token") logout_stmt = "DELETE FROM sessions where token=%s" cursor.execute(logout_stmt, (token, )) if cursor.rowcount is not 1: return error_with_message("session_does_not_exist") conn.commit() return success_with_data({})
def create_user(): cursor, conn = connect() email = request.form.get("email") password = request.form.get("password") name = request.form.get("name") class_year = request.form.get("class_year") split_email = email.split("@") school = split_email[1] find_school_stmt = "SELECT id FROM schools where email=%s" cursor.execute(find_school_stmt, (school, )) result = cursor.fetchall() if len(result) == 0: return error_with_message("must use school email") school_id = result[0][0] check_existing_users_stmt = "SELECT COUNT(*) FROM users WHERE email=%s" cursor.execute(check_existing_users_stmt, (email, )) count = cursor.fetchone()[0] if count is not 0: return error_with_message("user already exists") salt = ''.join( random.choice(string.ascii_letters + string.digits) for _ in range(32)) h = bcrypt_sha256.hash(password + salt) create_user_stmt = "INSERT INTO users (email, password, name, class_year, school_id, salt) VALUES (%s, %s, %s, %s, %s)" cursor.execute(create_user_stmt, (email, h, name, class_year, school_id, salt)) if cursor.rowcount is not 1: return error_with_message("creating user failed") conn.commit() user_id = cursor.lastrowid confirmation_token = ''.join( random.choice(string.ascii_letters + string.digits) for _ in range(32)) create_confirmation_stmt = "INSERT INTO email_confirmations (user_id, token) VALUES (%s, %s)" cursor.execute(create_confirmation_stmt, (user_id, confirmation_token)) conn.commit() # Send confirmation email FROM = "*****@*****.**" TO = [email] SUBJECT = "Confirm your StudyBuddy Account" MSG = ("Hello " + name + ",\nPlease confirm your account by visiting this link: " + "http://34.214.169.181:5000/confirm_email/" + confirmation_token + "\n" + "\nThank you,\nThe StudyBuddies Team") message = 'Subject: {}\n\n{}'.format(SUBJECT, MSG) server = smtplib.SMTP('localhost') server.sendmail(FROM, TO, message) server.quit() print(message) return success_with_data({"confirmation_token": confirmation_token})
def delete_class(**kwargs): cursor, conn = connect() user_id = kwargs["user_id"] class_id = int(request.form.get("class_id")) del_class_stmt = "DELETE FROM user_classes where user_id=%s AND class_id=%s" cursor.execute(del_class_stmt, (user_id, class_id)) if cursor.rowcount is not 1: return error_with_message("msg_failed_to_delete_class_for_user") conn.commit() return success_with_data({"deleted_class_id": class_id})
def update_profile(uid, email, age, gender, value): new_age = int(value["age"]) new_gender = value["gender"] if (new_age != age) or (new_gender != gender): con, c = dbconnect.connect() query = "UPDATE user SET user.age = %s ,user.gender =%s WHERE user.user_id = %s " c.execute(query, (new_age, new_gender, uid)) con.commit() dbconnect.close(con, c) return jsonify({"Status": 1, "Message": "Information changed successfully."})
def change_password(uid, new_password): if not is_strong_password(new_password): return jsonify({"Status": 0, "Message": "Your password is to weak, please try again."}) new_pass_hash = generate_password_hash(new_password) con, c = dbconnect.connect() query = " UPDATE user SET user.pass_hash = %s WHERE user.user_id = %s " c.execute(query, (new_pass_hash, uid)) con.commit() dbconnect.close(con, c) return jsonify({"Status": 1, "Message": "Password changed successfully, please login again."})
def check_auth(*args, **kwargs): cursor, conn = connect() session_token = request.form.get("session_token", "") auth_stmt = "SELECT user_id FROM sessions WHERE token=%s" cursor.execute(auth_stmt, (session_token,)) results = cursor.fetchall() if len(results) == 0: return error_with_message("msg_invalid_token") user_id = results[0][0] kwargs["user_id"] = user_id return f(*args, **kwargs)
def delete(rid): connection = connect() try: cursor = connection.cursor() sql = "DELETE FROM recipes WHERE recipe_id = (%s)" cursor.execute(sql, rid) connection.commit() finally: connection.close() return redirect(url_for('home'))
def home(): connection = connect() try: cursor = connection.cursor() sql = "SELECT * FROM recipes ORDER BY recipe_id desc" cursor.execute(sql) recipes = cursor.fetchall() finally: connection.close() return render_template('home.html', recipes=recipes, title='Home Page')
def view(rid): connection = connect() try: cursor = connection.cursor() sql = "SELECT * FROM recipes WHERE recipe_id = (%s)" cursor.execute(sql, rid) recipes = cursor.fetchall() finally: connection.close() return render_template('view.html', title='View Page', recipes=recipes)
def change_leader(**kwargs): cursor, conn = connect() new_leader_id = request.form.get("new_leader_id") user_id = kwargs["user_id"] change_leader_stmt = "UPDATE groups SET leader_id=%s where leader_id=%s" cursor.execute(change_leader_stmt, (new_leader_id, user_id)) if cursor.rowcount == 0: return error_with_message("user was not a group leader") conn.commit() return success_with_data({})
def confirm_email(confirmation_token): cursor, conn = connect() confirm_email_stmt = "SELECT user_id FROM email_confirmations WHERE token=%s" cursor.execute(confirm_email_stmt, (confirmation_token, )) result = cursor.fetchone() if len(result) == 0: return "Invalid confirmation" user_id = result[0] update_acct_stmt = "UPDATE users SET account_confirmed=1 WHERE id=%s" cursor.execute(update_acct_stmt, (user_id, )) conn.commit() return "Thanks for confirming your account. Click here to go back to the app"
def join_group(**kwargs): cursor, conn = connect() user_id = kwargs["user_id"] group_id = request.form.get("group_id") check_group_id_stmt = "SELECT COUNT(*) FROM groups WHERE id=%s" cursor.execute(check_group_id_stmt, (group_id, )) results = cursor.fetchall() if len(results) == 0: return error_with_message("no_matching_group_id") update_stmt = "UPDATE users SET group_id=%s WHERE id=%s" cursor.execute(update_stmt, (group_id, user_id)) conn.commit() return success_with_data({})
def start_search(searchquery): consumer_key = 'DHjYxfSwthP3CDCsoi2NAD1LD' consumer_secret = 'gXjx7X9gqpB6EHnFtY87zXqzoUa5ar4B5CjpGv11Jy96ocSCYW' access_token = '39007772-kSOdoKYkUJAZISv5HZLSMvcYzhR54tC4DDPelf6FO' access_secret = '3ZuxhuVRrpczjUA3wsnq45Yt9zgeRZ2U7wbYiKPVovDhl' auth = OAuthHandler(consumer_key,consumer_secret) auth.set_access_token(access_token,access_secret) api = tweepy.API(auth,wait_on_rate_limit = True) #api = tweepy.API(auth) #market=['$AAPL','$GOOGL','$MSFT'] max_tweets = 1000 print(api.rate_limit_status()['resources']['search']) tweetcount = 0 while True: try: for tweet in tweepy.Cursor(api.search,q=searchquery,count=1000).items(max_tweets): dbconnect.connect(json.dumps(tweet._json)) except tweepy.error.TweepError as e: print("Exceeded Limit") '''
def list_class_groups(**kwargs): cursor, conn = connect() user_id = kwargs["user_id"] class_groups_stmt = """SELECT groups.id, leader_id, start_time, end_time, category, groups.description, location_lat, location_lon, location_description, classes.id, course_title, course_name FROM groups LEFT JOIN classes ON groups.class_id = classes.id LEFT JOIN user_classes ON classes.id = user_classes.class_id WHERE user_classes.user_id = %s""" cursor.execute(class_groups_stmt, (user_id, )) results = cursor.fetchall() resultsDict = [] for result in results: group_id = result[0] leader_id = result[1] start_time = result[2] end_time = result[3] category = result[4] group_description = result[5] location_lat = result[6] location_lon = result[7] location_description = result[8] class_id = result[9] course_title = result[10] course_name = result[11] result = { "group_id": group_id, "leader_id": leader_id, "start_time": start_time, "end_time": end_time, "category": category, "group_description": group_description, "location_lat": location_lat, "location_lon": location_lon, "location_description": location_description, "class_id": class_id, "course_title": course_title, "course_name": course_name } resultsDict.append(result) return success_with_data({"groups": resultsDict})
def load_user(user_id): if user_id in user_cache: return user_cache[user_id] try: con, c = dbconnect.connect() query = " SELECT * FROM user WHERE user_id = %s " c.execute(query, (int(user_id),)) row = c.fetchall() dbconnect.close(con, c) row = row[0] valid_user = user.User(row[0], row[1], row[2], row[4], row[5]) add_to_cache(valid_user) return valid_user except Exception, e: return None
def update_user(**kwargs): cursor, conn = connect() userID = kwargs["user_id"] check_user_id_stmt = "SELECT id FROM users WHERE id=%s" cursor.execute(check_user_id_stmt, (userID,)) results = cursor.fetchall() if len(results) == 0: return error_with_message("user_does_not_exist") elif len(results) > 1: return error_with_message("more_than_one_user") check_password_stmt = "SELECT id FROM users WHERE id=%s AND password=%s" cursor.execute(check_user_id_stmt, (userID,)) results = cursor.fetchall() if len(results) == 0: return error_with_message("incorrect password") choose_defaults_stmt = "SELECT push_notifications_enabled, Apple_APN_Key, Android_APN_Key, group_id, name, class_year, password FROM users WHERE id=%s" cursor.execute(choose_defaults_stmt, (userID,)) result = cursor.fetchone() default_push = result[0] default_apple = result[1] default_android = result[2] default_group_id = result[3] default_name = result[4] default_class_year = result[5] default_password = result[6] push_notif_enable = request.form.get("push_notifications_enabled", default_push) Apple_APN_Key = request.form.get("Apple_APN_Key", default_apple) Android_APN_Key = request.form.get("Android_APN_Key", default_android) group_id = request.form.get("group_id", default_group_id) name = request.form.get("name", default_name) class_year = request.form.get("class_year", default_class_year) password = request.form.get("password", default_password) if int(push_notif_enable) != 0 and int(push_notif_enable) != 1: return error_with_message("Push notifications is not 0 or 1.") if int(group_id) < -1: return error_with_message("Group id too negative") update_user_stmt = "UPDATE users SET push_notifications_enabled=%s, Apple_APN_Key=%s, Android_APN_Key=%s, group_id=%s, name=%s, class_year=%s, password=%s WHERE id=%s" cursor.execute(update_user_stmt, (push_notif_enable, Apple_APN_Key, Android_APN_Key, group_id, name, class_year, password, userID)) if cursor.rowcount is not 1: return error_with_message("updating user failed") conn.commit() return success_with_data({})
def get_plan(uid): con, c = dbconnect.connect() query = " SELECT * FROM plan WHERE user_id = %s " c.execute(query, (uid,)) rows = c.fetchall() dbconnect.close(con, c) if len(rows) == 0: return jsonify(dict()) plans = dict() for row in rows: plan = dict() plan["source_id"] = row[2] plan["destination_id"] = row[3] plan["distance"] = row[4] plan["duration"] = row[5] plan["air"] = row[6] plan["transportation"] = row[7] plan["fixed_duration"] = row[8]
def sava_spot(uid, value): spot_name = value["name"] spot_type = value["type"] is_official = int(False) lon = value["lon"] lat = value["lat"] description = value["description"] con, c = dbconnect.connect() query = """ INSERT INTO spot(spot_name, spot_type, is_official, longitude, longitude, description, user_id) VALUES (%s, %s, %s, %s, %s, %s) """ c.execute(query, (spot_name, spot_type, is_official, lon, lat, description, uid)) con.commit() dbconnect.close(con, c) return jsonify({"Status": 1, "Message": "Successfully save your spot."})
def login(value): login_name = value["login"] password = value["password"] con, c = dbconnect.connect() if validate_email(login_name): query = " SELECT * FROM user WHERE user_email = %s " else: query = " SELECT * FROM user WHERE user_name = %s " c.execute(query, (login_name,)) row = c.fetchall() dbconnect.close(con, c) if len(row) != 0: row = row[0] pass_hash = row[3] if check_password_hash(pass_hash, password): valid_user = User(row[0], row[1], row[2], row[4], row[5]) return (valid_user, jsonify({"Status": 1, "Message": "Logged in successfully."})) return (None, jsonify({"Status": 0, "Message": "Invalid username or password."}))
def sava_plan(uid, value): source_id = value["source"] destination_id = value["destination"] distance = value["distance"] duration = value["duration"] air_pollution_level = value["air"] transportation = value["transportation"] is_oneway = int(value["is_oneway"]) fixed_duration = int(value["fixed_duration"]) con, c = dbconnect.connect() query = """ INSERT INTO plan(user_id, source_id, destination_id, distance, duration, air_pollution_level, transportation, is_oneway, fixed_duration) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) """ c.execute(query, (uid, source_id, destination_id, distance, duration, air_pollution_level, transportation, is_oneway, fixed_duration)) con.commit() dbconnect.close(con, c) return jsonify({"Status": 1, "Message": "Successfully save your plan."})
def register(value): email = value["email"] username = value["username"] password = value["password"] age = int(value["age"]) gender = value["gender"] if validate_email(email, check_mx=MX_VERIFY, verify=FULL_VERIFY) == False: # Only checking domain has SMTP Server return jsonify({"Status": 0, "Message": "Please enter a valid email address."}) if not is_safe_username(username): return jsonify({"Status": 0, "Message": "Please enter a valid username."}) if not is_strong_password(password): return jsonify({"Status": 0, "Message": "Your password is to weak, please try again."}) con, c = dbconnect.connect() query = " SELECT user_id FROM user WHERE user_email = %s " if c.execute(query, (email,)) != 0: dbconnect.close(con, c) return jsonify({"Status": 0, "Message": "Email address has already been taken."}) query = " SELECT user_id FROM user WHERE user_name = %s " if c.execute(query, (username,)) != 0: dbconnect.close(con, c) return jsonify({"Status": 0, "Message": "Username has already been taken."}) pass_hash = generate_password_hash(password) query = " INSERT INTO user(user_email,user_name,pass_hash,age,gender) VALUES (%s,%s,%s,%s,%s);" c.execute(query, (email, username, pass_hash, age, gender)) con.commit() dbconnect.close(con, c) html_msg = render_template("welcome.html", username=username) from run import send_mail send_mail("Welcome to Walk With Me", [email], html_msg) return jsonify({"Status": 1, "Message": "Registration successful! Please login."})
class ContextTask(TaskBase): abstract = True def __call__(self, *args, **kwargs): with app.app_context(): return TaskBase.__call__(self, *args, **kwargs) celery.Task = ContextTask return celery app = Flask(__name__, static_url_path='/movies/static') app.config.update(CELERY_BROKER_URL='amqp://', CELERY_RESULT_BACKEND='amqp://') celery = celery_config(app) app.secret_key = secret_key() mail_config(app) client = ImgurClient('c3b69165abc1e57', '6ab82cc6bb1cc6bf12f084569f63303f3cb9971f') # I don't really care about this one nor does it even work if I import, it for some reason mysql = connect(app) thwart = mysql.connect().escape_string mail = Mail(app) class RegistrationForm(Form): username = TextField('Username', [validators.Required(), validators.Length(min=4, max=25)]) email = TextField('Email Address', [validators.Required(), validators.Length(min=6, max=35)]) password = PasswordField('New Password', [ validators.Required(), validators.EqualTo('confirm', message='Passwords do not match') ]) confirm = PasswordField('Repeat Password') class EntryForm(Form): title = TextField('Title', [validators.Required()]) subtitle = TextField('Subtitle')