def update_user_profile(old_username, nickname, username, email):
response = { 'error': None, 'data': None }
user_with_same_username = None
user_with_same_email = None
user_info = None
with db.get_db_cursor(commit=False) as cur:
cur.execute('SELECT * FROM users WHERE username=%s;', (username))
user_with_same_username = cur.fetchone()
cur.execute('SELECT * FROM users WHERE username=%s;', (old_username))
user_info = cur.fetchone()
cur.execute('SELECT * FROM users WHERE email=%s;', (email))
user_with_same_email = cur.fetchone()
if user_with_same_username is not None:
response['error'] = "Username Already Exist!"
return response
if user_info is None:
response['error'] = "User Not Found!"
return response
if user_with_same_email['id'] != user_info['id']:
response['error'] = "Email Already Linked!"
return response
with db.get_db_cursor(commit=True) as cur:
cur.execute("UPDATE users SET nickname=%s, username=%s, email=%s WHERE username=%s;", (nickname, username, email, old_username,))
response['data'] = True
return response
def new_blog_entry(title, content, author_id, slug=None, published=False):
response = {'error': None, 'data': None}
if slug is None:
slug = slugify(title)
search_content = generate_search_content(title, content)
existing_entry = None
with get_db_cursor() as cur:
cur.execute('SELECT * FROM entries WHERE title = %s;', (title, ))
existing_entry = cur.fetchone()
if existing_entry is not None:
response[
'error'] = "There is an entry with the same title...Please choose a new one"
return response
# Save to database
inserted_entry = None
with get_db_cursor(commit=True) as cur:
cur.execute(
'INSERT INTO entries (title, content, author_id, slug, published) VALUES (%s, %s, %s, %s, %s) RETURNING *;',
(title, content, author_id, slug, published))
inserted_entry = cur.fetchone()
cur.execute(
'INSERT INTO fts_entries (doc_id, search_content) VALUES (%s, %s);',
(inserted_entry['id'], search_content))
response['data'] = inserted_entry
return response
def update_blog_entry(doc_id, title, content, published):
response = {'error': None, 'data': None}
slug = slugify(title)
search_content = generate_search_content(title, content)
existing_entry = None
with get_db_cursor() as cur:
cur.execute('SELECT * FROM entries WHERE title = %s;', (title, ))
existing_entry = cur.fetchone()
if existing_entry is None:
response['error'] = "There is no entries with the given slug"
return response
# Save to database
updated_entry = None
with get_db_cursor(commit=True) as cur:
cur.execute(
'UPDATE entries SET title=%s, content=%s, slug=%s, published=%s WHERE id = %s RETURNING *;',
(title, content, slug, published, doc_id))
updated_entry = cur.fetchone()
cur.execute(
'UPDATE fts_entries SET search_content=%s WHERE doc_id = %s;', (
search_content,
doc_id,
))
response['data'] = updated_entry
return response
def register_user(nickname, username, email, new_pass, new_pass_again, role='subcriber'):
existing_user = None
response = {'error': None, 'data': None}
if not validate_email(email):
response['error'] = "Email is invalid... Please provide a valid email"
return response
with db.get_db_cursor(commit=False) as cur:
cur.execute('SELECT * FROM users WHERE username = %s OR email=%s;', (username,email,))
existing_user = cur.fetchone()
if existing_user is not None:
response['error'] = "Username or Email already exist!"
return response
if new_pass and new_pass_again != new_pass:
response['error'] = "The retype password do not match...."
return response
password_hash = generate_password_hash(new_pass)
with db.get_db_cursor(commit=True) as cur:
cur.execute('INSERT INTO users (nickname, username, email, password_hash, role) VALUES (%s, %s, %s, %s, %s);', (nickname, username, email, password_hash, role))
response['data'] = True
return response
def people():
if request.method == 'POST':
name = request.form['name']
app.logger.info(f"got a name: {name}")
with db.get_db_cursor(commit=True) as cur:
cur.execute("insert into person (name) values (%s)", (name, ))
return redirect(url_for("people"))
else:
with db.get_db_cursor() as cur:
cur.execute("SELECT * FROM person;")
names = [record["name"] for record in cur]
return render_template("people.html", names=names)
def page_look_up_post(animal_id):
with db.get_db_cursor(True) as cur:
users_id = session['profile']['user_id']
users_avatar = session['profile']['picture']
latitude = request.form.get("latitude", None)
longitude = request.form.get("longitude", None)
description = request.form.get("description", None)
latitude = None if latitude == "" else latitude
longitude = None if longitude == "" else longitude
description = None if description == "" else description
if (latitude == None or longitude == None or description == None):
return "Latitude, longitude, and/or description cannot be empty!"
imageFile = request.files["image"]
if imageFile and allowed_file(imageFile.filename):
filename = secure_filename(imageFile.filename)
cur.execute("insert into Images (image_name, image_data) values (%s, %s) RETURNING id;", (filename, imageFile.read()))
imageID = cur.fetchone()[0]
cur.execute("INSERT INTO Posts (users_id, animal_id, post_text, image_id, latitude, longitude) values (%s, %s, %s, %s, %s, %s)", (users_id, animal_id, description, imageID, latitude, longitude))
else:
cur.execute("INSERT INTO Posts (users_id, animal_id, post_text, latitude, longitude) values (%s, %s, %s, %s, %s)", (users_id, animal_id, description, latitude, longitude))
return redirect(url_for('page_lookup', animal_id=animal_id))
def api_results():
reverse = request.args.get('reverse')
with db.get_db_cursor(commit=True) as cur:
query = "SELECT * FROM survey"
cur.execute(query)
result = cur.fetchall()
items = []
app.logger.info(f"Reverse is {reverse}")
if (reverse == 'true'):
app.logger.info(f"In the reverse")
for row in reversed(result):
i = 0
for key in cur.description:
items.append({key[0]: row[i]})
i = i + 1
else:
app.logger.info(f"In normal")
for row in result:
i = 0
for key in cur.description:
items.append({key[0]: row[i]})
i = i + 1
return jsonify({'Responses': items})
def callback():
auth0().authorize_access_token()
resp = auth0().get('userinfo')
userinfo = resp.json()
session['jwt_payload'] = userinfo
session['profile'] = {
'user_id': userinfo['sub'],
'name': userinfo['name'],
'picture': userinfo['picture']
}
with db.get_db_cursor(commit=True) as cur:
users_id = session['profile']['user_id']
users_name = session['profile']['name']
users_avatar = session['profile']['picture']
cur.execute("Select COUNT(*) FROM Users WHERE id = '%s';" % users_id)
try:
for record in cur:
if record[0] == 0:
if (users_avatar):
#app.logger.info("users_avatar has a value %s", users_avatar)
#cur.execute("insert into Images (image_name, image_data) values (%s, %s) returning id;", (users_id, users_avatar))
#imageID = cur.fetchone()[0]
##app.logger.info("imageID has a value %s", imageID)
cur.execute("insert into Users (id, users_name, profile_picture) values (%s, %s, %s);", (users_id, users_name, users_avatar))
else:
#app.logger.info("users_avatar has no value")
cur.execute("insert into Users (id, users_name) values (%s, %s);", (users_id, users_name))
except:
pass
return redirect('/test_auth')
def hobby_autocomplete():
query = request.args.get("query")
with db.get_db_cursor() as cur:
cur.execute("SELECT name FROM hobby WHERE name like %s;",
("%" + query + "%", ))
results = [x[0] for x in cur]
return jsonify(results)
def survey_api_results():
# this method contents are mainly copied from
# https://stackoverflow.com/a/43796849/14651198
reverse = request.args.get("reverse", False)
# makes sure to check if reverse parameter is actually "true"
if (reverse != False):
reverse = True if (reverse.lower() == "true") else False
with db.get_db_cursor() as cur:
if not reverse:
cur.execute("SELECT * FROM survey ORDER BY time_stamp ASC;")
else:
cur.execute("SELECT * FROM survey ORDER BY time_stamp DESC;")
row_headers = [x[0] for x in cur.description]
rv = cur.fetchall()
json_data = []
for result in rv:
json_data.append(dict(zip(row_headers,result)))
json_output = json.dumps(json_data, default=datetime_handler)
return json_output
def _input_thread_processor(args):
nonlocal input_queue
nonlocal output_queue
nonlocal lines
nonlocal eof_token
nonlocal file_id
t = args
with get_db_cursor() as cursor:
while True:
next_item = input_queue.get()
if next_item is eof_token:
break
i, _a = next_item
print("[T{}] Getting Address {} of {}.".format(
t, i + 1, lines))
new_triples = None
try:
new_triples = model.address.Address(_a, focus=True, db_cursor=cursor) \
.export_rdf(view='gnaf').serialize(format='nt').decode('utf-8')
except NotFoundError as e1:
msg = "address {} not found.".format(a)
logging.warning(msg)
print(msg)
with open('not_found_{}.log'.format(file_id), 'a') as _f:
_f.write(a + '\n')
except Exception as e:
logging.log(logging.DEBUG, 'address ' + a, e)
print('address ' + a + '\n')
print(e)
with open('faulty_{}.log'.format(file_id), 'a') as _f:
_f.write(a + '\n')
if new_triples is not None:
output_queue.put(new_triples)
print("[T{}] Thread terminated.".format(t))
def delete_blog_entry(doc_id):
response = {'error': None, 'data': None}
with get_db_cursor(commit=True) as cur:
cur.execute("DELETE FROM entries WHERE id = %s;", (doc_id, ))
cur.execute("DELETE FROM fts_entries WHERE doc_id = %s;", (doc_id, ))
response['data'] = True
return response
def new_hobby():
name = request.form.get("name", "noname")
desc = request.form.get("description", "no description")
with db.get_db_cursor(True) as cur:
cur.execute("INSERT INTO hobby (name, description) VALUES (%s, %s);",
(name, desc))
return "ok"
def processAddAnimal():
with db.get_db_cursor(commit=True) as cur:
users_id = session['profile']['user_id']
#users_id = 1 #TESTING TESTING TESTING - DON'T DEPLOY THIS
species = request.form.get("species")
endangerment_level = request.form.get("classification")
animal_range = request.form.get("range")
imageURL = request.form.get("imageURL") #TODO: fix the image back-end
imageFile = request.files["imageFile"]
if imageURL != '':
resp = requests.get(imageURL)
image = BytesIO(resp.content).read()
#image = Image.frombytes(imagebytes,'raw',)
#print(image)
##app.logger.info(image)
cur.execute('insert into Images (image_name, image_data) values (%s, %s) RETURNING id;', (str(imageURL),image))
imageID = cur.fetchone()[0]
elif imageFile and allowed_file(imageFile.filename):
filename = secure_filename(imageFile.filename)
cur.execute("insert into Images (image_name, image_data) values (%s, %s) RETURNING id;", (filename, imageFile.read()))
imageID = cur.fetchone()[0]
else:
return redirect(url_for("page_add_animal", status="Image Upload Failed"))
animal_description = request.form.get("description")
#post_time = str(datetime.now()) #Removed-this is not part of animal page right now
cur.execute('insert into Animals (species, endangerment_level, animal_range, image_id, animal_description, users_id) values (%s, %s, %s, %s, %s, %s) RETURNING id;', (species, endangerment_level, animal_range, imageID, animal_description, users_id))
tags = request.form.get("tags")
tagList = tags.split(', ')
#app.logger.info(tagList)
addTags(cur, cur.fetchone()[0], tagList)
return redirect(url_for("page_feed"))
def new_person():
with db.get_db_cursor(True) as cur:
name = request.form.get("name", "unnamed friend")
##app.logger.info("Adding person %s", name)
cur.execute("INSERT INTO person (name) values (%s)", (name,))
return redirect(url_for('people'))
def page_profile():
with db.get_db_cursor(commit=False) as cur:
users_id = session['profile']['user_id']
# Animal Postings
cur.execute(""" SELECT Animals.id, Animals.species, Animals.animal_description, Images.id
FROM Animals, Images
WHERE
Animals.users_id = %s
AND Animals.image_id = Images.id
ORDER BY Animals.id DESC;
""", [users_id])
animal_postings = [record for record in cur]
cur.execute(""" SELECT Animals.id, Animals.species, Posts.post_text
FROM Animals, Posts
WHERE
Posts.users_id = %s
AND Posts.animal_id = Animals.id
ORDER BY Posts.id DESC;
""", [users_id])
postings_on_existing_animals = [record for record in cur]
cur.execute(""" SELECT Animals.id, Animals.species, Comments.comm_text
FROM Animals, Comments
WHERE
Comments.users_id = %s
AND Animals.id = Comments.animal_id
ORDER BY Comments.id DESC;
""", [users_id])
comments_on_existing_animals = [record for record in cur]
return render_template("profile.html", animal_postings=animal_postings, postings_on_existing_animals=postings_on_existing_animals, comments_on_existing_animals=comments_on_existing_animals)
def get_person(id):
with db.get_db_cursor(False) as cur:
cur.execute("SELECT name, description from person where person_id = %s;", (id,))
people = [record for record in cur];
if(len(people) == 0):
return abort(404)
else:
return render_template("person.html", name=people[0][0], desc=people[0][1], id=id)
def reply_comment(animal_id):
with db.get_db_cursor(True) as cur:
users_id = session['profile']['user_id']
description = request.form.get("description", None)
cur.execute("INSERT INTO Comments (users_id, animal_id, comm_text) values (%s, %s, %s)", (users_id, animal_id, description))
return redirect(url_for('page_lookup', animal_id=animal_id))
def viewcalls(self):
""" Returns an HTML table containing all telephone records. """
cursor = db.get_db_cursor()
cursor.execute(db.normalize_sql('select * from calls order by datetime desc'))
headings = ['service','callee','datetime','duration','seg','cost',
'call_group']
return self.result_set_to_html_table(cursor, headings = headings)
def movie(movie_id):
with db.get_db_cursor() as cur:
cur.execute("SELECT * FROM movie where movie_id=%s", (movie_id, ))
movie = cur.fetchone()
if not movie:
return abort(404)
return render_template("movie.html", movie=movie)
def view_avatar(users_id):
#app.logger.info(users_id)
with db.get_db_cursor() as cur:
users_id.replace('%7C', '|')
cur.execute("SELECT profile_picture FROM Users where id=%s", (users_id,))
users_avatar = cur.fetchone()[0] # just another way to interact with cursors
#app.logger.info("users_avatar has a value %s", users_avatar)
return redirect(users_avatar)
def api_results():
with db.get_db_cursor(commit=True) as cur:
reverse = request.args.get('reverse')
if reverse is None:
cur.execute("SELECT * FROM survey_data")
else:
cur.execute("SELECT * FROM survey_data ORDER BY ts DESC")
rec = cur.fetchall()
return json.dumps(rec, indent=2)
def thanks():
if request.method == 'POST':
try:
q1 = request.form['q1']
app.logger.info(f"q1: {q1}")
except:
#TODO
q1 = None
try:
q2 = request.form['q2']
app.logger.info(f"q2: {q2}")
except:
q2 = None
try:
q3 = request.form['q3']
app.logger.info(f"q3: {q3}")
except:
q3 = None
try:
q4 = request.form['q4']
app.logger.info(f"q4: {q4}")
except:
q4 = None
try:
q5 = request.form['q5']
app.logger.info(f"q5: {q5}")
except:
q5 = None
try:
q6 = request.form['q6']
app.logger.info(f"q6: {q6}")
except:
q6 = None
day = datetime.datetime.today().strftime('%Y-%m-%d')
with db.get_db_cursor(commit=True) as cur:
cur.execute(
"insert into survey (q1, q2, q3, q4, q5, q6, day) values (%s, %s, %s, %s, %s, %s, %s)",
(
q1,
q2,
q3,
q4,
q5,
q6,
day,
))
return redirect(url_for("thanks"))
else:
return render_template("thanks.html")
def serve_img(img_id):
with db.get_db_cursor() as cur:
cur.execute("SELECT * FROM images where img_id=%s", (img_id, ))
image_row = cur.fetchone()
# in memory binary stream
stream = io.BytesIO(image_row["img"])
return send_file(stream, attachment_filename=image_row["filename"])
def api_results(json=True):
with db.get_db_cursor() as cur:
cur.execute("select * from results")
rows = cur.fetchall()
if 'reverse' in request.args and request.args['reverse'] == 'true':
rows = list(reversed(rows))
results = []
for item in rows:
results.append(dict(item))
return jsonify(results) if json else results
def __init__(self, identifier, db_cursor=None):
self.id = identifier
self.uri = config.URI_ADDRESS_SITE_INSTANCE_BASE + identifier
self.address_site_geocode_ids = dict()
if db_cursor is not None:
self.cursor = db_cursor
self._cursor_context_manager = None
else:
self._cursor_context_manager = get_db_cursor()
self.cursor = self._cursor_context_manager.__enter__()
def view_image(img_id):
with db.get_db_cursor() as cur:
cur.execute("SELECT * FROM images where image_id=%s", (img_id, ))
image_row = cur.fetchone() # just another way to interact with cursors
# in memory pyhton IO stream
stream = io.BytesIO(image_row["data"])
# use special "send_file" function
return send_file(stream, attachment_filename=image_row["filename"])
def set_result(self, res):
self.state = res.state
self.info = res.info
db, c = get_db_cursor()
c.execute('update submission set state=?, info=? where rowid=?',
(res.state, res.info, self.sid))
if res.state == 'accepted':
c.execute(
'update problem set n_acceptions=n_acceptions + 1 ' +
'where rowid = ?', (self.pid, ))
db.commit()
def survey_form_post():
with db.get_db_cursor(True) as cur:
major = request.form.get("major", "---")
year = request.form.get("year", None)
reason = request.form.get("optionsRadios", None)
suggestion = request.form.get("suggestion", None)
suggestion = None if suggestion == '' else suggestion
cur.execute("INSERT INTO survey (student_major, student_year, student_reason, student_suggestion) values (%s, %s, %s, %s)", (major, year, reason, suggestion))
return redirect(url_for('survey_thanks'))
def update_password(username, old_pass, new_pass, new_pass_again):
response = {'error': None, 'data': None}
user_current_password = None
with db.get_db_cursor() as cur:
cur.execute('SELECT password_hash FROM users WHERE username=%s;', (username,))
user_current_password = cur.fetchone()
if user_current_password is None:
response['error'] = "Username is not regconized!"
return response
if not validate_password(user_current_password['password_hash'], old_pass):
response['error'] = "Incorrect Old Password!!!"
return response
if new_pass != new_pass_again:
response['error'] = "Retype New Password Mismatch..."
return response
new_pass_hash = generate_password_hash(new_pass)
with db.get_db_cursor(commit=True) as cur:
cur.execute("UPDATE users SET password_hash=%s WHERE username=%s;", (new_pass_hash, username))
response['data'] = True
return response
def page_feed():
with db.get_db_cursor(False) as cur:
query = request.args.get("query", None)
if query:
records = getActivityFeedByQuery(cur, query)
else:
records = getActivityFeed(cur)
#app.logger.info(records.fetchone())
return render_template("feed.html", dataList=records)
def get_calls_from_db(self, min, max):
""" Retrieves calls from the database. """
cursor = db.get_db_cursor()
sql = '''select callee,'''+db.datetime('datetime')+''' datetime,
duration, cost, call_category from calls where
'''+db.datetime('datetime')+''' >= ?
and '''+db.datetime('datetime')+''' < ?'''
sql = db.normalize_sql(sql)
cursor.execute(sql, (str(min),str(max)))
rows = [row for row in cursor]
return rows
def gettops(self,min=None,max=None):
"""
Returns the top 4 most expensive call groups for a specified period of time.
"""
cursor = db.get_db_cursor()
(min,max)=self.get_default_min_max_time(cursor,min,max)
params = (min,max)
cursor.execute(db.normalize_sql('''select call_category from calls where '''+
db.datetime('datetime')+'''>= ? and '''+
db.datetime('datetime')+''' <= ? group by call_category order
by sum(cost) desc limit 4'''), params)
tops = [row[0] for row in cursor]
return json.dumps(tops)
def getcalls(self,min,max):
"""
Returns a detailed listing of the calls made between the specified
timestamps.
"""
cursor = db.get_db_cursor()
params = (min,max)
sql = '''select callee,round(sum(cost),2),call_category,`desc` from calls left
outer join telbook on callee=number where '''
sql += db.datetime('datetime')+'''>= ? and '''+db.datetime('datetime')
sql += ''' <= ? group by callee order by sum(cost) desc'''
cursor.execute(db.normalize_sql(sql), params)
data = [[row[0],
float(row[1]),
self.call_category[row[2]]['color'],
row[3]] for row in cursor]
return json.dumps(data)
def main_out(team1, team2):
def key_for_sort(x):
if x[0] != None and x[1] != None:
return len(x[0] + x[1])
print('#----------------------------------------- {0} - {1} ---------------------------#'.format(team1.encode('utf-8'), team2.encode('utf-8')))
for y in match_history(team1, team2):
for x in y:
print(x, end=' | ')
print()
db = get_db_cursor()
teams = db.teams
print('-*-'*20)
tm1 = teams.find_one({'name': team1})
if not tm1:
print('Команды с названием {0} нет :('.format(team1.encode('utf-8')))
else:
print(team1)
print("-"*40)
for name, data in iter(sorted(grab_team_static(tm1['uri'] + '?type=champ').items(), key=key_for_sort)):
try:
print(name + ': ' + data)
except TypeError:
continue
print('-*-'*20)
tm2 = teams.find_one({'name': team2})
if not tm2:
print('Команды с названием {0} нет :('.format(team2.encode('utf-8')))
else:
print(team2)
print("-"*40)
for name, data in iter(sorted(grab_team_static(tm2['uri'] + '?type=champ').items(), key=key_for_sort)):
try:
print(name + ': ' + data)
except TypeError:
continue
print('-*-'*20)
def cost_per_unit_of_time(self,min,max,unit,tops):
"""
Returns a JSON response containing plot data of telephone calls between
"min" and "max" timestamps, aggregating over "unit" periods of time (e.g.
days or months) and highlighting specified "tops" call groups.
"""
cursor = db.get_db_cursor()
if tops is None:
tops = []
elif isinstance(tops,str):
tops = [tops]
(min,max)=self.get_default_min_max_time(cursor,min,max)
def get_data_for_top(top,top_condition):
params = (min,max)
sql = '''select '''+db.datetime_start_of('datetime',unit)+''' as day,
sum(cost) from calls where call_category '''+top_condition+'''
and '''+db.datetime('datetime')+''' >= ? and '''
sql += db.datetime('datetime')+''' <= ? group by day order by day'''
sql = db.normalize_sql(sql)
cursor.execute(sql, params)
return {
'label':self.call_category[top]['desc'],
'color':self.call_category[top]['color'],
'data':[[1000*int(row[0]),float(row[1])] for row in cursor],
}
data = []
for top in tops:
data.append(get_data_for_top(top,'= "' + top + '"'))
tops_list = ','.join(['"'+top+'"' for top in tops])
data.append(get_data_for_top('REST','not in ('+tops_list+')'))
return json.dumps(data)