def delete_comments(commentID):
data = jwt.decode(request.args.get('token'), app.config['SECRET_KEY'])
username = data['username']
try:
with connection.cursor() as cursor:
sql = "DELETE FROM `comments` WHERE `comments`.`commentID`='" + str(
commentID) + "'and `comments`.`username`= '" + username + "'"
try:
cur.execute(
"SELECT * FROM `comments` WHERE `comments`.`commentID` LIKE '"
+ str(commentID) + "'and `comments`.`username` LIKE '" +
username + "'"
) #checks if there is any comment whose ID matches the username
result = cur.fetchone()
if result is None:
return jsonify({
'message':
'you are not authorised to delete this message'
})
else:
cursor.execute(sql)
except:
return jsonify({
'message':
'you are not authorised to delete this message'
})
connection.commit()
except:
pass
return jsonify({'message': 'comment deleted'})
def insert(table: str, column_values: Dict):
"""
todo add try/except and exceptions processing; for now it's suitable only for registration case
"""
columns = ', '.join(column_values.keys())
values = [v for v in column_values.values()]
placeholders = f'\'{values[0]}\', \'{values[1]}\''
cursor.execute(f"INSERT INTO {table} "
f"({columns}) "
f"VALUES ({placeholders});")
connection.commit()
def insert_currency(table: str, values: Dict):
"""
add prices for chosen currency. if on these dates data already exist do nothing.
:param table:
:param values:
"""
placeholders = ', '.join([
f"('{date}',{price})"
for date, price in zip(values['date'], values['price'])
])
query = f"INSERT INTO {table} " \
f"(date, price) " \
f"VALUES {placeholders} on conflict (date) do nothing;"
cursor.execute(query)
connection.commit()
def modify_entry(entry_id):
"""Used to modify diary entries."""
details = UserEntries()
user_id = details.get_user_id()
if isinstance(user_id, bool) is False:
if update_entry(entry_id, user_id, details.get_title(),
details.get_contents(), details.get_date_of_event(),
details.get_reminder_time()) is True:
my_cursor.execute("""SELECT TITLE, CONTENTS, DATEOFEVENT,
REMINDERTIME FROM ENTRIES WHERE ID = %s AND
USERID = %s;""", (entry_id, user_id,))
entry_in_database = my_cursor.fetchone()
connection.commit()
return jsonify({"message": entry_in_database}), 200
return jsonify({"message": "entry not in database"}), 403
return jsonify({"message": "Invalid token please login first"}), 401
def exec_script(file_name):
try:
from models import connection
cur = connection.cursor()
log.debug('Execute script %s' % file_name)
cur.executescript(open(file_name, 'r').read())
cur.close()
connection.commit()
except lite.Error, e:
if connection:
connection.rollback()
log.error("Error %s:" % e.args[0])
sys.exit(1)
def comments():
comment = request.get_json()["comment"]
data = jwt.decode(request.args.get('token'), app.config['SECRET_KEY'])
username = data['username']
try:
with connection.cursor() as cursor:
sql = "INSERT INTO `comments`(`username`,`comment`)VALUES(%s, %s)"
try:
cursor.execute("SELECT * FROM users WHERE username = '******'") #checks if username exists
if cursor.fetchone() is not None:
cursor.execute(sql, (username, comment))
else:
return jsonify(
{'message': 'you are not a registered user'})
except:
return jsonify({'message': 'comment not posted'})
connection.commit()
finally:
pass
return jsonify({'message': 'your comment is succesfully posted'})
def register():
name = request.get_json()["name"]
username = request.get_json()["username"]
email = request.get_json()["email"]
password = request.get_json()["password"]
try:
with connection.cursor() as cursor:
sql = "INSERT INTO `users`(`name`,`username`,`email`,`password`)VALUES(%s, %s, %s, %s)"
try:
#checks if username exists in the database
cursor.execute("SELECT * FROM users WHERE username = %s;",
username)
if cursor.fetchone() is not None:
return jsonify({'message': 'username exists'})
else:
cursor.execute(sql, (name, username, email, password))
except:
return jsonify(
{'meassage': 'you are not succesfully registered'})
connection.commit()
except:
pass
return jsonify({'message': 'you are succesfully registered'})