def update_log(id, text): sql = ("UPDATE logs SET text = %s WHERE id = %s") cursor.execute(sql, (text, id)) db.commit() print("Log {} updated".format(id))
def editPwd(): #cursor.execute("SELECT password FROM users where email = %s;", (session['logged_email'],)) #u = cursor.fetchone() error = None if request.method == 'POST': oldPassword = request.form['oldPassword'].strip() newPassword = request.form['newPassword'].strip() newPassword2 = request.form['newPassword2'].strip() cursor.execute("SELECT user_id FROM users WHERE email = %s AND password = crypt(%s, password);", \ (session['logged_email'], oldPassword)) u = cursor.fetchone() if u is None: error = "Your old password is not right." #if not bcrypt.check_password_hash(u[0], oldPassword): # error = 'Your old password is not right.' elif newPassword != newPassword2: error = 'The password is not repeated correctly' elif len(newPassword) < 6: error = 'The password has at least 6 characters' else: #password = bcrypt.generate_password_hash(newPassword) password = newPassword cursor.execute("UPDATE users SET password = crypt(%s, gen_salt('bf', 8)) where email = %s", \ (password, session['logged_email'])) conn.commit() flash('Edit Password Success!') return render_template('users/edit.html', u=u, error=error)
def get_tv_shows(): sql = ("SELECT * FROM tv_shows ORDER BY created DESC") cursor.execute(sql) result = cursor.fetchall() for row in result: print(row)
def delete_log(id): if get_log(id) != None: sql = ("DELETE FROM logs WHERE id = %s") cursor.execute(sql, (id,)) db.commit() print("Log deleted") #venv deactivate ### Add a log ### #add_log('It is one more log', 'Jonathan') #add_log('It is one more log', 'Bruno') #add_log('It is one more log', 'Cristian') ### Return all logs ### #get_logs() ### To add an id to return one log ### #get_log(9) ### Update a log ### #update_log(9, "Updated log") #### Delete one log ### #delete_log(9)
def get_tv_show(id): sql = ("SELECT * FROM tv_shows WHERE id = %s") cursor.execute(sql, (id, )) result = cursor.fetchone() for row in result: print(row)
def get_lp_tokens(lp_staked, lp_staked_vaults): print("Deposit: " + "{:.2f}USD".format(lp_staked['deposit']) + ", Yield: " + "{:.2f}USD".format(lp_staked['yield']) + ", Total: " + "{:.2f}USD".format(lp_staked['total'])) for stake in lp_staked_vaults: print("{:.2f}".format(stake['LPInfo']['currentToken0'] * stake['LPInfo']['priceInUSDToken0'] + stake['LPInfo']['currentToken1'] * stake['LPInfo']['priceInUSDToken1']) + "USD - " + stake['name'] + " deposited") print("Total yield: " + "{:.2f}".format(stake['totalRewards'] * stake['priceInUSDRewardToken'])) sql = "INSERT INTO lp_yield_history (pool, first_token_amount, second_token_amount, first_token_price, second_token_price, deposit, yield) VALUES (%s, %s, %s, %s, %s, %s, %s)" cursor.execute( sql, (stake['name'], stake['LPInfo']['currentToken0'], stake['LPInfo']['currentToken1'], stake['LPInfo']['priceInUSDToken0'], stake['LPInfo']['priceInUSDToken1'], (stake['LPInfo']['currentToken0'] * stake['LPInfo']['priceInUSDToken0'] + stake['LPInfo']['currentToken1'] * stake['LPInfo']['priceInUSDToken1']), stake['totalRewards'])) db.commit()
def get_staked_tokens(staked_tokens, staked_vaults): print("Deposit: " + "{:.2f}USD".format(staked_tokens['deposit']) + ", Yield: " + "{:.2f}USD".format(staked_tokens['yield']) + ", Total: " + "{:.2f}USD".format(staked_tokens['total'])) for stake in staked_vaults: print("{:.2f}".format(stake['depositedTokens']) + " - " + stake['depositToken'] + " deposited") print(stake['depositToken'] + " per USD: " + "{:.2f}".format(stake['priceInUSDDepositToken'])) print("Total USD value: " + "{:.2f}".format(stake['depositedTokens'] * stake['priceInUSDDepositToken'])) print("Total yield: " + "{:.2f}".format( (stake['currentTokens'] - stake['depositedTokens']) * stake['priceInUSDDepositToken'])) sql = "INSERT INTO yield_history (pool, token_amount, token_price, deposit, yield) VALUES (%s, %s, %s, %s, %s)" cursor.execute( sql, (stake['depositToken'], stake['depositedTokens'], stake['priceInUSDDepositToken'], (stake['depositedTokens'] * stake['priceInUSDDepositToken']), ((stake['harvestedRewards'] + stake['pendingRewards']) * stake['priceInUSDRewardToken']))) db.commit()
def test(): user_id = session['logged_id'] cursor.execute( 'SELECT * FROM message where user_id = %s ORDER BY c_time DESC', (user_id, )) m = cursor.fetchall() print(m)
def query_line_id(user_token: str) -> str: logging.debug(f"querying line id with token {user_token}") cmd = ''' SELECT user_line_id FROM cq_users WHERE user_token = (%s); ''' cursor.execute(cmd, (user_token, )) return cursor.fetchall()[0][0]
def get_log(id): sql = ("SELECT * FROM logs WHERE id = $s") cursor.execute(sql, (id,)) result = cursor.fetchone() for row in result: print(row)
def add_letter(id, letter): x = ord(letter) if (x > 60 and x < 91): x = x + 32 if (x > 96 and x < 123): t = get_letter(id, chr(x)) + 1 sql = ("UPDATE logs SET " + letter + " = %s WHERE id = %s") cursor.execute(sql, (t, id)) db.commit() elif (x > 47 and x < 58): m = 'zero' if (letter == '0'): m = 'zero' elif (letter == '1'): m = 'one' elif (letter == '2'): m = 'two' elif (letter == '3'): m = 'three' elif (letter == '4'): m = 'four' elif (letter == '5'): m = 'five' elif (letter == '6'): m = 'six' elif (letter == '7'): m = 'seven' elif (letter == '8'): m = 'eight' elif (letter == '9'): m = 'nine' t = get_letter(id, chr(x)) + 1 sql = ("UPDATE logs SET " + m + " = %s WHERE id = %s") cursor.execute(sql, (t, id)) db.commit()
def create_user(): email_valid = False while True: if not email_valid: email = input("Email: ") email_valid = check_email(email) if not email_valid: print("***Invalid email address***") continue user = cursor.execute( f'SELECT * FROM USER WHERE EMAIL="{email}";').fetchone() if user: print("***Email address already exists***") email_valid = False continue email_valid = True password = getpass.getpass() if len(password) < 6: print("***Password must have min 6 characters***") continue confirm_pass = getpass.getpass(prompt="Confirm password: "******"***Passwords do not match***") continue cursor.execute( f"Insert Into USER ('EMAIL','PASSWORD') Values ('{email}', '{generate_password_hash(password)}');" ) conn.commit() print("***User has been created successfully!***") break
def submit(): errors = 0 for i in range(len(studentdata)): if studentdataEntries[i].get() != studentdataValues[i]: try: cursor.execute( 'update studentdata set %s = %s where studentId = %s' % (studentdata[i], studentdataEntries[i].get(), studentId)) except: messagebox.showerror('Student Info', 'Invalid input') errors += 1 for i in range(len(parentinfo)): if parentinfoEntries[i].get() != parentinfoValues[i]: try: cursor.execute( 'update parentinfo set %s = %s where studentId = %s' % (parentinfo[i], parentinfoEntries[i].get(), studentId)) except: messagebox.showerror('Student Info', 'Invalid input') errors += 1 db.commit() if errors == 0: messagebox.showinfo('Student Info', 'Data successfully updated!')
def change_password(email): user = cursor.execute( f'SELECT * FROM USER WHERE EMAIL="{email}";').fetchone() if not user: print(f"***The user with '{email}' email address does not exist***") return user_old_pass = user[2] old_pass_valid = False while True: if not old_pass_valid: old_password = getpass.getpass(prompt="Old password: "******"***Invalid old password***") continue else: old_pass_valid = True new_password = getpass.getpass(prompt='New password:'******'{generate_password_hash(new_password)}' WHERE email='{email}';" ) conn.commit() print("***Password has been changed successfully!***") break
def update_controller(timer,func): sql_update = "UPDATE controller SET next_run = '%s', status = 'running' WHERE function = '%s'" % (func,timer) try: cursor.execute(sql_update) db.commit() except: pass
def table_helper(data, fields, ranges, source1, source2): from_date, to_date = data[-1][1].split(" - ") # Convert dates from the daterange plugin's format to Year-Month-Day from_date = datetime.strptime(from_date, '%m/%d/%Y').strftime("%Y-%m-%d") to_date = datetime.strptime(to_date, '%m/%d/%Y').strftime("%Y-%m-%d") # Convert ranges from semicolon delimited strings to lists converted_ranges = [] for r in ranges: converted_ranges.append(tuple(map(int, r.split(";")))) # Query by the relevant fields q = "SELECT " for field in fields: q += "%s, " % field q = q.strip().strip(",") q += " FROM title_comparison WHERE " # Filter by user input field ranges for i in range(len(fields)): if not fields[i] in text_fields: q += "CAST(%s AS float) >= %f and CAST(%s AS float) <= %f and " % \ (fields[i], converted_ranges[i][0], \ fields[i], converted_ranges[i][1]) # Filter by date range q += "title1_date >= '%s' and title1_date" \ "<= '%s'" % (from_date, to_date) # Filter by sources if len(source1) > 0: q += " and (" for i in range(len(source1)): q += "source1 = '{}'".format(source1[i]) q += " or " q = q.strip().strip(" or") q += ")" if len(source2) > 0: q += " and (" for i in range(len(source2)): q += "source2 = '{}'".format(source2[i]) q += " or " q = q.strip().strip(" or") q += ")" # Execute the query cursor.execute(q) # Fetch all results of the query results = cursor.fetchall() # Make a dynamic HTML table to display the selected fields table = makeHTMLTable(fields, results) return table
def check(): if len(studentIdEntry.get().strip()) == 0: messagebox.showerror('Attendence', "Please enter a Student ID") elif len(yearEntry.get().strip()) != 4: messagebox.showerror('Attendence', 'Please enter a valid year') issue_counter = 0 variables = [ janVar, febVar, marVar, aprVar, mayVar, junVar, julVar, augVar, sepVar, octVar, novVar, decVar ] for variable in variables: if variable.get() == '': messagebox.showerror('Attendence', 'Please fill all the details') issue_counter += 1 break studentId = studentIdEntry.get().strip() year = yearEntry.get().strip() cursor.execute('use school') cursor.execute( 'select * from attendence where studentId = %s and year = %s' % (studentId, year)) data = cursor.fetchall() if len(data) != 0: messagebox.showerror('Attendence', "Data already exists") issue_counter += 1 if issue_counter == 0: submit()
def find_sources(source1, source2): global all_source1 global all_source2 #find all sources in data (only need to do once) if len(all_source1) == 0 or len(all_source2) == 0: cursor.execute("SELECT DISTINCT source1 FROM title_comparison") all_source1 = cursor.fetchall() #format: list of tuples all_source1 = [x[0] for x in all_source1] formatted_source1 = [(x, True) for x in all_source1] cursor.execute("SELECT DISTINCT source2 FROM title_comparison") all_source2 = cursor.fetchall() #format: list of tuples all_source2 = [x[0] for x in all_source2] formatted_source2 = [(x, True) for x in all_source2] if len(source1) > 0: c = set(source1) formatted_source1 = [(x, x in c) for x in all_source1] if len(source2) > 0: c = set(source2) formatted_source2 = [(x, x in c) for x in all_source2] source1_form = FieldSelection(formatted_source1) source2_form = FieldSelection(formatted_source2) return source1_form, source2_form
def get_logs(): sql = ("SELECT * FROM logs ORDER BY created DESC") cursor.execute(sql) result = cursor.fetchall() for row in result: print(row[1])
def login(): if request.method == 'GET': # Main page return render_template('login.html') if request.method == 'POST': email = request.form['email'] password = request.form['password'] select_user = '******' cursor.execute(select_user, [email]) result = cursor.fetchone() if result != None: user_id = result[0] name = result[1] hash = result[4] isAdmin = result[5] print(isAdmin) if sha256_crypt.verify(password, hash): session['logged_in'] = True session['email'] = email session['name'] = name session['user_id'] = user_id if (isAdmin == True): return redirect(url_for('admins')) else: flash('You are now logged in', 'success') return redirect(url_for('home')) else: error = 'Invalid Login' return render_template('login.html', error=error) else: error = 'User not found! Please Register' return render_template('login.html', error=error) return render_template('login.html')
def unlike(msg_id): if request.method == 'GET': user_id = session['logged_id'] cursor.execute( "DELETE FROM like_msg where msg_id = %s AND user_id = %s;", (msg_id, user_id)) conn.commit() return redirect(url_for('show_entries'))
def update_controller_runtime(func,runtime): newtime = time.time() - runtime sql_update = "UPDATE controller SET runtime = '%s', status = 'inactive' WHERE function = '%s'" % (newtime, func) try: cursor.execute(sql_update) db.commit() except: pass
def selectGender(): cursor.execute(f"""SELECT AGE, (CASE WHEN gender = 1 THEN 'male' ELSE 'female' END) AS gender, height, weight FROM medical_examination WHERE height >= 160""") result = cursor.fetchall() print(result)
def SQLNameExist(self,player,name): sql = 'select id from tb_guild where name=%s' params=(name,) cursor.execute(sql,params) results =cursor.fetchall() if not results: return GS2CGuildNameRepeat(player) return results
def homedata(): origin = request.form['origin'] destination = request.form['destination'] seat_count = "select STOP_CROWD,EMPTY_SEATS from BUS_STOP where ORIGIN = %s and DESTINY = %s" param = (origin, destination) cursor.execute(seat_count, param) seat = cursor.fetchall() return jsonify(seat)
def home(): if request.method == 'GET': cursor = db.cursor() bus_stop_names = 'SELECT DISTINCT ORIGIN FROM BUS_STOP' cursor.execute(bus_stop_names) result = cursor.fetchall() db.commit() return render_template('home.html', bus_stops=result)
async def on_guild_channel_delete(self, channel): guild = get_guild(channel.guild.id) if channel.id == guild[2]: cursor.execute( "UPDATE guilds SET verification_channel_id = %s, verification_role_id = %s, verification_set_username = %s WHERE guild_id = %s", (None, None, None, channel.guild.id)) db.commit()
def delete(cmt_id): if request.method == 'GET': cursor.execute("SELECT msg_id FROM comment where cmt_id = %s;", (cmt_id,)) m = cursor.fetchone() cursor.execute("DELETE FROM comment where cmt_id = %s;", (cmt_id,)) conn.commit() flash('Delete Success!') return redirect(url_for('comment.show', msg_id=m[0]))
async def home2(request): cursor.execute('SELECT * FROM users LIMIT 10') records = cursor.fetchall() rows=[] for row in records: rows.append({'title':row['title']}) return response.json(rows)
def index(): cursor.execute("SELECT * FROM parking") result = cursor.fetchall() print(url_for('index')) print(url_for('login')) print(url_for('login', next='/')) print(url_for('profile', username='******')) return jsonify(result)
def show(pin): cursor.execute("SELECT * FROM parking WHERE pin = " + pin) result = cursor.fetchone() print(url_for('index')) print(url_for('login')) print(url_for('login', next='/')) print(url_for('profile', username='******')) return jsonify(result)
def RegisterAccount(self,socket,name,password): if self.GetByName(name): GS2CNameRepeate(socket) return account = Caccount(name,password) self.Add(account) sql = "insert into tb_account(id,name,password) values(%s,%s,%s)" params = (account.m_Id,name,password) cursor.execute(sql,params) GS2CRegisteAccountSuccess(socket,account)
def GetByName(self,name): account = self.m_NameAccount.get(name, None) if account: return account params=(name,) cursor.execute("select id,password from tb_account where name=%s",params) results=cursor.fetchall() if not results: return None name,password=results[0] account = Caccount(name,password) self.Add(account,account.m_ID) return account
def ChangePassword(self,socket,accountId,oldPassword,newPassword): account = self.Get(accountId) if oldPassword != account.m_Password: GS2COldPasswordIncorrect(socket) return account.m_Password=newPassword self.Add(account,account.m_Id) #from mylogger import log #log.info("login","% change password from %s to %s"%()) sql = "update tb_account set password=%s where id=%s" params = (account.m_Password,account.m_Id) cursor.execute(sql,params) GS2CChangePasswordSuccess(socket)
def Create(self): sql = "insert into tb_guild(id,data) values(%s,%s)" data = self.Pack() params = (self.m_Id,data) cursor.execute(sql,params)
def Save(self): data = self.Pack() sql = "update tb_guild set data=%s where id=%s" params = (data,self.m_Id) cursor.execute(sql,params)
def Load(self): sql = 'select data from tb_guild where id=%s' params=(self.m_Id,) cursor.execute(sql,params) data = cursor.fetchall() self.Unpack(data)