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)
