def connect(userid, requesterid): """ Route for sending a connection request to a user """ # Checks if connection between users exist connection = get_connection(conn, int(requesterid), int(userid)) # If connection does not exist yet, create connection if connection == None: create_connection(conn, int(requesterid), int(userid)) flash("Connection request sent!", "success") return redirect("/profile/" + userid)
def add_ticker(self): """Add ticker to stock watchlist""" try: symbol = self.add_ticker_entry.get().upper() price = float(self.add_price_entry.get()) pct_inc = float(self.percent_inc_entry.get()) pct_dec = float(self.percent_dec_entry.get()) price_low = price * (1 - pct_dec / 100) price_high = price * (1 + pct_inc / 100) except (ValueError, UnboundLocalError): messagebox.showinfo('ERROR: MISSING ENTRY FIELDS', \ 'Please make sure all fields have valid info.') return # error message if not all entries filled else add ticker to db if len(symbol) == 0 or not price or not pct_dec or not pct_inc: messagebox.showinfo('ERROR: MISSING REQUIRED FIELDS', \ "'Ticker Symbol', 'Price', and '% Dec' and " "'% Inc' are required fields.") else: db = create_connection('tickers.db') cursor = db.cursor() cursor.execute("""INSERT INTO symbols (ticker, price, pct_dec, pct_inc, price_low, price_high) VALUES (?, ?, ?, ?, ?, ?);""", \ (symbol, '{:.2f}'.format(price), '{:.2f}'.format(pct_dec), \ '{:.2f}'.format(pct_inc), '{:.2f}'.format(price_low), \ '{:.2f}'.format(price_high))) close_connection(db) # clear out entry fields self.clear_entries()
def check_price(self, symbol): """ Checks current ticker symbol price to see if price is outside user-defined range """ current_price = lookup(symbol)['price'] db = create_connection('tickers.db') cursor = db.cursor() cursor.execute( """SELECT price, price_low, price_high FROM symbols WHERE ticker=?""", (symbol, )) price, price_low, price_high = cursor.fetchone() if current_price < price_low: # send user a notification that lower bound has been exceeded pct = abs(current_price - price) / price * 100 message = symbol + ' is down ' + str(round(pct, 2)) + '% to $' + \ str(round(current_price, 2)) + ' from your watch ' + \ 'price of $' + str(round(price, 2)) + '.' send_sms(message) elif current_price > price_high: # send user a notification that upper bound has been exceeded pct = abs(current_price - price) / price * 100 message = symbol + ' is up ' + str(round(pct, 2)) + '% to $' + \ str(round(current_price, 2)) + ' from your watch ' + \ 'price of $' + str(round(price, 2)) + '.' send_sms(message)
def complete_task(): # get current user id user_id = str(session['user_id']) # get task name to complete task_name = request.form.get('task') print(task_name) # get todays date for the date of completion today = date.today() # add the completed task to the completed database table conn = create_connection("global.db") db = conn.cursor() db.execute( "INSERT INTO completed ('task', 'date', 'user_id') VALUES (?, ?, ?)", (task_name, today, user_id)) # delete the completed tasks from the tasks database table db.execute("DELETE FROM tasks WHERE task=?", (task_name, )) conn.commit() conn.close() return redirect("/")
def check_tickers(self): """Checks all tickers' prices using check_price""" db = create_connection('tickers.db') cursor = db.cursor() cursor.execute("""SELECT ticker FROM symbols""") tickers = [ticker[0] for ticker in cursor.fetchall() ] # get list of user's tickers for ticker in tickers: self.check_price(ticker)
def project(): # User reached route via POST (as by submitting a form via POST) if request.method == "POST": # get user id: user_id = str(session['user_id']) conn = create_connection("global.db") db = conn.cursor() # store project name project_name = request.form.get('name') # get project description db.execute("SELECT description FROM projects WHERE name=?", (project_name, )) project_description = db.fetchall() project_description = project_description[0][0] # get project id: # query database for project in question: db.execute("SELECT id FROM projects WHERE owner=? AND name=?", (user_id, project_name)) project_id = db.fetchall() refined_project_id = [] for identification in project_id: refined_project_id.append(identification[0]) print(refined_project_id) # use the name to get project details (tasks, deadlines): db.execute("SELECT task, deadline FROM tasks WHERE project_id=?", str(refined_project_id[0])) task_deadline_tuple = db.fetchall() conn.commit() conn.close() # turn the list of tuples into a list of lists task_deadlines = [list(item) for item in task_deadline_tuple] # we want to change the date format from yyyy-mm-dd to Month dd, yyyy: for task in task_deadlines: task_due_date = datetime.strptime(task[1], "%Y-%m-%d") task[1] = task_due_date.strftime("%B %d, %Y") print(task[1]) today = date.today() day = today.strftime("%B %d, %Y") return render_template("projects.html", name=project_name, info=task_deadlines, current_day=day, desc=project_description) # User reached route via GET (as by clicking a link or via redirect) else: return render_template("projects.html")
def run(self): print('Starting ' + self.name + '...\n') # RETRIEVE LIST OF TICKER SYMBOLS VIA SQL db = create_connection('tickers.db') cursor = db.cursor() cursor.execute("""SELECT ticker FROM symbols""") # tickers = cursor.fetchall() tickers = [ticker[0] for ticker in cursor.fetchall()] print('tickers: ', tickers) for symbol in tickers: GUI.check_price(self, symbol) print('Exiting ' + self.name + '...\n')
def delete_task(): # get task name to delete task_name = request.form.get('task') print(task_name) # delete the task from the database conn = create_connection("global.db") db = conn.cursor() db.execute("DELETE FROM tasks WHERE task=?", (task_name, )) conn.commit() conn.close() return redirect("/")
def login(): """Log user in""" # Set up database for queries conn = create_connection("global.db") # Forget any user_id session.clear() # User reached route via POST (as by submitting a form via POST) if request.method == "POST": # Ensure username was submitted if not request.form.get("username"): return render_template("error.html", error_message="must provide username") # Ensure password was submitted elif not request.form.get("password"): return render_template("error.html", error_message="must provide password") db = conn.cursor() username = request.form.get("username") # Query database for username rows = db.execute("SELECT * FROM users WHERE username = ?", (username, )) rows = db.fetchall() conn.commit() # Ensure username exists and password is correct if rows == [] or not check_password_hash(rows[0][2], request.form.get("password")): return render_template( "error.html", error_message="invalid username and/or password") # Remember which user has logged in session["user_id"] = rows[0][0] conn.close() # Redirect user to home page return redirect("/") # User reached route via GET (as by clicking a link or via redirect) else: return render_template("login.html")
def remove_ticker(self): """Remove ticker from stock watchlist""" symbol = self.add_ticker_entry.get().upper() db = create_connection('tickers.db') cursor = db.cursor() cursor.execute("""SELECT count(*) FROM symbols WHERE ticker=?""", (symbol, )) data = cursor.fetchone()[0] if data == 0: messagebox.showinfo("REMOVAL ERROR", \ "Ticker doesn't exist in your watchlist.") else: cursor.execute("DELETE FROM symbols WHERE ticker=?;", (symbol, )) # clear out entry fields self.clear_entries() close_connection(db)
def create(): # User reached route via POST (as by submitting a form via POST) if request.method == "POST": user_id = str(session['user_id']) # Ensure project name was submitted if not request.form.get("project_name"): return render_template("error.html", error_message="must provide project name") # store project name and description as variables project_name = request.form.get("project_name") project_description = request.form.get("project_description") # check if project name is already taken conn = create_connection("global.db") db = conn.cursor() db.execute("SELECT name FROM projects WHERE owner=? AND name=?", (user_id, project_name)) # check if the project name already exists for someone with the same username exists = db.fetchall() if len(exists) == 1: return render_template( "error.html", error_message="project name already exists, try another name") # connect to sqlite3 and put name and description into database db = conn.cursor() db.execute( "INSERT INTO projects ('owner', 'name', 'description') VALUES (?, ?, ?)", (session['user_id'], project_name, project_description)) rows = db.fetchall() conn.commit() conn.close() return redirect("/") # User reached route via GET (as by clicking a link or via redirect) else: return render_template("create.html")
def add_task(): # check if the user entered a day that already happened deadline = request.form.get('deadline') deadline_date = datetime.strptime(deadline, "%Y-%m-%d") print(deadline) print(deadline_date) current_date = datetime.now() print(current_date) if deadline_date.date() < current_date.date(): return render_template( "error.html", error_message="please enter a due date today or after today's date" ) # get project name to help add to database project_name = request.form.get('confirm_add_task') # get user id to help add to database user_id = str(session['user_id']) # store task details task_name = request.form.get('task') deadline = request.form.get('deadline') # get the project id for the selected task conn = create_connection("global.db") db = conn.cursor() db.execute("SELECT id FROM projects WHERE name=?", (project_name, )) project_id = db.fetchall() project_id = project_id[0][0] # add the task to the database: db.execute( "INSERT INTO tasks ('task', 'deadline', 'project_id') VALUES (?, ?, ?)", (task_name, deadline, project_id)) conn.commit() conn.close() return redirect("/")
def predict_price_for_user(user_id): """ :param user_id: user_id which will be used to get data from database :return: None """ features = [ 'area', 'rooms', 'floor', 'balconies', 'distance_to_center', 'city' ] connection, cursor = create_connection() data = get_data_from_db(user_id, cursor, 'cost') # Check if new data to predict exist if not data: return for feature in features: if feature not in data or data.get(feature, None) is None: return del data['cost'] # Remove column which predict price_prediction_model = get_model_from_cache('price') data_to_solve_id = data.pop('id') data_to_predict = scaling_data_to_good_view( pd.DataFrame([data])[features]) # Scale data # Rescale predicted value original_price = return_original_price( predict_data_with_model(price_prediction_model, data_to_predict)) K.clear_session() update_predicted_data(cursor, user_id=user_id, column='cost', predicted_value=original_price, data_to_solve_id=data_to_solve_id) connection.commit()
def complete(): # User reached route via POST (clicked to delete a task) if request.method == "POST": project_name = request.form.get('complete_task') print(project_name) # get the tasks for this project, by first getting the id: conn = create_connection("global.db") db = conn.cursor() db.execute("SELECT id FROM projects WHERE name=?", (project_name, )) project_id = db.fetchall() project_id = project_id[0][0] # and then getting the tasks db.execute("SELECT task FROM tasks WHERE project_id=?", (project_id, )) tasks = db.fetchall() print(tasks) # turn the list of tuples into a list of lists tasklist = [list(item) for item in tasks] updated_tasklist = [] for task in tasklist: updated_tasklist.append(task[0]) print(updated_tasklist) conn.commit() conn.close() return render_template("complete.html", project=project_name, tasks=updated_tasklist) # User reached route via GET (by submitting the form detailing the task) else: return redirect("/")
def __init__(self): # configure the root window self.root = tk.Tk() self.root.title('Stock Change Notifier') # set window size (w, h) = self.root.winfo_screenwidth(), self.root.winfo_screenheight() self.root.geometry("%dx%d+0+0" % (w, h)) # create all of the main containers self.top_row = tk.Frame(self.root, width=w, height=100, bd=10, bg='red') self.mid_row = tk.Frame(self.root, width=w, height=100, bd=10, bg='blue') self.bot_row = tk.Frame(self.root, width=w, height=100, bd=10, bg='green') # layout all of the main containers self.root.grid_rowconfigure(0, weight=1) self.root.grid_rowconfigure(1, weight=1) self.root.grid_rowconfigure(2, weight=1) self.root.grid_columnconfigure(0, weight=1) self.root.grid_columnconfigure(1, weight=1) self.root.grid_columnconfigure(2, weight=1) self.top_row.grid(row=0, columnspan=3) self.mid_row.grid(row=1, columnspan=3) self.bot_row.grid(row=2, columnspan=3) # create the widgets for the top row self.see_stock_list_button = tk.Button(self.top_row, \ command=self.see_list, \ text='See Stock Watchlist', \ font='Times 20', \ activebackground='blue', bd=2, \ bg='grey', fg='black', \ highlightthickness=10, \ highlightcolor='red') # layout the widgets in the top row self.see_stock_list_button.grid(row=0, sticky='we') # create the widgets for the middle row self.add_ticker_label = tk.Label(self.mid_row, text='Ticker Symbol: ', \ font='Times 20') self.add_ticker_entry = tk.Entry(self.mid_row, justify='left', \ font='Times 20', takefocus=1) self.add_price_label = tk.Label(self.mid_row, text='Price: ', \ font='Times 20', justify='left') self.add_price_entry = tk.Entry(self.mid_row, justify='left', \ font='Times 20', takefocus=1) self.get_current_price_button = tk.Button(self.mid_row, \ command=self.lookup_current_price, \ text='Get Current Price', \ font='Times 20', bd=2, \ activebackground='blue', \ bg='grey', fg='black', \ justify='center', padx=5, \ highlightthickness=10, \ highlightcolor='red') self.percent_inc_label = tk.Label(self.mid_row, font='Times 20', \ text='% Increase to Notify: ', \ justify='left') self.percent_inc_entry = tk.Entry(self.mid_row, justify='left', \ font='Times 20', takefocus=1) self.percent_dec_label = tk.Label(self.mid_row, font='Times 20', \ text='% Decrease to Notify: ', \ justify='left') self.percent_dec_entry = tk.Entry(self.mid_row, justify='left', \ font='Times 20', takefocus=1) # layout the widgets for the middle row self.add_ticker_label.grid(row=0, column=0, sticky='we') self.add_ticker_entry.grid(row=0, column=1, sticky='we') self.add_price_label.grid(row=1, column=0, sticky='we') self.add_price_entry.grid(row=1, column=1, sticky='we') self.get_current_price_button.grid(row=1, column=2, sticky='we') self.percent_inc_label.grid(row=2, column=0, sticky='we') self.percent_inc_entry.grid(row=2, column=1, sticky='we') self.percent_dec_label.grid(row=3, column=0, sticky='we') self.percent_dec_entry.grid(row=3, column=1, sticky='we') # create the widgets for the bottom row self.add_ticker_button = tk.Button(self.bot_row, \ command=self.add_ticker, \ text='Add Ticker to List', \ font='Times 20', \ activebackground='blue', bd=2, \ bg='grey', fg='black', \ justify='center', padx=1000, \ pady=25, highlightthickness=10, \ highlightcolor='red') self.remove_ticker_button = tk.Button(self.bot_row, \ command=self.remove_ticker, \ text='Remove Ticker from List', \ font='Times 20', \ activebackground='blue', bd=2, \ bg='grey', fg='black', \ justify='center', padx=1000, \ pady=25, highlightthickness=10, \ highlightcolor='red') # layout the widgets for the bottom row self.add_ticker_button.grid(row=0, sticky='we') self.remove_ticker_button.grid(row=1, sticky='we') # set initial focus to add_ticker_entry self.add_ticker_entry.focus() # allow user to tab to next widget self.see_stock_list_button.bind('<Tab>', focus_next_widget) self.add_ticker_entry.bind('<Tab>', focus_next_widget) self.add_price_entry.bind('<Tab>', focus_next_widget) self.get_current_price_button.bind('<Tab>', focus_next_widget) self.percent_inc_entry.bind('<Tab>', focus_next_widget) self.percent_dec_entry.bind('<Tab>', focus_next_widget) self.add_ticker_button.bind('<Tab>', focus_next_widget) self.remove_ticker_button.bind('<Tab>', focus_next_widget) # allow user to shift+tab to go to previous widget self.see_stock_list_button.bind('<Shift-KeyPress-Tab>', focus_prev_widget) self.add_ticker_entry.bind('<Shift-KeyPress-Tab>', focus_prev_widget) self.add_price_entry.bind('<Shift-KeyPress-Tab>', focus_prev_widget) self.get_current_price_button.bind('<Shift-KeyPress-Tab>', focus_prev_widget) self.percent_inc_entry.bind('<Shift-KeyPress-Tab>', focus_prev_widget) self.percent_dec_entry.bind('<Shift-KeyPress-Tab>', focus_prev_widget) self.add_ticker_button.bind('<Shift-KeyPress-Tab>', focus_prev_widget) self.remove_ticker_button.bind('<Shift-KeyPress-Tab>', focus_prev_widget) # allow user to execute button on 'Enter' self.see_stock_list_button.bind('<Return>', lambda event: self.see_list()) self.get_current_price_button.bind( '<Return>', lambda event: self.lookup_current_price()) self.add_ticker_button.bind('<Return>', lambda event: self.add_ticker()) self.remove_ticker_button.bind('<Return>', lambda event: self.remove_ticker()) # get list of ticker symbols in user's watchlist db = create_connection('tickers.db') cursor = db.cursor() cursor.execute("""SELECT ticker FROM symbols""") tickers = cursor.fetchall() tickers = [ticker[0] for ticker in tickers] # check prices of all tickers on user's watchlist against bounds for ticker in tickers: self.check_price(ticker) close_connection(db) # bring root window into immediate view self.root.lift() self.root.attributes('-topmost', True) self.root.after_idle(self.root.attributes, '-topmost', False) # set focus onto root window self.root.focus_force()
from helpers import create_connection, fetchall, fetchone connection = create_connection("ekatte", "postgres", "admin", "127.0.0.1", "5432") def get_data(search): q = f"SELECT s.ekatte, s.type, s.name, m.name, a.name FROM settlements s LEFT JOIN municipalities m ON s.municipality_code=m.code LEFT JOIN areas a ON m.area_code=a.code WHERE LOWER(s.name) LIKE '%{search.lower()}%';" responses = fetchall(connection, q) return responses def get_stats(): stats = dict() q = f'SELECT COUNT(*) FROM areas;' r = fetchone(connection, q) stats['areas'] = r[0] q = f'SELECT COUNT(*) FROM municipalities;' r = fetchone(connection, q) stats['municipalities'] = r[0] q = f'SELECT COUNT(*) FROM settlements;' r = fetchone(connection, q) stats['settlements'] = r[0] return stats
This script notifies the user when their chosen stock tickers change in price by more than a user-specified percentage. """ from helpers import (create_connection, close_connection) from GUI import thread_run_GUI, thread_check_price, GUI import os if __name__ == '__main__': # create SQLite3 database to store user's stock market information if not # created already if not os.path.exists('tickers.db'): # create connection to database and create a table to store data db = create_connection('tickers.db') cursor = db.cursor() create_sql_db = """ CREATE TABLE symbols ( ticker TEXT PRIMARY KEY, price REAL, pct_dec REAL, pct_inc REAL, price_low REAL, price_high REAL);""" cursor.execute(create_sql_db) close_connection(db) # create new threads # thread1 = thread_run_GUI(1, 'GUI Thread', 1) # thread2 = thread_check_price(2, 'Check Ticker Prices', 2)
def index(): # Set up database for queries conn = create_connection("global.db") db = conn.cursor() user_id = str(session['user_id']) # Query database for username db.execute("SELECT username FROM users WHERE id=?", user_id) user = db.fetchall() # query database for project names db.execute("SELECT name FROM projects WHERE owner=?", user_id) project_names = db.fetchall() refined_project_names = [] for project in project_names: refined_project_names.append(project[0]) print(refined_project_names) # query database for project ids that user owns db.execute("SELECT id FROM projects WHERE owner=?", user_id) project_ids = db.fetchall() refined_project_ids = [] for identification in project_ids: refined_project_ids.append(identification[0]) ids = str(refined_project_ids) # query database for completed tasks: db.execute("SELECT task, date FROM completed WHERE user_id=?", user_id) completed_tasks = db.fetchall() print(completed_tasks) # query database for deadlines (detailed tasks) db.execute( "SELECT task, deadline, project_id FROM tasks WHERE project_id IN (%s)" % ','.join('?' * len(ids)), ids) task_deadline_tuple = db.fetchall() print(task_deadline_tuple) # turn the list of tuples into a list of lists task_deadlines = [list(item) for item in task_deadline_tuple] print(task_deadlines) # add the project name to this list for task in task_deadlines: db.execute("SELECT name FROM projects WHERE id=?", str(task[2])) project_name = db.fetchall() task[2] = project_name[0][0] print(task_deadlines) user = user[0][0] conn.commit() today = date.today() print(today) # 2020-09-07 day = today.strftime("%B %d, %Y") print(day) # September 07, 2020 return render_template("index.html", username=user, current_date=day, refined_project_names=refined_project_names, info=task_deadlines, completed_tasks=completed_tasks)
def see_list(self): """Creates table of watchlist to present to the user""" top = tk.Toplevel() top.title('Your watchlist') (w, h) = self.root.winfo_screenwidth(), self.root.winfo_screenheight() top.geometry('{}x{}'.format(w, h)) #create containers for widgets top_row = tk.Frame(top, width=w, height=round(h * .05), bg='blue') mid_row = tk.Frame(top, width=w, height=round(h * .75), bg='yellow') bot_row = tk.Frame(top, width=w, height=round(h * .05), bg='red') #layout containers top.grid_rowconfigure(0, weight=1) top.grid_rowconfigure(1, weight=1) top.grid_columnconfigure(0, weight=1) top.grid_columnconfigure(1, weight=0) top_row.grid(row=0, columnspan=1) mid_row.grid(row=1, columnspan=1) bot_row.grid(row=2, columnspan=1) #open connection to tickers.db db = create_connection('tickers.db') cursor = db.cursor() #create widgets for displaying ticker watchlist cursor.execute("""SELECT COUNT(*) FROM symbols""") num_rows = cursor.fetchone()[0] cursor.execute("""SELECT * FROM symbols""") col_names = [name[0] for name in cursor.description] #get column headers col_names = list(map(lambda x: x.upper(), col_names)) #convert headers to uppercase num_cols = len(col_names) #create "table" of user's watchlist for display title = tk.Label(top_row, text='Your Watchlist', font='Times 40', bd=10, \ fg='black', bg='green', relief='raised') title.grid(row=0) for col_name in range(num_cols): h = tk.Label(mid_row, text=col_names[col_name], font='Times 20', \ fg='black', bg='yellow') h.grid(row=0, column=col_name) cursor.execute("""SELECT ticker FROM symbols""") portfolio = cursor.fetchall() for row in range(num_rows): ticker = portfolio[row][0] cursor.execute("""SELECT * FROM symbols WHERE ticker=?""", (ticker, )) ticker_info = cursor.fetchall() for col in range(num_cols): text = ticker_info[0][col] b = tk.Label(mid_row, text=text, fg='black', \ font='Times 20', bg='yellow', padx=20, pady=10) b.grid(row=row + 1, column=col) #create 'dismiss' button dismiss_button = tk.Button(bot_row, text='Dismiss', command=top.destroy, \ font='Times 20', takefocus=1) #layout 'dismiss' button dismiss_button.grid(row=0, sticky='n') #save changes to tickers.db and close connection close_connection(db)
def register(): """Register user""" # Set up database for queries conn = create_connection("global.db") conn2 = create_connection("global.db") # Forget any user_id session.clear() # User reached route via POST (as by submitting a form via POST) if request.method == "POST": # Ensure username was submitted if not request.form.get("username"): return render_template("error.html", error_message="must provide password") # Ensure password was submitted elif not request.form.get("password"): return render_template("error.html", error_message="must provide password") # Ensure password confirmation was submitted elif not request.form.get("confirmation"): return render_template("error.html", error_message="must confirm password") # Ensure the confirmation is the same as the password elif request.form.get("password") != request.form.get("confirmation"): return render_template("error.html", error_message="passwords don't match") # Get username and password username = request.form.get("username") print(username) password = generate_password_hash(request.form.get("password")) print(password) db = conn.cursor() db2 = conn2.cursor() # Query database for username db.execute("SELECT * FROM users WHERE username=?", (username, )) rows = db.fetchall() print(rows) conn.commit() # Ensure username exists and password is correct if len(rows) == 1: return render_template("error.html", error_message="username already taken") # Add the new user to the database db2.execute("INSERT INTO users ('username', 'hash') VALUES (?, ?)", (username, password)) rows2 = db2.fetchall() conn2.commit() conn.close() conn2.close() # Redirect user to home page return redirect("/") # User reached route via GET (as by clicking a link or via redirect) else: return render_template("register.html")