def delete(event, context) -> str:
email = event['requestContext']['authorizer']['claims']['email']
params = json.loads(event['body'])
if not params.get('key'):
return 'Key is absent!'
key = params['key']
key_s3 = email + '_' + key
# Looking for picture
req_text = f'''
SELECT * FROM users_pictures WHERE email='{email}' AND picture_name='{key}';
'''
cursor.execute(req_text)
rec = cursor.fetchall()
if len(rec) == 0:
return 'There is no picture with this name!'
# Deleting
req_text = f'''
DELETE FROM users_pictures WHERE email='{email}' AND picture_name='{key}';
'''
cursor.execute(req_text)
conn.commit()
s3.Object(bucket_name, key_s3).delete()
return "Picture was successfully deleted!"
def change_user_by_id(user_id: int,
new_name: str = None,
new_surname: str = None) -> None:
if new_name is not None and len(new_name) > 20:
print("new_name length is over 20 characters!!")
return
if new_surname is not None and len(new_surname) > 20:
print("new_surname length is over 20 characters!!")
return
c.execute("SELECT * FROM Users WHERE userID = {}".format(user_id))
record: Tuple[int, str, str] = c.fetchone()
if record is None:
print("User with given ID is not in Users table!")
return
new_name = new_name if new_name is not None else record[1]
new_surname = new_surname if new_surname is not None else record[2]
try:
c.execute("UPDATE Users SET "
"name = '{}',"
"surname = '{}'"
"WHERE userID = {}".format(new_name, new_surname,
user_id))
db_connection.commit()
print("Update was succesfull!")
except ValueError:
print("Update of user with id {} failed!".format(user_id))
return
def clear_table(table: str) -> None:
try:
c.execute("DELETE FROM {}".format(table))
db_connection.commit()
except ProgrammingError:
print("Deletion of all rows has failed!")
return
print("Deletion of all rows succeeded")
def user_id(self) -> Optional[int]:
c.execute("SELECT userID FROM Users "
"WHERE name = '{}' AND surname = '{}'"
.format(self.name, self.surname))
got: Tuple[int] = c.fetchone()
if got is None:
print("This user is not in Users table!")
return None
return got[0]
def insert_user(user: User) -> None:
try:
c.execute("INSERT INTO Users (name, surname)"
"VALUES"
"('{}', '{}')".format(user.name, user.surname))
db_connection.commit()
except ProgrammingError:
print("Insertion of a given user into Users table has failed!")
return
print("Succesfully inserted into Users table!")
def note_id(self) -> Optional[int]:
c.execute(
"SELECT noteID FROM Notes "
"WHERE title = '{}' AND authorID = '{}' AND content = '{}'".format(
self.title, self.author.user_id, self.content))
got: Tuple[int] = c.fetchone()
if got is None:
print("This note is not in Notes Table!")
return None
return got[0]
def drop_table(table: str) -> None:
try:
c.execute("DROP TABLE {}".format(table))
print("Drop was sucessful!")
except ProgrammingError:
print("Dropping table with name '{}' has failed!".format(table))
return
except IntegrityError:
print("Cannot delete or update a parent row: "
"a foreign key constraint fails")
return
def create_users_table() -> None:
try:
c.execute(
"CREATE TABLE Users ("
"userID int PRIMARY KEY AUTO_INCREMENT,"
"name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_slovak_ci,"
"surname VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_slovak_ci"
")")
print("Table was created succesfully!")
except (ProgrammingError, DatabaseError):
print("Creation of table Users has failed!")
return
def select_user_by_id(user_id: int, table: str = "Users") \
-> Optional[User]:
try:
c.execute("SELECT * FROM {} WHERE userID = {}".format(
table, user_id))
got: Tuple[int, str, str] = c.fetchone()
if got is None:
print("Table {} doesnt contain record with user id: {}".format(
table, user_id))
return
return User(got[1], got[2])
except ProgrammingError:
print("Selection failed!")
return
def delete_note_by_id(note_id: int, table: str = "Notes") -> None:
c.execute("SELECT * FROM Notes WHERE noteID = {}".format(note_id))
if c.fetchone() is None:
print("No record inside Notes table with noteID equals {}!".format(
note_id))
return
try:
c.execute("DELETE FROM {} WHERE noteID = {}".format(
table, note_id))
db_connection.commit()
except ProgrammingError:
print("Deletion by note_id has failed!")
return
print("Deletion by note_id succedeed!")
def insert_note(note: Note) -> None:
if note.author.user_id is None:
print("Cannot insert given note, author is not in Users table!")
return
try:
c.execute(
"INSERT INTO Notes (title, authorID, published_date, content)"
"VALUES"
"('{}', '{}', '{}', '{}')".format(note.title,
note.author.user_id,
str(note.publish_date),
note.content))
db_connection.commit()
except ProgrammingError:
print("Insertion of a note into Notes table has failed!\n")
return
print("Succesfully inserted into Notes table!")
def create_notes_table() -> None:
q = """CREATE TABLE Notes (
noteID int AUTO_INCREMENT,
title VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_slovak_ci,
authorID int,
published_date DATE,
content VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_slovak_ci,
PRIMARY KEY (noteID),
FOREIGN KEY (authorID) REFERENCES Users (userID)
)"""
try:
c.execute(q)
print("Table was created succesfully!")
except DatabaseError:
print("Cannot create Notes Table before Users table is created,"
" because of foreign key constraints!")
return
def select_note_by_id(note_id: int, table: str = "Notes") \
-> Optional[Note]:
try:
c.execute("SELECT * FROM {} WHERE noteID = {}".format(
table, note_id))
got: Tuple[int, str, int, date, str] = c.fetchone()
if got is None:
print("Table '{}' doesnt contain a record with id {}!".format(
table, note_id))
return
c.execute("SELECT * FROM Users where userID = {}".format(got[2]))
user_got = c.fetchone()
usr = User(user_got[1], user_got[2])
return Note(got[1], usr, got[3], got[4])
except ProgrammingError:
print("Selection failed due to unknown error!")
return
def delete_user_by_id(user_id: int, table: str = "Users") -> None:
c.execute("SELECT * FROM Users WHERE userID = {}".format(user_id))
if c.fetchone() is None:
print("No record inside Users table with userID equals {}!".format(
user_id))
return
try:
c.execute("DELETE FROM {} WHERE userID = {}".format(
table, user_id))
db_connection.commit()
print("Deletion by user_id succedeed!")
except ProgrammingError:
print("Deletion by user_id has failed!")
return
except IntegrityError:
print("Cannot delete or update a parent row: "
"a foreign key constraint fails")
return
def change_note_by_id(note_id: int,
new_title: str = None,
new_auth: User = None,
new_date: date = None,
new_content: str = None) -> None:
if new_title is not None and len(new_title) > 20:
print("Lenght of new_title exceeded the maximum varchar!")
return
if new_auth is not None and new_auth.user_id is None:
print("This user is not in Users table, cannot be used!")
return
if new_content is not None and len(new_content) > 256:
print("Length of new_content exceeded the maximum varchar!")
return
try:
c.execute("SELECT * FROM Notes WHERE noteID = {}".format(note_id))
record: Tuple[int, str, User, date, str] = c.fetchone()
if record is None:
print("No note record found with given noteID!")
return
new_title = new_title if new_title is not None else record[1]
new_auth_id = new_auth.user_id if new_auth is not None else \
record[2]
new_date = new_date if new_date is not None else record[3]
new_date = str(new_date)
new_content = new_content if new_content is not None else record[4]
c.execute("UPDATE Notes SET "
"title = '{}',"
"authorID = '{}',"
"published_date = '{}',"
"content = '{}'"
"WHERE noteID = {}".format(new_title, new_auth_id,
new_date, new_content,
note_id))
print("Update was succesfull!")
db_connection.commit()
except ProgrammingError:
print("Update has failed!")
return
def handler(event, context) -> dict:
email = event['requestContext']['authorizer']['claims']['email']
req_text = f'''
SELECT picture_name, picture_link FROM users_pictures WHERE email='{email}';
'''
cursor.execute(req_text)
rec = cursor.fetchall()
pictures = []
for item in rec:
# print(count, item)
# print(count, type(item))
name = item[0]
link = item[1]
pictures.append({
"picture_name": name,
"picture_link": link,
})
# print(event['requestContext']['authorizer']['claims'].keys())
# print(event['requestContext']['authorizer']['claims'])
print(pictures)
response = {
"statusCode": 200,
"headers": {},
"body": json.dumps(pictures),
"isBase64Encoded": False,
}
return response
def handler(event, context):
key = event["Records"][0]['s3']['object']['key']
email_name=key.split('_')
email = email_name[0].replace('%40', '@')
name = email_name[1]
link = base_url+key
req_text = f'''
INSERT INTO users_pictures (email, picture_name, picture_link)
VALUES ('{email}', '{name}', '{link}');
'''
cursor.execute(req_text)
conn.commit()
responseBody = event
response = {
"statusCode": 200,
"headers": {},
"body": json.dumps(responseBody),
"isBase64Encoded": False,
}
return response
def select_whole_table(table: str) -> \
Union[List[User_record], List[Note_record]]:
c.execute("SELECT * FROM {}".format(table))
return c.fetchall()