def upsert_rating_joke(user_id: str, joke_id: int, rating: float,
source: str) -> bool:
if check_user_exists(user_id) and check_joke_id_exists(joke_id):
try:
sql = """
INSERT INTO ratings (user_id, joke_id, rating, created_at, source)
VALUES ('{user_id}', {joke_id}, {rating}, '{created_at}', '{source}')
ON CONFLICT (user_id, joke_id)
DO UPDATE
SET rating = {rating};
""".format(
user_id=user_id,
joke_id=joke_id,
rating=rating,
created_at=datetime.datetime.now().isoformat(),
source=source,
)
conn = db.get_jokes_app_connection()
db.execute_update(conn, sql)
except sqlalchemy.exc.ProgrammingError:
return False
return True
else:
return False # id user does not exist in DB
def delete_joke(joke_id: int):
conn = db.get_jokes_app_connection()
sql = f"delete from jokes_to_send where id = {joke_id}"
try:
db.execute_update(conn, sql)
except sqlalchemy.exc.SQLAlchemyError:
return False
return True
def upsert_joke_tag(user_id: [str, int], joke_id: int, tag_id: int):
sql = """
INSERT INTO joke_tags (user_id, joke_id, tag_id, created_at)
VALUES ('{user_id}', {joke_id}, {tag_id}, '{created_at}')
ON CONFLICT (user_id, joke_id, tag_id)
DO NOTHING;
""".format(user_id=user_id,
joke_id=joke_id,
tag_id=tag_id,
created_at=datetime.datetime.now().isoformat())
conn = db.get_jokes_app_connection()
db.execute_update(conn, sql)
def remove_user_mail(email: str) -> (bool, str):
conn = db.get_jokes_app_connection()
if has_db_mail_user(conn, email):
sql = "DELETE FROM public.users_mail WHERE email='{email}';".format(
email=email)
try:
db.execute_update(conn, sql)
return True, "User unsubscribed successfully. Sorry to see you go!"
except sqlalchemy.exc.ProgrammingError:
return False, "Error" # some error in executing delete script
else:
return True, "user not in db" # the user is not in the DB already
def update_joke_validation(joke_id: str, user_id: str, is_joke: bool) -> None:
conn = db.get_jokes_app_connection()
sql = """
update
validate_jokes
set
is_joke = {is_joke},
validated_by_user_id = '{validated_by_user}',
updated_at='{updated_at}'
where
id = {joke_id}
""".format(is_joke=is_joke,
validated_by_user=user_id,
updated_at=datetime.datetime.now().isoformat(),
joke_id=joke_id)
db.execute_update(conn, sql)
def add_user_mail(email: str) -> bool:
conn = db.get_jokes_app_connection()
if not has_db_mail_user(conn, email):
s_uuid = str(uuid.uuid1())
d_user_mail = {
"email": email,
"id_hash": s_uuid,
"created_at": datetime.datetime.now().isoformat()
}
db.add_record(conn, "users_mail", d_user_mail)
else: # can be a user resubscribing
if has_db_mail_user_deleted(
conn, email): # if its true then set deleted_at null
sql = "UPDATE public.users_mail SET deleted_at=null WHERE email='{email}';".format(
email=email)
db.execute_update(conn, sql)
else:
print("User with email: '{}' already exists. Skipping..".format(
email))
return True
def put_validated_jokes_in_joke_db():
logger = logging.getLogger(__name__)
conn = db.get_jokes_app_connection()
logger.info("Select validated jokes")
sql_query = "select * from validate_jokes where deleted_at is null and is_joke is true"
df_validated_jokes = db.execute_read(conn, sql_query)
if not df_validated_jokes.empty:
# drop columns that we do not want and rename cols
col_exclude = [
"id",
"hash_id",
"user_str_id",
"user_name",
"is_joke",
"validated_by_user_id",
"updated_at",
"deleted_at",
]
df_jokes = df_validated_jokes.drop(columns=col_exclude)
# add all the records to Jokes DB
db.add_records(conn, "jokes_to_send", df_jokes)
# put soft-delete in validate_jokes
update_query = "update validate_jokes set deleted_at = NOW() where deleted_at is null and is_joke is true"
db.execute_update(conn, update_query)
logger.info("Updated '{}' jokes. New jokes in Jokes DB".format(
len(df_jokes.index)))
else:
logger.info("No new validated jokes to put into the DB")
return True